Rails 8 Multiple Databases: Read Replicas, Sharding, and Automatic Role Switching

Rails has supported multiple databases since version 6.0, but Rails 8 refined the implementation enough that it actually works without fighting the framework. Here’s how to set up read replicas, horizontal sharding, and automatic connection switching — with the configuration details that the Rails guides gloss over.

The database.yml configuration that actually works

Most tutorials show the minimal config. Here’s what a production database.yml looks like with a primary database and a read replica:

# config/database.yml
production:
  primary:
    database: myapp_production
    host: primary-db.internal
    username: <%= ENV["DB_PRIMARY_USER"] %>
    password: <%= ENV["DB_PRIMARY_PASSWORD"] %>
    adapter: postgresql
    pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
    prepared_statements: true

  primary_replica:
    database: myapp_production
    host: replica-db.internal
    username: <%= ENV["DB_REPLICA_USER"] %>
    password: <%= ENV["DB_REPLICA_PASSWORD"] %>
    adapter: postgresql
    pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
    replica: true
    prepared_statements: true

The replica: true line is what tells Rails this connection is read-only. Miss it, and Rails will try to write to your replica — you’ll get a PG::ReadOnlySqlTransaction error in production at the worst possible time.

Connecting models to specific databases

Your ApplicationRecord needs to know about the replica:

# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

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

Every model inheriting from ApplicationRecord now has two connection pools: one for writes, one for reads. Rails handles connection checkout from the right pool based on the current role.

Automatic role switching

Rails 8 ships with built-in middleware for automatic read/write switching. Enable it:

# 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

The delay: 2.seconds parameter is replication lag protection. After a write, Rails routes all subsequent reads to the primary for 2 seconds, giving the replica time to catch up. In my experience, 2 seconds is generous for most PostgreSQL streaming replication setups — we measured p99 replication lag at 50ms on RDS, but kept the 2-second buffer because the cost of serving stale data after a write is higher than the cost of a few extra primary reads.

How the switching logic works

The middleware checks the session timestamp of the last write. On each request:

1. If the request is a GET or HEAD, check if delay has elapsed since last write
2. If yes → route to reading replica
3. If no → route to primary (writing role)
4. Any POST/PUT/PATCH/DELETE → always routes to primary

You can also switch manually when you need explicit control:

# Force a read from primary (e.g., after a write in an API flow)
ActiveRecord::Base.connected_to(role: :writing) do
  @user = User.find(params[:id])
end

# Force a read from replica (e.g., analytics queries)
ActiveRecord::Base.connected_to(role: :reading) do
  @stats = Order.where(created_at: 30.days.ago..).group(:status).count
end

Horizontal sharding

Read replicas handle read scaling. Horizontal sharding handles data volume. Rails 8’s sharding support lets you split data across multiple databases by a shard key:

# config/database.yml
production:
  primary_shard_one:
    database: myapp_shard_1
    host: shard1-db.internal
    adapter: postgresql

  primary_shard_two:
    database: myapp_shard_2
    host: shard2-db.internal
    adapter: postgresql

# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  connects_to shards: {
    shard_one: { writing: :primary_shard_one },
    shard_two: { writing: :primary_shard_two }
  }
end

Switching between shards:

ActiveRecord::Base.connected_to(shard: :shard_one) do
  User.find(123) # queries shard_one
end

The tricky part is routing. Rails doesn’t decide which shard to query — that’s your responsibility. A common pattern is middleware that determines the shard from the request:

# app/middleware/shard_selector.rb
class ShardSelector
  def initialize(app)
    @app = app
  end

  def call(env)
    request = ActionDispatch::Request.new(env)
    shard = determine_shard(request)

    ActiveRecord::Base.connected_to(shard: shard) do
      @app.call(env)
    end
  end

  private

  def determine_shard(request)
    # Route by tenant subdomain, user ID hash, geographic region, etc.
    tenant = request.subdomain
    tenant_shard_mapping[tenant] || :shard_one
  end
end

Migrations with multiple databases

Each database needs its own migration directory. Generate migrations targeting a specific database:

bin/rails generate migration CreateAnalyticsEvents --database=analytics

This creates the migration in db/analytics_migrate/ instead of the default db/migrate/. Run migrations per-database:

bin/rails db:migrate              # migrates primary only
bin/rails db:migrate:analytics    # migrates analytics database
bin/rails db:migrate:all          # migrates everything

One gotcha: db:migrate:all wasn’t added until Rails 7.1. If you’re upgrading from an older version, you might have a deploy script that only runs db:migrate and silently skips your secondary databases. I’ve seen this cause a 2-hour incident where the analytics database was missing a column that the application expected — the migration had been committed weeks earlier but never ran in production.

