Database Connection Pooling: What Actually Causes Exhaustion
We diagnosed a client's recurring 'too many connections' Postgres error and the first assumption — that traffic had simply outgrown the connection limit — turned out to be wrong. The actual cause was a long-running report query that held a connection open for over two minutes, combined with an application connection pool sized well above what the database's max_connections setting could actually support across all application instances combined.
Each application instance in that setup had its own pool sized for peak local load, but nobody had done the math across instances — five instances times a pool size of 50 each meant a possible 250 connections against a database configured for 200. Under normal load this never triggered, since not every instance hit peak simultaneously, but a deploy that briefly doubled instance count during a rolling update pushed it over the edge.
The fix had two parts: introducing PgBouncer in transaction pooling mode between the application and Postgres, which let us support far more application-level connections against a much smaller number of actual Postgres backend connections, and setting a hard statement_timeout so a stray long-running query couldn't hold a connection indefinitely and starve the pool.
Idle connections turned out to be a second, quieter contributor. Some of our background workers opened a database connection at process start and held it for the worker's entire lifetime, even between jobs. With enough workers running, that's a meaningful chunk of the connection budget spent on idle sits rather than active work. We moved those workers to acquire a connection per job rather than per process.
Our current default for any new service: PgBouncer or an equivalent pooler in front of Postgres from day one, connection pool sizes calculated as a function of total application instances rather than per-instance defaults, and a statement_timeout set aggressively enough to catch runaway queries before they become an exhaustion incident rather than after.
