13 min read

Database Connection Pooling: Sizing, Timeouts, and Failure Modes

PostgreSQLDatabasesBackend

Every backend service that talks to a database needs a connection pool. Get it wrong and you get cascading failures under load, mysterious timeouts, or a database that falls over from connection storms. Get it right and nobody thinks about it.

Here is what I have learned about sizing, tuning, and operating connection pools in production.

Why Pools Exist

Opening a database connection is expensive. It involves TCP handshake, TLS negotiation, authentication, and session setup. Doing this per request would add tens of milliseconds of overhead and exhaust database resources quickly.

A connection pool maintains a set of open connections that are reused across requests. The application borrows a connection, uses it, and returns it. This amortizes the connection cost and bounds the total connections to the database.

System Architecture

Most production setups end up with three layers of pooling, whether planned or not.

+------------------+     +------------------+
| App Instance 1   |     | App Instance 2   |
| pool max 25      |     | pool max 25      |
+--------+---------+     +---------+--------+
         +---------------+---------+
         |      PgBouncer          |
         |  transaction pooling    |
         +------------+------------+
                      |
                      v
         +-------------------------+
         |      PostgreSQL         |
         | max connections 200     |
         | real connections 40     |
         +-------------------------+

Layer 1 is the per-instance pool inside the application (HikariCP, asyncpg pool, pgxpool). It bounds how many connections one process holds and recycles them.

Layer 2 is PgBouncer (or RDS Proxy, or the cloud equivalent). It multiplexes thousands of client connections down to a small number of real database connections. This is where the real connection budget lives.

Layer 3 is PostgreSQL itself, with max_connections as the hard ceiling.

The math has to work at every layer. If 20 instances each hold 25 connections, that is 500 client connections into PgBouncer. If PgBouncer is configured for 60 server connections and PostgreSQL max_connections is 200, the setup is healthy. If PgBouncer server connections are set to 300 against a max_connections of 200, the database will start rejecting connections, and you will find out at 2 AM.

Pool Sizing: The Math

The most common question: how many connections should the pool have?

There is a well-known formula from the HikariCP documentation:

connections = ((core_count * 2) + effective_spindle_count)

For a 4-core server with SSD storage (effective_spindle_count = 1), this suggests 9 connections. This seems absurdly low, and for many workloads it is. The formula assumes the bottleneck is CPU and that connections are held only during active query execution.

In practice, I use this as a starting point and adjust based on:

Workload characteristics:

Application concurrency:

Database capacity:

My practical starting point for a typical web service: pool size of 20-25 per application instance, then tune based on monitoring. This is higher than the HikariCP formula suggests but works well for I/O-bound workloads where connections are held during network round trips.

Per-service pool budgeting. When several services share one database, budget connections explicitly instead of letting each team pick a number. Start from the database ceiling and work down:

per_instance_max = floor((max_connections - reserved) / instance_count * 0.8)

Reserve connections for admin, monitoring, and replication (10 is a reasonable default; PostgreSQL also has superuser_reserved_connections for this). The 0.8 factor leaves headroom for failover, where a replica promotion or a restarted primary briefly needs extra connections.

Example: max_connections = 200, reserved = 10, 10 instances. Per-instance max = floor(190 / 10 * 0.8) = 15.

Two caveats. First, size for the maximum instance count your autoscaler allows, not the current count, or a scale-up event will blow the budget. Second, if instances scale dynamically and the math gets tight, that is the signal to add PgBouncer and stop doing per-instance arithmetic. The pooler absorbs the variance.

Timeouts: Fail Fast

Every pool operation should have a timeout. The critical ones:

# asyncpg example
pool = await asyncpg.create_pool(
    dsn=DATABASE_URL,
    min_size=5,
    max_size=25,
    # How long to wait for a connection from the pool
    timeout=5.0,
    # How long a connection can be idle before recycling
    max_inactive_connection_lifetime=300,
    # Statement timeout (PostgreSQL side)
    command_timeout=30,
)

Connection acquisition timeout. How long to wait for a free connection before giving up. Set this to a few seconds, not minutes. If the pool is exhausted for more than a few seconds, something is wrong (slow queries, pool too small, or downstream issue). Failing fast lets the caller retry or return a 503 instead of hanging.

Query timeout (statement_timeout). PostgreSQL can kill queries that run too long. Set this at the database or connection level. A runaway query holding a connection for 5 minutes starves the pool.

Idle connection lifetime. Recycle idle connections periodically. This prevents issues with stale connections (database restarted, network interruption, load balancer timeout).

Pool Exhaustion: Detection and Response

Pool exhaustion is when all connections are checked out and new requests wait. Symptoms:

Detection:

Immediate response:

Root cause fixes:

The key insight: pool exhaustion is almost always a symptom, not the root cause. The pool is doing its job by bounding connections. The real problem is usually slow queries or insufficient database capacity.

PgBouncer: When to Add It

PgBouncer is a connection pooler that sits between your application and PostgreSQL. It is useful when:

You have many application instances. 50 pods each with a pool of 20 = 1000 connections to PostgreSQL. That is too many. PgBouncer multiplexes these down to a smaller number of real database connections.

Connection churn is high. Serverless functions or short-lived processes that open and close connections frequently. PgBouncer maintains persistent connections to the database.

You need transaction-level pooling. PgBouncer can assign a database connection per transaction rather than per client connection, dramatically increasing efficiency.

Application (1000 connections) -> PgBouncer -> PostgreSQL (100 connections)

