ActiveRecord Role-Based Connection Switching for Replicas

Problem statement: Your Rails app sends every query to the primary, read load is crowding out writes, and you want ActiveRecord’s built-in role switching to route GET/HEAD reads to a replica and keep recent writers on the primary — without hand-editing every query.

Symptom Identification

Look for these before wiring up role switching:

• The primary handles all SELECT traffic while a provisioned replica sits idle — the app declares no reading role, so ActiveRecord only knows the writing connection.
• The primary’s connection pool (ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool) exhausts under read spikes even though replicas are healthy.
• Read-your-writes bugs: a controller create succeeds, the redirect reads a replica that has not applied the row, and the user sees stale or missing data — replication lag leaking through.
• A hand-added replica connection accepts a stray write and fails deep in the stack with PG::ReadOnlySqlTransaction instead of a clean application error.
• Background jobs hammer the primary with reporting reads that could safely run on a replica, because the automatic request-level switching never runs outside HTTP.

Root Cause Analysis

ActiveRecord models connection targets as roles. Since Rails 6, a model can declare more than one role with connects_to, mapping a writing role to the primary database and a reading role to a replica. ActiveRecord holds a separate connection pool per role and dispatches each query to the pool for the currently active role. By default the active role is writing, so without any switching every query hits the primary.

Two mechanisms change the active role. Manual switching via connected_to(role: :reading) { ... } sets the role for the duration of a block. Automatic switching via ActiveRecord::Middleware::DatabaseSelector sets the role per HTTP request: idempotent requests (GET/HEAD) use reading; non-idempotent requests use writing. Crucially, after a request writes, the middleware stamps a timestamp into the session and pins that user’s subsequent reads to the primary for a configurable delay window — 2 seconds by default — so the user reads their own write while the replica catches up. This is the read-your-writes accommodation built directly into the framework, and it is why the delay must be tuned to your actual replication lag percentiles.

The reading role is additionally configured with prevent_writes enabled, so any DML issued while connected to reading raises ActiveRecord::ReadOnlyError in the application before it ever reaches the standby. This is ActiveRecord’s slice of the general ORM middleware routing model — a role abstraction plus a request-scoped selector, rather than a per-statement classifier.

Architecture: Roles and the Delay Window

Step-by-Step Resolution

Step 1: Define primary and replica databases

Give each environment a primary and a primary_replica connection. The replica: true flag tells ActiveRecord this connection is a standby, so it is skipped by db:migrate and treated as read-only.

# config/database.yml
production:
  primary:
    adapter: postgresql
    database: app_production
    username: app
    password: <%= ENV["DATABASE_PASSWORD"] %>
    host: primary.db.internal
    pool: <%= ENV.fetch("RAILS_MAX_THREADS", 5) %>
  primary_replica:
    adapter: postgresql
    database: app_production
    username: app_ro
    password: <%= ENV["REPLICA_PASSWORD"] %>
    host: replica-lb.internal
    replica: true
    pool: <%= ENV.fetch("RAILS_MAX_THREADS", 5) %>

Inline verification: rails runner 'puts ActiveRecord::Base.configurations.configs_for(env_name: "production").map(&:name)' lists both primary and primary_replica.

Step 2: Declare roles with connects_to

Map roles in the abstract base model. The writing role uses the primary; the reading role uses the replica.

# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  connects_to database: {
    writing: :primary,
    reading: :primary_replica,
  }
end

Every model inheriting from ApplicationRecord now has both a writing and a reading connection pool. The default active role is writing, so nothing changes until you switch.

Inline verification: ApplicationRecord.connected_to(role: :reading) { ActiveRecord::Base.connection.select_value("SELECT pg_is_in_recovery()") } returns t.

Step 3: Enable automatic switching with the DatabaseSelector

Turn on the middleware and set the delay to cover your replication lag. This is the whole automatic-routing mechanism.

# config/environments/production.rb
config.active_record.database_selector = { delay: 2.seconds }
config.active_record.database_resolver =
  ActiveRecord::Middleware::DatabaseSelector::Resolver
config.active_record.database_resolver_context =
  ActiveRecord::Middleware::DatabaseSelector::Resolver::Session

With this in place: GET and HEAD requests use reading; other verbs use writing; and after any write, the requesting user’s reads are pinned to the primary for delay seconds. Set delay from your measured p99 lag — too short and users see stale data, too long and the primary absorbs avoidable read load.

Inline verification: a POST then an immediate GET from the same session both show queries on the primary in pg_stat_activity; a GET more than delay seconds later shows queries on the replica.

Step 4: Switch roles manually where the middleware does not reach

The middleware only runs for HTTP requests. In jobs, rake tasks, and specific hot paths, switch explicitly.

# Force a heavy report onto the replica inside a background job
class NightlyReportJob < ApplicationJob
  def perform
    ApplicationRecord.connected_to(role: :reading) do
      Invoice.where(status: "paid").find_each { |inv| aggregate(inv) }
    end
  end
end

