Rails Read Replicas: Multiple Databases Setup with Automatic Connection Switching

A founder I work with as fractional CTO called me on a Tuesday morning because his Rails 8 SaaS had been throwing 502s for forty minutes. The Postgres primary was at 96% CPU, the queue was backing up, and Sidekiq workers were timing out on connection checkout. Read traffic was eating the database alive — a single dashboard endpoint that ran four big aggregations on every page load, called from a Slack channel with two hundred people in it. We did not need a bigger primary. We needed Rails read replicas, and we needed them by lunch.

After nineteen years of Rails I have done this dance more times than I want to count. The good news is that Rails read replicas are no longer a third-party concern. Since Rails 6.1 and especially since the multiple-databases improvements in Rails 7 and 8, the framework has everything you need: database.yml configuration, automatic role switching middleware, manual connected_to blocks, and prevention of writes to replicas. This is the production playbook I use every time, including the sharp edges that the Rails guides do not mention.

What Rails Read Replicas Actually Solve

Rails read replicas are not a magic scaling pixie dust. They solve exactly one problem: the primary database is doing too many SELECTs. If your bottleneck is writes, lock contention, or a missing index, replicas will not help — they will just give you the same problem on another server with the added bonus of replication lag. Before you reach for read replicas, run pg_stat_statements and confirm that read traffic is the dominant cost. If it is not, fix the actual bottleneck first.

When read traffic is the issue, Rails read replicas buy you three things. They offload the heavy SELECTs from the primary, freeing up CPU for writes and the latency-sensitive read path. They give you horizontal headroom — you can add a second or third replica without touching application code. And they let you isolate noisy workloads, like analytics dashboards or admin reports, onto a replica that can fall behind without anyone noticing.

The cost is conceptual, not infrastructural. Once you have replicas, every read in your codebase has to answer a new question: am I allowed to be a few seconds stale? Most reads are. The ones that are not — “did the order I just placed actually save” — are the ones that will burn you if you route them wrong. The whole rest of this post is about getting that routing correct.

Setting Up Multiple Databases in database.yml

The Rails read replicas configuration lives entirely in config/database.yml. You define two connections — a writing connection that points at the primary and a reading connection that points at the replica — and tell Active Record they share a schema. Postgres handles streaming replication outside of Rails; managed services like RDS, Aurora, and Crunchy give you a replica endpoint as a hostname, and that is the only thing Rails needs to know.

# config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  variables:
    statement_timeout: 5000

production:
  primary:
    <<: *default
    database: app_production
    username: app
    password: <%= ENV["DATABASE_PASSWORD"] %>
    host: <%= ENV["DATABASE_HOST"] %>
  primary_replica:
    <<: *default
    database: app_production
    username: app_readonly
    password: <%= ENV["REPLICA_PASSWORD"] %>
    host: <%= ENV["REPLICA_HOST"] %>
    replica: true

Two things matter on the replica block. The replica: true flag tells Rails not to run migrations against this connection — without it, bin/rails db:migrate will try to write DDL to the replica and fail in confusing ways. The username should be a different Postgres role with SELECT-only grants. Even though Rails will refuse writes to a replica connection, defense in depth is cheap: a read-only Postgres user means a buggy migration or a rogue script cannot accidentally write through the replica connection if someone misconfigures the role.

Now wire the model layer. Most apps have one Active Record base class, and that is where the connects-to lives:

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

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

primary_abstract_class tells Rails this is the parent for every model, so the connection config is inherited everywhere automatically. After this, every model in the app has both a writing and a reading role available — but no read traffic is actually going to the replica yet. We need the switcher.

Automatic Connection Switching with the Built-in Middleware

Rails ships a middleware that flips between writing and reading roles based on the HTTP method. GET and HEAD requests use the reading role. POST, PATCH, PUT, DELETE use the writing role. If a request just wrote to the database, every subsequent read for that session is also routed to writing for a short window — the resolver tracks the timestamp of the last write to avoid sending a freshly-mutated user back to a stale replica.

# config/application.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

That is it. Three lines, restart, and your GETs are now hitting the replica. The delay: 2.seconds is the post-write window — set it to whatever your replication lag is comfortably above, and instrument it (more on that below). The session context stores the last-write timestamp in the user’s session, so a user who just submitted a form will keep getting the writer for two seconds and then drift to the reader.