When you do NOT need PgBouncer:

PgBouncer adds operational complexity. Do not add it preemptively. Add it when connection count becomes a problem.

Scalability

Connection math gets unforgiving as instance counts grow. 10 instances with pools of 25 is 250 connections, manageable. 100 instances is 2,500 connections, which will crush a PostgreSQL server whose max_connections is 200. You have three options: shrink per-instance pools (risks latency under load), add a pooler like PgBouncer (the usual answer), or both.

Transaction pooling vs session pooling. PgBouncer has three pooling modes. Session pooling assigns a server connection to a client for the whole session, which barely multiplexes. Transaction pooling assigns a server connection per transaction, which is where the 10-50x efficiency comes from. Statement pooling goes further but is rarely usable in practice.

Transaction pooling has a well-known conflict: prepared statements. Most drivers use named prepared statements by default, and a prepared statement is session state, which does not survive being handed to a different server connection mid-session. If your application relies on prepared statements, test this combination explicitly before deploying. The failure mode is subtle: queries that worked in staging start failing under transaction pooling in production.

Other things that break under transaction pooling: LISTEN/NOTIFY, advisory locks, temporary tables, and SET commands that change session state. If you need any of these, keep those workloads on session pooling or a direct connection.

Read replica pool splitting. Reads and writes should not share one pool against the primary. Give the application two pools: a small pool for the writer, and a larger pool spread across read replicas. Watch replica lag; a read pool that serves stale data is worse than a slow primary. Route lag-sensitive reads (read-your-own-write flows like "user just saved, now show it") to the primary.

At 100x load, the bottleneck moves. First the per-instance pools saturate, then the pooler CPU becomes the limit (PgBouncer is single-threaded per instance, so run several), then the database itself hits CPU or I/O limits and no amount of pooling helps. Pooling solves the connection-count problem. It does not solve the query-load problem.

Security Considerations

Credential rotation without restart. Database passwords should rotate, and rotating them should not require redeploying every service. Load the DSN from a secrets manager at startup and support reloading it on signal (SIGHUP is the common convention) or on a schedule. Better: use short-lived credentials. IAM database authentication (RDS, Cloud SQL) issues tokens valid for 15 minutes; the pool refreshes them automatically and there is no long-lived password to leak.

Least-privilege roles per service. Each service gets its own database role with grants for exactly the tables it needs. The reporting service does not get write access. The API service does not get access to billing tables it never touches. This limits blast radius when (not if) a credential leaks, and it makes pg_stat_activity useful for attributing load to services.

TLS everywhere. Set sslmode to verify-full between application and pooler and between pooler and database, with proper certificate validation. Connection strings with sslmode=disable are common in internal networks and wrong. Internal networks get breached too.

Connection strings are secrets. Never commit them, never print them in logs, never paste them into chat. A DSN contains host, port, username, and password in one convenient string for an attacker. Store it in a secrets manager, inject it as an environment variable or mounted file, and mask it in any diagnostic output. If your logging pipeline ever records a connection string (it happens during startup error dumps), treat it as a credential compromise and rotate.

Common Mistakes

  1. Pool too small. Requests queue, latency spikes. Fix by sizing correctly.
  2. Pool too large. Database overwhelmed by connections. Each PostgreSQL connection uses memory and CPU for backend processes.
  3. No timeouts. Hung queries hold connections forever. Always set acquisition and statement timeouts.
  4. Not monitoring. You cannot tune what you do not measure. Track pool utilization, wait times, and exhaustion events.
  5. Sharing one pool across very different workloads. OLTP queries and analytical queries have different characteristics. Consider separate pools or read replicas.

Operational Concerns

Monitoring. The metrics that matter:

This query shows what the database sees right now:

SELECT state, usename, count(*),
       max(now() - query_start) AS longest_running
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state, usename
ORDER BY count(*) DESC;

A pile of connections in idle in transaction means the application is holding connections across work it should not be holding them across. A pile in active with old query_start values means slow queries are eating the pool.

Alerting thresholds (starting points, tune to your workload):

Failure modes and runbooks.

Pool exhaustion cascade: a slow downstream dependency makes queries slow, connections pile up, the pool exhausts, requests fail, clients retry, and retries add more load. The runbook: first check pg_stat_activity ordered by query_start for the slow queries, kill the worst offenders if safe, then decide whether to scale the pool or fix the query. Adding pool capacity without fixing the query just delays the same failure.

Thundering herd on database restart: every application instance tries to reconnect at once, and the storm of new connections overwhelms the recovering database. Mitigate with connection retry backoff and jitter in the pool configuration, and stagger instance restarts. With PgBouncer, use PAUSE before the database restart and RESUME after, so client connections wait instead of hammering.

Failover: DNS-based failover leaves stale connections in pools pointing at the old primary. Pools need to detect dead connections (test-on-borrow or periodic validation queries) and recycle aggressively after a failover event. Test failover regularly; the first time you discover your pool does not handle it should not be during a real outage.

Circuit breaker integration. When the database is slow but not dead, the pool fills with in-flight queries and everything queues behind them. A circuit breaker on the database client trips when latency or error rate crosses a threshold, failing fast instead of queuing. This protects the pool (and the rest of the service) from a degraded database, and it gives the database room to recover instead of drowning it in queued work. Set the breaker threshold below the pool acquisition timeout so the breaker trips first.

The Checklist

Connection pooling is infrastructure. When it works, nobody notices. When it breaks, everything breaks. Invest the time to get it right.

More articles