multi-database-and-replicas

Multi-Database + Replicas

Rails 6+ has first-class multi-database support. Use it to add a read replica before sharding, and to isolate concerns (analytics queries on a separate DB) before splitting services. AI agents often reach for sharding when a replica would do, or for two services when one DB with two connections would do.

The opinion

Add a read replica first. Sharding is a v0.3 / scale-out problem. Use connects_to per ApplicationRecord-subtree (not per-action). Sticky writes are the default — a write followed by a read in the same request must hit primary (Rails handles via ApplicationRecord.connected_to(role: :writing) middleware). Monitor replica lag; route long-running reports to a read-only replica.

Core patterns

Pattern 1: Primary + replica config

# config/database.yml
production:
  primary:
    adapter: postgresql
    database: myapp_production
    host: <%= ENV["DB_PRIMARY_HOST"] %>
    username: <%= ENV["DB_USER"] %>
    password: <%= ENV["DB_PASSWORD"] %>

  primary_replica:
    adapter: postgresql
    database: myapp_production
    host: <%= ENV["DB_REPLICA_HOST"] %>
    username: <%= ENV["DB_USER"] %>
    password: <%= ENV["DB_PASSWORD"] %>
    replica: true

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

# config/application.rb — automatic role switching
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

What this gives you:

• Reads default to the reading role → replica.
• Writes go to the writing role → primary.
• After a write, the session-cookie resolver tracks the last-write timestamp; subsequent requests within delay (here 2s) stay on primary regardless of HTTP verb. This prevents read-after-write inconsistencies caused by replication lag. The trigger is "wrote-recently", not "HTTP verb" — a GET shortly after a POST still hits primary.