Connection Pools Aren't Free

A connection pool is a cache of open database (or HTTP) connections that your application reuses instead of opening a new one for every request. The advice "use a connection pool" is ubiquitous and correct. But "use a pool" is not "understand the pool." The pool has a size, a timeout, an eviction policy, and a health check — and every one of these parameters can cause latency spikes, connection storms, or silent failures if misconfigured. This article explains what a connection pool actually does and why "just add a pool" is incomplete.

Why connections are expensive

A database connection is not a file handle. It is a server-side resource:

• Memory. Each PostgreSQL connection spawns a backend process with ~5–10 MB of memory (stack, per-connection state, query workspace). MySQL's per-connection memory is similar. 100 connections × 10 MB = 1 GB of server memory.

• Handshake. Each new connection requires a TCP handshake (1 RTT), authentication (1–2 RTTs), and SSL negotiation (1 RTT in TLS 1.3). At 10ms RTT, that's 30–40ms per connection — before any query runs.

• Server resources. The database allocates a process (or thread), file descriptors, and per-connection buffers for each connection. At scale, connection creation itself becomes a bottleneck.

The pool amortizes these costs: create N connections once, reuse them across all requests. The first request pays the connection cost. All subsequent requests get a warm connection.

Pool sizing: the formula that doesn't exist

The internet is full of pool sizing advice. Most of it is wrong because it ignores the actual bottleneck:

The naive formula: pool_size = 2 × num_cpu_cores + num_disk_spindles

This formula assumes the database is bottlenecked on CPU or disk. In practice, the bottleneck is often locks, I/O wait, or external calls — things the formula doesn't account for.

The correct approach: measure and tune.

pool_size = (total request rate) × (average query time) × (safety margin)
 
example:
  1000 requests/second
  average query time: 10ms = 0.01s
  safety margin: 1.5x
 
  pool_size = 1000 × 0.01 × 1.5 = 15 connections

This is the Little's Law approach: L = λW, where L is the average number of items in the system, λ is the arrival rate, and W is the average time in the system. The pool size should be enough to handle the concurrent demand without queuing.

The danger of too many connections:

PostgreSQL's max_connections default is 100. MySQL's max_connections default is 151. If your application has 100 instances each with a pool of 50 connections, the database sees 5,000 connections — far beyond what it can handle efficiently. PgBouncer or ProxySQL multiplexes these onto a smaller number of actual connections.

Connection exhaustion: the failure mode

When all connections in the pool are busy, new requests wait for a connection to become available. This wait has a timeout (typically 30 seconds). If the timeout expires, the request fails with a connection pool exhaustion error.

pool state:
  [conn1: busy, query running]
  [conn2: busy, query running]
  [conn3: busy, query running]
  [conn4: busy, query running]
  [conn5: busy, query running]
 
request arrives → waits for conn1, conn2, or conn3 to become free
  → 30 seconds pass → timeout → "connection pool exhausted"

The cascade: when connections are exhausted, requests queue. Queued requests hold application threads. Application threads hold HTTP connections. HTTP connections fill up. The load balancer health check fails. The instance is removed from the pool. Remaining instances get more traffic. Their pools exhaust. Cascade failure.

The fix: backpressure. When the pool is full, reject new requests immediately instead of queuing. Return a 503 (Service Unavailable) to the load balancer. The load balancer retries on another instance. This fails fast instead of timing out.

# good: fail fast when pool is exhausted
pool = ConnectionPool(
    max_connections=10,
    timeout=5,  # short timeout
    retry=False  # don't retry pool exhaustion
)
 
# bad: queue indefinitely
pool = ConnectionPool(
    max_connections=10,
    timeout=30,  # long timeout → holds threads
    retry=True   # retry → more pressure on pool
)

Idle connections: the silent killer

Connections that are not being used still consume resources:

• Server-side memory. The database holds the connection's memory even when idle.
• Firewall timeouts. Network firewalls and load balancers often drop idle connections after 60–300 seconds. The pool thinks the connection is alive; the server has closed it.
• DNS resolution. If the database IP changes (failover, migration), idle connections point to the old IP.

Connection validation prevents stale connections:

pool eviction policy:
  1. test-on-borrow: execute SELECT 1 before using the connection
  2. test-on-idle: validate periodically for idle connections
  3. min-idle-time: close connections idle for > N seconds

test-on-borrow adds latency (one extra round trip per connection checkout) but guarantees the connection is alive. test-on-idle validates less frequently and is the common default. min-idle-time closes stale connections proactively.

The trade-off: validation adds overhead. SELECT 1 costs ~0.5ms on a warm connection. If every request validates its connection, that's 0.5ms per request. For a pool that serves thousands of requests per second, this adds up. The practical balance: validate on idle (once per N seconds), not on every checkout.

The warm-up problem

After a database restart or pool reset, all connections are gone. The pool must re-establish them under load:

pool after restart:
  [empty]
 
request 1 → open connection → 30ms handshake → query
request 2 → open connection → 30ms handshake → query
...
request 10 → open connection → 30ms handshake → query
request 11 → pool is warm → reuse connection → query (2ms)

The first N requests (where N = pool size) pay the connection establishment cost. If the pool size is 20 and each connection takes 30ms to establish, the first 20 requests are 30ms slower than normal.

Pre-warming: some connection pools (HikariCP, c3p0) support minimumIdle or minPoolSize — the pool maintains a minimum number of idle connections at all times. After a restart, the pool immediately creates minPoolSize connections in the background.

Lazy initialization: other pools create connections on demand — the first request creates a connection, the second request creates another, etc. This avoids wasting connections but slows the first requests.

The right choice depends on your latency requirements. If the 99th percentile must be below 50ms, pre-warm the pool. If startup time matters more, lazy-init is fine.

What this means for your code

1. Size the pool based on Little's Law, not rules of thumb. Measure your request rate and query time. Calculate the concurrent demand. Size the pool to handle that demand with a safety margin.

2. Set a short timeout and fail fast. A 5-second pool timeout is better than 30 seconds. A request that waits 30 seconds for a connection is already failed from the user's perspective.

3. Validate idle connections. Test-on-idle every 30 seconds prevents stale connection errors. Test-on-borrow is expensive; use it only for high-reliability requirements.

4. Pre-warm after restarts. minimumIdle or minPoolSize keeps connections ready. The cost is memory; the benefit is predictable latency.

5. Monitor pool metrics. Active connections, idle connections, wait time, and exhaustion count. These are the leading indicators of pool problems — the trailing indicator is user-visible latency.