# Force a read-your-writes read onto the primary
ApplicationRecord.connected_to(role: :writing) do
  order = Order.find(params[:id])   # guaranteed to see the just-written row
end

connected_to sets the role for the block only; the previous role is restored on exit. Nesting is allowed, and the innermost block wins.

Inline verification: inside connected_to(role: :reading), ActiveRecord::Base.connection.select_value("SELECT pg_is_in_recovery()") is t; inside role: :writing it is f.

Step 5: Rely on prevent_writes as a safety net

The reading role has prevent_writes enabled. A write attempted while connected to it raises before touching the database.

ApplicationRecord.connected_to(role: :reading) do
  Order.create!(total: 10)   # raises ActiveRecord::ReadOnlyError
end

This turns a dangerous misroute — a write reaching a replica — into a clean, catchable application error. Do not disable it. If you have a legitimate write path, wrap that path in connected_to(role: :writing) instead of loosening the reading role.

Inline verification: the snippet above raises ActiveRecord::ReadOnlyError, not a PG::ReadOnlySqlTransaction from the backend.

Step 6: Confirm end-to-end routing

Exercise the full request lifecycle and inspect both backends.

# rails console
# Read on replica
ApplicationRecord.connected_to(role: :reading) do
  puts ActiveRecord::Base.connection.select_value("SELECT pg_is_in_recovery()") # => t
end
# Write on primary
ApplicationRecord.connected_to(role: :writing) do
  puts ActiveRecord::Base.connection.select_value("SELECT pg_is_in_recovery()") # => f
end

Then confirm the middleware behavior with a real session: a browser POST followed by a fast GET should both appear on the primary in SELECT * FROM pg_stat_activity, and a delayed GET should appear on the replica.

Inline verification: pg_stat_activity.application_name on the replica shows read queries only; on the primary it shows writes plus sticky-window reads.

Configuration Snippet

The complete wiring — database.yml, the abstract model, and the middleware — condensed:

# config/environments/production.rb
config.active_record.database_selector = { delay: 2.seconds }
config.active_record.database_resolver =
  ActiveRecord::Middleware::DatabaseSelector::Resolver
config.active_record.database_resolver_context =
  ActiveRecord::Middleware::DatabaseSelector::Resolver::Session

# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class
  connects_to database: { writing: :primary, reading: :primary_replica }
end

The Resolver::Session context stores the last-write timestamp in the user session, so the delay window is per-user. For a stateless API where you cannot rely on cookies, implement a custom resolver context that reads the timestamp from a signed header or a shared store — the same read-your-writes signal, carried differently.

Verification and Rollback

Confirm switching is live:

# Which role/host served this query?
ApplicationRecord.connected_to(role: :reading) do
  ActiveRecord::Base.connection.select_value("SELECT inet_server_addr()")
end

# Pool sizes per role
ActiveRecord::Base.connection_pool.stat   # writing pool

Roll back by removing the database_selector config and the reading mapping — the app reverts to a single writing connection with no code changes at call sites:

# config/environments/production.rb — kill switch during a replica incident
# (comment out the three database_selector lines)

# app/models/application_record.rb
connects_to database: { writing: :primary, reading: :primary }

Pointing the reading role at :primary collapses all reads onto the primary while keeping connected_to(role: :reading) blocks valid, so no code that references the reading role breaks. Restore the replica mapping once lag recovers.

Edge Cases and Gotchas

The delay window is per session, not per database

The Resolver::Session context stores the last-write timestamp in each user’s session, so the sticky-to-primary window applies only to that user. A different user reading the same freshly written row can still hit a lagging replica and see stale data. If your consistency requirement is global (e.g. an admin dashboard reflecting all users’ writes), the delay window is not enough — force those reads with connected_to(role: :writing) or apply an LSN-based freshness check.

Automatic switching never runs in jobs

DatabaseSelector is Rack middleware, so it only affects HTTP requests. Background jobs, rake tasks, and console sessions default to the writing role and hit the primary. Wrap read-heavy job bodies in connected_to(role: :reading) explicitly, and be careful not to write inside such a block — prevent_writes will raise ActiveRecord::ReadOnlyError.

Tuning delay against real lag percentiles

The default 2-second delay is a guess. If your p99 replication lag is 6 seconds during peak write bursts, a 2-second window lets users read their own writes off a stale replica. Measure lag, set delay above your p99, and revisit it as write volume grows. Setting it far too high needlessly routes reads to the primary and erodes the read-scaling benefit — this is the same trade-off discussed in fallback strategies when replicas fall behind.

FAQ

Related

← Back to ORM Middleware for Automatic Query Routing

• Read/Write Splitting in SQLAlchemy with Multiple Binds — the Python equivalent using a custom get_bind() instead of role switching.
• Configuring Django Database Routers for Read Replicas — Django’s DATABASE_ROUTERS compared with ActiveRecord roles.
• Using Application-Level Timestamps to Bypass Stale Replicas — a stronger, per-query freshness check for when the session delay window is not enough.
• Fallback Strategies When Replicas Fall Behind — how to tune the delay window and degrade gracefully when replicas exceed the lag budget.