One Hash Bloom Filters
Concept of the day · databases
Connection Pooling and Pool Exhaustion
A Postgres connection is a process; the pool is the real concurrency limit.
The mechanism. Each PostgreSQL connection is an OS process with its own memory (work_mem is per operation, per connection). A few hundred of them will exhaust a server that handles thousands of queries per second happily on twenty. So you put a pool in front: application-side (HikariCP, pg.Pool, SQLAlchemy) or a proxy (PgBouncer, pgcat, RDS Proxy).
The counterintuitive result. Throughput peaks at a small pool. The useful rule of thumb is roughly cores * 2 + effective_spindle_count. Beyond that, connections queue inside the database instead of outside it, context switching and lock contention rise, and p99 gets worse while the dashboard still shows idle CPU.
Pool exhaustion. The failure looks like a hang, not an error: every connection is checked out, and new requests block on acquisition until they time out. Three usual causes — a transaction left open across an HTTP call or a slow external API; a leaked connection on an error path that never returns it; and N+1 queries multiplying hold time. Always set an acquisition timeout, or a slow database becomes an unavailable application.
Transaction vs session pooling. PgBouncer in transaction mode multiplexes connections per transaction, which is far more efficient but breaks anything relying on session state: prepared statements, SET, advisory locks, LISTEN/NOTIFY, temp tables.
Takeaway. Your concurrency limit is the pool size, whether you chose it or not. Pick it deliberately and set a timeout on acquisition.