skip to content

In Rails 8.1, what do max_connections and checkout_timeout in database.yml control, and when does ActiveRecord::ConnectionTimeoutError appear?

level: middleimportance: should knowfreq 42%

answer

  1. one pool per process, entry and role
  2. max_connections defaults to 5
  3. checkout_timeout: 5 seconds of waiting
  4. all pooled connections were in use
  5. connection_pool.stat shows busy and waiting

basics

~20 s

max_connections caps how many connections one process's pool may open (default 5), and checkout_timeout is how many seconds a thread waits for a free one (default 5). When every connection is busy past that wait, Active Record raises ActiveRecord::ConnectionTimeoutError.

solid answer

~50 s

Active Record keeps a connection pool per database entry and role (and shard) in each process, so a primary and its replica have separate pools. A thread takes a connection from the pool to run queries or a transaction and gives it back when it is done, or at the end of the request or job if it leased one. `max_connections` (renamed from `pool` in 8.1; default 5) is the ceiling. When the ceiling is reached, the next thread waits up to `checkout_timeout` seconds (default 5) and then gets `ActiveRecord::ConnectionTimeoutError`: "could not obtain a connection from the pool within 5.000 seconds ... all pooled connections were in use". Related 8.1 keys are `min_connections` (warm floor, default 0), `idle_timeout` (300 s), `keepalive` (600 s) and `max_age`. `ActiveRecord::Base.connection_pool.stat` shows `size`, `busy`, `idle` and `waiting` when you diagnose it.

code

yaml · 8 lines
yaml
production:
  primary:
    adapter: postgresql
    database: payroll_production
    max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
    min_connections: 1
    checkout_timeout: 5
    idle_timeout: 300

go deeper

for a junior

Know that Active Record reuses a pool of connections, that max_connections caps it, and that ConnectionTimeoutError means the pool was exhausted.

for a middle

Explain the checkout path: idle connection, new connection under the ceiling, then a checkout_timeout wait, and name the 8.1 keys and defaults.

for a senior

Diagnose exhaustion with connection_pool.stat, find long holds inside transactions or stray threads, and remember each role and entry has its own pool.

for a principal

Balance per-process pools against the database server's connection limit across every process and service that shares it.

## What a connection pool is in Active Record Opening a database connection is slow, so Active Record keeps a **connection pool**: a set of open connections that threads borrow and return. There is one pool per database entry and role (and shard) in each process. An app with `primary` and `primary_replica` therefore has two pools per process, and the Solid Queue `queue` entry has its own. A thread borrows a connection when it runs a query, and keeps it while a transaction is open. Code that calls `lease_connection` keeps it for the rest of the request or job; the Rails executor hands leased connections back at the end. A connection that is checked out by one thread cannot serve another at the same time. ## The keys in database.yml | Key (Rails 8.1) | Meaning | Default | |---|---|---| | `max_connections` | most connections the pool may open; `nil` or `-1` means unlimited | 5 | | `min_connections` | connections the pool opens and keeps open | 0 | | `checkout_timeout` | seconds a thread waits for a free connection | 5 | | `idle_timeout` | seconds an unused connection may sit before it is closed; 0 keeps it | 300 | | `keepalive` | seconds between checks on an idle connection | 600 | | `max_age` | seconds before a connection is retired at its next check-in | unlimited | Before 8.1 the ceiling was spelled `pool:`. Rails 8.1 still reads `pool:` when `max_connections:` is absent, but it raises an "Ambiguous configuration" error at boot when both are set to different values, and also when `min_connections` is combined with `pool:`. Do not confuse `checkout_timeout` with the `timeout: 5000` in the generated SQLite block: that one is SQLite's busy timeout in milliseconds, about waiting for a database lock, not for a pool slot. ## What happens when the pool runs dry 1. A thread asks the pool for a connection. 2. If an idle connection exists, it gets it at once. 3. If not, and fewer than `max_connections` are open, the pool opens a new one. 4. If the ceiling is reached, the thread joins a wait queue for up to `checkout_timeout` seconds. 5. If nobody returns a connection in time, Active Record raises **`ActiveRecord::ConnectionTimeoutError`** with the message "could not obtain a connection from the pool within 5.000 seconds (waited 5.002 seconds); all pooled connections were in use". The error is a subclass of `ActiveRecord::ConnectionNotEstablished`, but it does not mean the database is down: the server may be perfectly reachable while this process has used up its own pool. ## Diagnosing it in a payroll service Typical causes, in the order worth checking: - **More concurrent threads than connections.** Each thread that touches the database needs a slot. How to size the pool against the web server's threads is a deployment question with its own home; here the point is that the error is what undersizing looks like. - **Long holds.** A request that opens a transaction, then calls a slow external API inside it, keeps its connection for the whole call. - **Hand-rolled threads.** Work in a `Thread.new` that leases a connection or opens a transaction outside the executor holds it until that thread lets go; wrapping the work in `ActiveRecord::Base.with_connection { ... }` makes the check-in explicit. - **A forgotten second pool.** Report queries sent to the replica draw from the replica's pool, whose `max_connections` comes from the replica's own entry. `ActiveRecord::Base.connection_pool.stat` returns a hash such as `{ size: 5, connections: 5, busy: 5, dead: 0, idle: 0, waiting: 3, checkout_timeout: 5.0 }`; `busy` equal to `size` with `waiting` above zero is the signature of this error. ## Where the pool sits in a multi-database app Every entry and role gets its own pool, each with its own `max_connections` read from its own entry. In a new Rails 8.1 app that means the `primary`, `cache`, `queue` and `cable` entries each have a pool in every process that touches them, and a replica adds one more. The numbers multiply across processes, which is why the database server's own connection limit has to be checked against the total, not against one entry. ## What not to do - Do not "fix" the error by raising `checkout_timeout` alone: requests then queue longer before failing. - Do not set `max_connections` above what the database server allows across all processes; the database refuses connections and you trade one error for another.

  • Does ActiveRecord::ConnectionTimeoutError mean the database server is down?
    No. It means this process's pool had every connection checked out for longer than `checkout_timeout`. The database may be perfectly healthy; the app is holding connections too long or running more concurrent database work than `max_connections` allows. A refused or failed connection to the server surfaces as a different error, such as `ActiveRecord::ConnectionNotEstablished`.
  • What happens if a Rails 8.1 database.yml entry sets both pool: 5 and max_connections: 10?
    Boot fails. The database configuration validates the pair and raises "Ambiguous configuration: 'pool' (5) and 'max_connections' (10) are set to different values. Prefer just 'max_connections'." Setting both to the same number is tolerated, and `pool:` alone still works as a fallback.

A connection pool is the rack of shopping carts at a supermarket door: max_connections is how many carts exist, checkout_timeout is how long a shopper waits at the empty rack, and ConnectionTimeoutError is that shopper giving up while the store itself is still open.

  • checkout_timeout limits how long a connection may stay checked out before it is reclaimed.
  • ConnectionTimeoutError means the database server could not be reached.
  • The generated SQLite timeout: 5000 is the pool wait in milliseconds.
  • A primary and its replica share one connection pool per process.
  • Raising checkout_timeout is the fix when every connection is busy.

Assumes Rails 8.1, which renamed pool: to max_connections: and added min_connections, keepalive and max_age; pool: is still read as a fallback.