28 / 32 · 5 min
Why the database refuses to talk to you
A query that ran in 5ms during testing times out in production, and the database is not busy. The usual answer is that the application never got a connection.
A connection is expensive
Opening one means a TCP handshake, authentication, and on Postgres a new operating system process with its own memory. That is tens of milliseconds and several megabytes, per connection.
So a server that opens one per request spends more time connecting than querying, and a traffic spike opens thousands at once.
Pooling
A connection pool opens a fixed number at startup and lends them out:
request -> borrow from pool -> query -> return to poolThe connection is never closed, so the cost is paid once. When every connection is busy, the next request waits, and that wait is where the mysterious timeout comes from: the query is fast, the queue for a connection is not.
Sizing it
The instinct is to make the pool large. That is usually wrong.
A database with 8 cores cannot genuinely run 500 queries at once. It runs 8 and context switches between the rest, so every query gets slower and none finish sooner. A common starting point is a small multiple of the core count, and the right value is found by measuring.
The arithmetic people miss is that the pool is per application instance. Ten instances with a pool of 20 each is 200 connections, and Postgres defaults to allowing 100. The eleventh instance then fails to start.
| Symptom | Likely cause |
|---|---|
| Fast query, slow request | waiting for a connection |
| "too many connections" | instances times pool size exceeds the limit |
| Fine at low traffic, collapses at peak | pool too small, or leaked connections |
| Gradual degradation over days | connections not returned to the pool |
Leaks
A connection borrowed and never returned is gone until the process restarts. It happens when an error path skips the release, which is why every language has a construct for it: try/finally, with, defer, using. Releasing by hand at the end of the happy path is the bug.
Transactions hold connections
A transaction occupies its connection from BEGIN to COMMIT. Calling an external API inside a transaction holds a connection for the length of that call, and a slow third party then exhausts the pool. Keep transactions short, and do slow work outside them.