Sizing ActiveRecord Pools for Multiple Databases

This guide is part of Rails ActiveRecord Connection Pool. Rails supports several databases in one application — a primary and a replica, a separate analytics database, a queue database — and each one gets its own connection pool per process. The pool: value in database.yml is therefore not “the pool size” but “the pool size for this database”, and a configuration copied across four entries multiplies the connection count by four:

ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool within 5.000 seconds
# while, on the primary:
FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

The two errors often appear together: the process cannot get a connection from one pool because another pool has consumed the database’s remaining slots.

The objective is a per-database pool sized from the work that actually uses it, a fleet total that fits each server’s budget separately, and a clear picture of which pools a request can touch.

Rapid incident diagnosis

List the pools the process holds. Rails exposes them, and the list is usually longer than expected:

ActiveRecord::Base.connection_handler.all_connection_pools.map do |p|
  { name: p.db_config.name, host: p.db_config.configuration_hash[:host],
    size: p.size, connections: p.connections.size, busy: p.connections.count(&:in_use?) }
end

A four-entry database.yml in a process with 16 Puma threads can hold 64 connections without anything looking wrong locally.

Attribute them on each server. Set a distinct application_name per entry so the servers can tell the pools apart:

SELECT application_name, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;

Check which pool is exhausted. The error names the connection pool in newer Rails versions; where it does not, the stack trace shows the model, and the model’s connects_to role identifies the pool:

Order.connection_pool.stat        # {size:, connections:, busy:, dead:, idle:, waiting:, checkout_timeout:}
AnalyticsRecord.connection_pool.stat

waiting above zero identifies the pool that is short.

Look for accidental replica usage. A model that reads from the replica inside a write transaction is routed to the primary by connected_to, but code that explicitly wraps a read in connected_to(role: :reading) inside a write transaction can hold connections from two pools at once:

grep -rn "connected_to(role: :reading)" --include=*.rb app/ lib/ | head

Mathematical sizing or parameter formula

Size each pool from the work that uses that database, not from the thread count:

pool_primary   = threads_that_can_write × 1                      (a thread holds at most one)
pool_replica   = ceil( read_qps × mean_read_seconds ÷ 0.7 )      capped at thread count
pool_analytics = small and fixed, e.g. 2–4
pool_queue     = concurrency of the queue client

The primary’s pool is the one that must cover every thread, because any thread may write. A replica used only by reporting endpoints needs far fewer connections than there are threads.

Per-server budgets. Each server has its own limit, so the arithmetic is per database rather than fleet-wide:

for each server:
  Σ over processes ( pool_for_that_server ) ≤ usable_connections_on_that_server

With 12 processes: the primary sees 12 × 17 = 204, the replica 12 × 8 = 96, analytics 12 × 4 = 48. A primary with 300 usable connections is fine; an analytics database on a small instance with 100 might not be, especially if other services also connect to it.

Threads are the ceiling, not the target. A pool larger than the number of threads that can use it wastes connections:

pool ≤ threads_per_process + background_threads_using_that_database

For Puma with 16 threads, a primary pool above 17 or 18 cannot be used by the web process.

Replica pools shrink when reads are rare. If only 5 % of requests read from the replica, a replica pool of 2 or 3 covers a 16-thread process:

replica_in_flight = read_fraction × threads_busy × mean_read_seconds ÷ mean_request_seconds

Exact remediation & configuration

Declare each database with its own pool and application name:

# config/database.yml
production:
  primary:
    adapter: postgresql
    url: <%= ENV["DATABASE_URL"] %>
    pool: <%= ENV.fetch("RAILS_MAX_THREADS", 16).to_i + 1 %>
    checkout_timeout: 5
    variables:
      application_name: rails-primary
      statement_timeout: 3000
  primary_replica:
    adapter: postgresql
    url: <%= ENV["REPLICA_URL"] %>
    pool: 8
    replica: true
    checkout_timeout: 3
    variables:
      application_name: rails-replica
  analytics:
    adapter: postgresql
    url: <%= ENV["ANALYTICS_URL"] %>
    pool: 4
    migrations_paths: db/analytics_migrate
    checkout_timeout: 10
    variables:
      application_name: rails-analytics
      statement_timeout: 60000

