connection-pool-tuner

Connection Pool Tuner

Pool exhaustion shows up as checkout timeouts and "too many clients" errors; the fix is almost never "raise max_connections." Diagnose from evidence first, then size to the database.

Workflow

1. Capture the evidence before touching any number. Query pg_stat_activity grouped by state under load. Record total connections, count per state, and the longest-running query/transaction age. Also record every pool that points at the DB: app replicas times per-replica pool size, plus migrations and any superuser sessions.

2. Classify the failure from the evidence:

   • Many idle in transaction rows -> code opens a transaction and does slow work (an HTTP call, sleep, batch) before commit. Fix the transaction scope, not the pool.
   • Many active long-runners -> slow queries saturating connections. Tune the queries.
   • Checkout/acquire timeouts while DB connections sit mostly idle -> the app pool is too small or connections leak (not returned to the pool). Find the leak before enlarging.
   • Total connections at or above max_connections -> the sum of pools is too large; reduce per-replica pool or front with a proxy. Do not raise max_connections.

3. Size the pool to the database, not the app. Postgres performs best at a small connection count; each backend is a process with real memory cost. Target tens of active connections, not thousands; a common ceiling is roughly (core_count * 2) + effective_spindle_count. The sum of all pools must stay under max_connections with headroom for migrations and superuser - keep at least 10-15% of max_connections free. Twenty replicas with a pool of 20 each is 400 connections - usually far too many.

4. Set timeouts that fail fast. Set a pool checkout/acquire timeout (2-5s) so a request errors instead of piling up. Set idle_in_transaction_session_timeout (30-60s is a sane starting range for web workloads) to kill stuck transactions. Set statement_timeout to cap runaway queries - anchor it to your slowest legitimate query plus headroom, often 30s for OLTP paths. In HikariCP, set connectionTimeout and keep maxLifetime a minute or two below the DB/proxy idle cutoff so you never hand out a dead socket.