The database is slow, so someone raises the connection pool from twenty to a hundred. Latency gets worse. They raise it to two hundred. The database falls over.
This sequence happens in most companies at least once, and it happens because the pool is being treated as a capacity setting when it is actually a queue. Requests arrive, they wait for a connection, they hold it for the duration of a query, and they give it back. The pool size is the number of servers at the front of that queue. Everything about how it behaves follows from queueing theory, and the useful part of queueing theory fits in one line.
#Little's law tells you the size
For any stable system:
L = λ × WThe number of things in the system equals the arrival rate times the time each one spends there. For a connection pool, L is connections in use, λ is queries per second, and W is how long a query holds its connection.
A service doing 500 queries a second where each query takes 20 milliseconds has, on average, 500 × 0.02 = 10 connections in use. That is the whole calculation. A pool of ten is at full utilisation, which means any burst queues. A pool of fifteen has headroom. A pool of a hundred has eighty-five idle connections that exist only to make the database's life harder.
The number that people get wrong is W. It is not the query's execution time as reported by the database. It is the time the connection is checked out: the round trip, the query, the result transfer, and in the case of a transaction, everything the application does between BEGIN and COMMIT, including the HTTP call it makes to a third party while holding the connection open. A five millisecond query inside a transaction that also waits three hundred milliseconds on a payment API holds a connection for three hundred and five milliseconds. Little's law does not care where the time went.
Measure W from the pool's own metrics, as checkout duration, not from the database's slow query log.
#A bigger pool is slower past the core count
The intuition that more connections mean more throughput assumes the database has the capacity to run them concurrently. It does not, past a point that arrives early.
A PostgreSQL connection is a process. Each active one competes for the same cores, the same shared buffers, the same lock manager and the same disk. With more active connections than cores, the database is context switching between queries rather than running them, and every query takes longer because it is sharing a core with several others. Throughput plateaus and then declines, and latency rises the whole way.
The often-quoted starting point, from the HikariCP maintainers, is that the optimal active connection count is close to twice the core count plus the number of disk spindles, and on modern hardware with SSDs it is close to the core count times two. An eight-core database server saturates somewhere around sixteen to twenty active connections. The two hundred connection pool that was supposed to fix latency was two hundred processes fighting over eight cores.
This is not an argument for tiny pools. It is an argument that the pool's job is to keep the database at its ideal concurrency and make everything else wait in the application, where waiting is cheap, rather than in the database, where waiting means holding a process and a lock.
#Every instance has its own pool
The number that matters to the database is not one service's pool size. It is the sum of every pool from every instance of every service that connects.
Twenty pods with a pool of twenty each is four hundred connections. Scaling to fifty pods for a traffic peak is a thousand. The database was fine at four hundred, mostly idle, and the moment traffic arrives and those thousand connections all become active at once it is running a thousand processes on sixteen cores and every query is slow, which makes every connection stay checked out longer, which makes the application open more.
PostgreSQL's max_connections is the hard ceiling, and hitting it means new connections are refused outright. The pool that cannot get a connection to the database fails every request, and nothing on the application side can fix that.
The fix at that scale is a pooler between the applications and the database. PgBouncer in transaction mode holds a small number of real connections to PostgreSQL and multiplexes thousands of client connections across them, assigning a real connection only for the duration of a transaction. The application pools stay wide for latency; the database sees twenty.
Transaction pooling has one well-known cost: session state does not survive between transactions, because the next transaction may land on a different server connection. Session-level settings, advisory locks held across transactions, and LISTEN all break. Named prepared statements were the classic casualty, and PgBouncer 1.21 added protocol-level support for them, so on a current version that particular problem is gone. Check the rest before switching.
#Exhaustion cascades upward
Here is what a pool exhaustion actually looks like in production, in order.
The database slows for some unrelated reason: a vacuum, a lock, a bad plan on one query. W goes up. By Little's law, connections in use go up, because the same arrival rate is now holding connections longer. The pool fills. New requests wait for a connection.
Those waiting requests are holding something too: a worker thread, an event loop tick, a goroutine, a memory allocation, and above all a client on the other end that is waiting with its own timeout. Latency at the service edge rises to the pool wait time plus the query time. The clients' timeouts fire. The clients retry. Arrival rate λ goes up, at exactly the moment W is already up.
More arrivals, longer holds, and a pool that was full before any of this. This is the retry storm with a connection pool as the amplifier, and it does not recover on its own even after the database speeds back up, because the queue of waiting requests is now deeper than the pool can drain before the next wave of retries arrives.
Two settings decide whether this is a blip or an outage.
The pool's acquire timeout is how long a request waits for a connection before giving up. The default in most libraries is thirty seconds, which is thirty seconds of a client holding a socket for a request that will not succeed. Set it to something close to the latency budget of the request, a few hundred milliseconds for a web request, and fail fast with a 503. A request that fails in 200 milliseconds can be retried with backoff. A request that fails in thirty seconds has already caused the retry storm.
The pool's maximum size, as above, should not be the number that makes the queue disappear. It should be the number the database can serve concurrently. The queue is supposed to exist. Its depth is the signal. Its wait time is what you alert on.
#One pool per purpose
A background job that runs a report holding a connection for forty seconds and a web request that needs one for five milliseconds should not be drawing from the same pool. When ten reports run at once, ten of the twenty connections are gone for forty seconds, and the web tier is now running on half a pool with no change in its own traffic.
Give the batch work its own pool with its own small ceiling. It will queue, which is fine; it is batch work. The web pool stays whole. This is the same isolation argument as rate limiting per client: the cheap way to stop one workload from starving another is to give them separate lines.
The same applies to a single request that does a lot. A request that holds a connection across three sequential external calls should not be holding it at all. Read what you need, release, do the slow thing, acquire again to write. The transaction boundary and the connection boundary do not have to be the request boundary.
#Connections have to die
A connection that lives forever accumulates state and outlives its DNS answer. When the database fails over to a replica, the old primary's address is still what every long-lived connection is pointed at. Pools that never recycle connections keep talking to a server that is now read-only or gone.
Set a maximum lifetime, thirty minutes is common, so connections are replaced on a rolling basis and a failover is picked up within that window without any restart. Set an idle timeout lower than the database's own, so the pool does not hand out a connection the server already closed. Enable TCP keepalive so a connection whose peer vanished without a FIN is detected in minutes rather than never.
And validate on checkout only if you must. A test query before every use adds a round trip to every request to catch a condition that lifetime and keepalive already handle. Most pools offer it; most deployments should leave it off.
#The four numbers to graph
Connections in use, connections idle, requests waiting for a connection, and the p99 of acquire wait time. The first two tell you if the pool is sized for the load. The third tells you it is exhausted. The fourth tells you what that is costing every request, and it is the one to alert on, because it starts rising before the third is nonzero for long, and it is measured in the units the client cares about.
If acquire wait is climbing and connections in use are pinned at the maximum, do not raise the maximum. Look at W, find what is holding connections longer than it should, and fix that. Raising the maximum treats the queue as the problem when the queue is the messenger.
#The one-line summary
A pool is a queue with a fixed number of servers. Size it from arrival rate times hold time, keep it under what the database can run at once, fail fast when it is full, and treat its wait time as the health of every request behind it.
#References
- HikariCP: About Pool Sizing, the argument for small pools, with the benchmark
- PgBouncer: features and pooling modes
- PostgreSQL documentation: connections and authentication, for
max_connectionsand keepalive settings - John Little, A Proof for the Queuing Formula L = λW, 1961