Route models to the right pools:

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class
  connects_to database: { writing: :primary, reading: :primary_replica }
end

class AnalyticsRecord < ActiveRecord::Base
  self.abstract_class = true
  connects_to database: { writing: :analytics, reading: :analytics }
end

class DailyRollup < AnalyticsRecord; end

Use automatic role switching rather than manual blocks where possible, so reads go to the replica without scattering connected_to through the code:

# 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

The two-second delay keeps a session’s reads on the primary just after it writes, which is the read-your-writes guard.

Avoid holding two connections at once. A block that reads from the replica inside a primary transaction holds one connection from each pool:

# Holds primary + replica simultaneously
ApplicationRecord.transaction do
  order = Order.create!(params)
  ApplicationRecord.connected_to(role: :reading) { Report.recent }   # second pool
end

# Better: do the read before or after the transaction
recent = ApplicationRecord.connected_to(role: :reading) { Report.recent }
ApplicationRecord.transaction { Order.create!(params) }

Give background processes their own configuration. Sidekiq’s concurrency, not RAILS_MAX_THREADS, drives its pools; the pattern is in Sizing ActiveRecord Pools for Sidekiq Workers. A worker that never touches analytics should not be configured with an analytics pool at all — remove the entry from that process’s configuration rather than sizing it to zero.

Verify the migration paths. A second database with its own migrations_paths needs rails db:migrate:analytics; running the default task against it silently does nothing, and a schema drift then looks like a pool problem when queries fail.

Auditing the fleet’s connections per server

With several databases, the budget question is per server, and the answer changes whenever a process type is added.

Build the table from configuration, not from memory. Every process type — web, worker, cron, console — has a set of pools:

The totals are what each server must accept. A primary with 400 usable connections has margin; an analytics instance with 100 does not, and the fix is to remove the analytics pool from process types that never use it.

Check it in CI. The same script that checks a single service’s budget can sum per database:

configs = ActiveRecord::Base.configurations.configs_for(env_name: "production")
configs.each do |c|
  total = PROCESS_TYPES.sum { |t| t[:instances] * (t[:pools][c.name.to_sym] || 0) }
  budget = BUDGETS.fetch(c.name.to_sym)
  abort "#{c.name}: #{total} exceeds budget #{budget}" if total > budget
end

Watch for pools created implicitly. connected_to(shard:), multiple shards, and ActiveRecord::Base.establish_connection in an initializer all create additional pools that no database.yml entry describes. The runtime list from the diagnosis section is the ground truth.

Re-check after adding a database. The most common regression is adding a fourth database to database.yml and deploying it to every process type, including the twelve web processes that will never query it.

Validation & verification

Pool list matches the intent. In each process type, print the runtime pool list and confirm it contains only the databases that process should use.

Per-server totals match the table. During peak, each server’s session count grouped by application_name should match the computed totals within a few connections.

Replica reads actually use the replica. Confirm from the replica’s statistics that reads are arriving:

SELECT sum(calls) FROM pg_stat_statements WHERE query ILIKE 'select%';   -- on the replica

No request holds two pools unnecessarily. Instrument the two pools’ busy counts and compare their peaks with request concurrency; if both peak at the same time as each other and as request concurrency, some path is holding both.

Checkout timeouts name the right pool. Force a small pool on one database in staging and confirm the error and the metrics identify that pool rather than the primary.

Frequently Asked Questions

Related

• Rails ActiveRecord Connection Pool — the parent guide to ActiveRecord’s pool.
• Sizing ActiveRecord Pools for Sidekiq Workers — the worker side of the same arithmetic.
• Fixing ActiveRecord ConnectionTimeoutError in Puma — diagnosing a single pool’s exhaustion.
• Calculating a Connection Budget Across Microservices — the per-server budget method this page applies.