This default works for most CRUD apps. It does not work for everything, and the gotchas matter. The middleware only switches based on HTTP method, not based on what the controller actually does. If you have a GET endpoint that triggers a write — a webhook handler, an OAuth callback that creates a user, a tracking pixel that increments a counter — you will write to the replica role and Rails will raise ActiveRecord::ReadOnlyError. Convert those endpoints to POST or use a manual switching block.

The middleware also does nothing for background jobs, runners, or rake tasks. Anything outside the request cycle defaults to the writing role. If you want a Sidekiq job that runs a giant analytical query on the replica, you have to ask for it explicitly.

Manual Connection Switching for Background Jobs

ActiveRecord::Base.connected_to is the manual switch. Wrap any block in it and the queries inside route to whichever role you ask for. This is how I move analytics, reporting, and slow background work onto replicas without touching the request path:

class WeeklyReportJob < ApplicationJob
  queue_as :reports

  def perform(account_id)
    ActiveRecord::Base.connected_to(role: :reading) do
      account = Account.find(account_id)
      stats = account.orders
        .joins(:line_items)
        .where(created_at: 1.week.ago..)
        .group("date_trunc('day', orders.created_at)")
        .sum("line_items.amount_cents")

      ReportMailer.weekly(account, stats).deliver_later
    end
  end
end

Two patterns I rely on. First, I never wrap the whole job in connected_to(:reading) — deliver_later enqueues another job, and that enqueue is a write that needs to hit the primary. Keep the read block tight around the actual SELECTs and let the writes fall outside. Second, for jobs that legitimately need to read very fresh data (a payment confirmation, a permission check), do not switch. Default to the writer. The replica is for things that can tolerate lag.

For Sidekiq middleware-style enforcement, I sometimes register a per-queue role:

# config/initializers/sidekiq.rb
Sidekiq.configure_server do |config|
  config.server_middleware do |chain|
    chain.add(ReplicaRouter)
  end
end

class ReplicaRouter
  REPLICA_QUEUES = %w[reports analytics exports].freeze

  def call(_worker, job, queue)
    if REPLICA_QUEUES.include?(queue)
      ActiveRecord::Base.connected_to(role: :reading) { yield }
    else
      yield
    end
  end
end

Now any job pushed to the reports queue runs against the replica by default. The job code stays clean, the routing is configuration. This pairs nicely with Solid Queue’s queue prioritization — heavy reads get their own pool, their own queue, and their own connection.

Handling Replica Lag in Production

Replica lag is the silent killer of Rails read replicas. Postgres streaming replication is fast — usually milliseconds — but under heavy write load, during long-running transactions on the primary, or when the replica is doing its own vacuum work, lag can spike to seconds or minutes. If your app silently routes a critical read to a lagged replica, you will get bug reports that are impossible to reproduce in development.

Three defenses. First, monitor lag explicitly. Postgres exposes pg_last_wal_replay_lsn() and pg_stat_replication — get those into your dashboards and alert when lag exceeds your delay window. I usually alert at three times the configured delay, so a delay: 2.seconds config alerts at six. Second, set a hard ceiling on delay in database_selector config that is at least double your p99 lag. If your p99 lag is 800ms, set delay: 2.seconds, not 200ms. The default is too aggressive for most production workloads.

Third, write reads that cannot tolerate lag with explicit role switches. If a controller action depends on data the user just wrote — confirming an order, showing the result of a form submission, returning a freshly-created record — wrap it:

class OrdersController < ApplicationController
  def show
    @order = ActiveRecord::Base.connected_to(role: :writing) do
      Order.find(params[:id])
    end
  end
end

Yes, this is verbose. Yes, it defeats some of the point of the middleware. Use it surgically — one or two endpoints, the ones that actually break under lag. The post-write delay handles the common case (a user who just submitted a form). The explicit switch handles the uncommon case (a confirmation page that loads from a different session, an admin viewing a freshly-created record, a webhook that immediately reads back what it wrote).

This is also where Postgres advisory locks come in handy for cron-like jobs — if your job writes and then reads, the lock guarantees serialization on the primary and you avoid the read-from-stale-replica trap entirely.

Common Pitfalls with Rails Read Replicas

The first pitfall is N+1 queries. If you have a controller that lazily loads associations, every association load is a separate query, and the middleware will route them all to the replica. That is fine until one of them is actually a find_or_create_by masquerading as a read. Rails N+1 patterns and replica routing interact badly — fix the N+1s first, and use eager loading aggressively, before you turn on replica routing.