Separate databases for different concerns

Beyond replicas, splitting by domain concern reduces contention. A common pattern:

production:
  primary:
    database: myapp_production
    host: primary-db.internal
    adapter: postgresql

  analytics:
    database: myapp_analytics
    host: analytics-db.internal
    adapter: postgresql
    migrations_paths: db/analytics_migrate

# app/models/analytics_record.rb
class AnalyticsRecord < ActiveRecord::Base
  self.abstract_class = true

  connects_to database: {
    writing: :analytics,
    reading: :analytics
  }
end

# app/models/page_view.rb
class PageView < AnalyticsRecord
  # This model reads and writes to the analytics database
end

This keeps heavy analytics queries from competing for connections with your transactional workload. On a project where we split analytics to a separate database, average API response times dropped 15% because the primary connection pool was no longer saturated by long-running aggregate queries.

Connection pool sizing

With multiple databases, each database gets its own connection pool. A Rails process with 5 Puma threads connecting to a primary and a replica maintains 10 connections total — 5 per pool. Running 4 Puma workers means 40 database connections from a single server.

Calculate your total connections:

total_connections = puma_workers × threads_per_worker × number_of_databases

For a setup with 4 workers, 5 threads, primary + replica + analytics:

4 × 5 × 3 = 60 connections per application server

PostgreSQL’s default max_connections is 100. With two application servers, you’re already at 120 — over the limit. Either increase max_connections (watch your memory), use PgBouncer for connection pooling, or reduce your per-pool size.

The pool setting in database.yml should match your thread count, not exceed it. Setting pool: 20 with 5 threads wastes connection slots that could serve other processes.

Testing with multiple databases

Your test configuration needs to mirror production structure:

test:
  primary:
    database: myapp_test
    adapter: postgresql

  primary_replica:
    database: myapp_test  # same DB in test — no actual replica needed
    adapter: postgresql
    replica: true

  analytics:
    database: myapp_analytics_test
    adapter: postgresql
    migrations_paths: db/analytics_migrate

For the replica in test, point it at the same database. You’re not testing replication — you’re testing that your application correctly routes queries through the right connection.

Run bin/rails db:test:prepare to set up all test databases. If you’re using database_cleaner, configure it for each connection:

# spec/support/database_cleaner.rb
RSpec.configure do |config|
  config.before(:suite) do
    DatabaseCleaner[:active_record, { connection: :primary }]
      .strategy = :transaction
    DatabaseCleaner[:active_record, { connection: :analytics }]
      .strategy = :transaction
  end
end

When you actually need multiple databases

Not every application benefits from this complexity. Here’s when it pays off:

Read replicas make sense when your read-to-write ratio exceeds 10:1 and your primary is CPU-bound on queries. If your bottleneck is write throughput or your reads are fast, a replica adds operational complexity without meaningful gain.

Horizontal sharding makes sense when a single PostgreSQL instance can’t hold your dataset or handle your write throughput. For most Rails applications, vertical scaling (bigger instance) is cheaper and simpler until you hit the tens-of-terabytes range.

Separate databases by concern make sense when different workloads compete for resources. Analytics queries running alongside transactional queries is the classic case, but also consider separating background job storage or audit logs.

Before adding databases, check if better indexing or caching solves the problem first. Multiple databases are a scaling tool, not a performance fix.

FAQ

How does Rails handle transactions across multiple databases?

Rails does not support distributed transactions. A transaction block only applies to the current connection. If you need atomicity across two databases, you’ll need application-level compensation logic (saga pattern) or accept eventual consistency. The service object pattern works well for coordinating multi-database writes.

Can I use different database engines for different connections?

Yes. You can run PostgreSQL for your primary and MySQL or SQLite for another database in the same Rails application. Each entry in database.yml specifies its own adapter. This is useful when integrating with legacy systems that run on a different engine.

What happens if the read replica goes down?

By default, Rails will raise a connection error. It does not automatically fall back to the primary. You’ll need a custom resolver or a proxy like PgBouncer/HAProxy to handle failover. In production, I run replicas behind a load balancer that health-checks the replica and routes to the primary if the replica is unreachable.

Does connected_to work inside background jobs?

Yes, but the connection context doesn’t carry over from the enqueuing code. If you enqueue a job while in a connected_to(shard: :shard_two) block, the job itself runs with the default connection. Pass the shard key as a job argument and switch explicitly inside perform.

How do I monitor connection pool usage across multiple databases?

Rails exposes pool statistics through ActiveRecord::Base.connection_pool.stat. For multiple databases, query each pool individually. The OpenTelemetry integration can export pool metrics (size, connections in use, waiting threads) per database to your monitoring system.