Why my Postgres connection pool was the wrong size for two years

27 February · 5 minute read · postgres

The pool was set to two hundred because that number felt safe. Under load the application got slower, so at some point past me had raised it from a hundred, observed nothing in particular, and moved on. Both numbers were wrong in the same direction.

What the numbers actually looked like

Throughput barely moved across a sixteen-fold change in pool size, and the tail latency moved by a factor of four. The server has eight cores. Two hundred connections were never doing eight cores' worth of work in parallel — they were queueing inside the database, where I could not see it, instead of in the application, where I could.

The part that took two years

Nothing was broken. Requests succeeded. The dashboards showed a healthy green service with a latency graph nobody had a baseline for. It only surfaced because a customer mentioned that the export button felt slow, and the export button happened to be the one endpoint that held a connection for longer than a few milliseconds.

That is the uncomfortable lesson. The problem was visible the whole time, in a graph I looked at weekly, and I had no idea what the number was supposed to be.

What I changed

Pool down to twenty-four. A separate pool of four for the long-running report queries, so they cannot starve everything else. And one line in the runbook explaining why the number is small — that last part matters most, because without it the next person raises it again, for exactly the same reasons I did.