The second pitfall is connection pool sizing. Each role gets its own pool. If you have pool: 5 and you turn on replicas, your Puma worker now keeps up to ten Postgres connections — five to the primary and five to the replica. With four Puma workers per server and ten servers, that is 400 connections to each Postgres host. Postgres defaults to 100. You will exhaust connections. Either drop the pool size, run PgBouncer in front of both endpoints, or both. I almost always do both.

The third pitfall is migrations. bin/rails db:migrate runs against every non-replica database in database.yml. If you forgot the replica: true flag, the migration will run against the replica too, which is a write, which the replica will reject, which leaves you with half-migrated state. Always set replica: true. Test it by deliberately running a migration in a staging environment with a replica configured — if you get an error you understand, you are wired up correctly.

The fourth pitfall is the test environment. Most teams configure replicas only in production. That is fine until a controller test exercises the replica routing path and behaves differently in production than in CI. I configure a single-database test environment that points the replica role at the same database as the writer. The middleware code path runs in tests, but there is no actual replica, so there is no lag and no surprises.

Monitoring and Verifying Replica Routing

Configuration without verification is faith-based engineering. After you turn on Rails read replicas, you have to confirm queries are actually going where you think. The cheapest signal is ActiveSupport::Notifications — every query event includes the connection, and you can tag log lines with the role:

# config/initializers/log_db_role.rb
ActiveSupport::Notifications.subscribe("sql.active_record") do |*args|
  event = ActiveSupport::Notifications::Event.new(*args)
  role = event.payload[:connection]&.pool&.db_config&.name
  Rails.logger.tagged("db=#{role}") { Rails.logger.debug(event.payload[:sql]) }
end

Better than logs: tag your APM. New Relic, Datadog, and Skylight all support custom attributes — push the role onto every span and you get a per-endpoint breakdown of replica vs primary traffic. After a week of data you will find at least one endpoint that is hitting the wrong role. There is always at least one.

The ultimate verification is a production query against pg_stat_activity on each host. The primary should show a mix of SELECT, INSERT, UPDATE, DELETE. The replica should show only SELECT (and the replication apply process). If the replica shows anything else, you have a misconfigured connection somewhere — usually a rogue background job or a Rails console session that forgot which role it was in.

FAQ

Do Rails read replicas work with PgBouncer?

Yes, and you almost certainly want them to in production. Run a separate PgBouncer pool in front of each endpoint — one for the primary, one for each replica — in transaction pooling mode. The Rails connection pool sits in front of PgBouncer, and PgBouncer multiplexes thousands of Rails connections onto a small number of Postgres backends. Without PgBouncer, even modest Rails fleets exhaust Postgres max_connections quickly when you double the per-worker pool by adding a replica.

How do I route specific Active Record models to a different replica?

Override connects_to on a per-model abstract class. Create an AnalyticsRecord that inherits from ActiveRecord::Base, give it its own connects_to mapping pointing to a dedicated analytics replica, and have your reporting models inherit from AnalyticsRecord instead of ApplicationRecord. Each abstract class gets its own connection pool, and you can route queries by model rather than by HTTP method. Useful when one team’s analytics workload should never touch the primary or the user-facing replica.

What happens to transactions when using read replicas?

Transactions always run on the writing role. ActiveRecord::Base.transaction implicitly wraps in connected_to(role: :writing), so anything inside a transaction block hits the primary even if the surrounding request is GET. This is correct — you cannot have a multi-statement transaction across two physical databases — but it surprises people. If you see writes to the primary on a request you expected to hit the replica, look for an implicit transaction (commonly from a callback or a with_lock call).

Can I use Rails read replicas with Aurora, Crunchy, or self-managed Postgres?

All three. The Rails configuration is identical — you point the replica role at whatever hostname your provider gives you. Aurora’s reader endpoint load-balances across all replicas automatically. Crunchy and self-managed setups typically give you per-replica hostnames, so you list one replica per Rails connection and let DNS or a load balancer in front handle failover. The Rails side does not care which model you use, as long as the host accepts read-only Postgres connections with logical or streaming replication behind it.

Scaling a Rails app and not sure whether read replicas, sharding, or a bigger primary is the right next move? TTB Software helps founders pick the right scaling lever and ship it without downtime. Nineteen years of Rails, plenty of replica setups, and an opinion that most teams turn on replicas a year too late.