Connection pooling

You raised the pool size because requests were timing out, and it got slower, not faster — or you are staring at a max_connections setting with no idea what number belongs there. A pool is not spare capacity waiting to be turned up. What it buys first is one specific saving, and past a point that has nothing to do with the pool itself, adding more of it stops helping and starts costing you — which is how two hundred open connections end up slower than twenty.

What the pool is actually saving you#

Opening a connection is not free, and none of what it costs happens once a query starts running. A client and server negotiate a TCP handshake, the server authenticates the client, and then does whatever per-connection setup its architecture requires before a single row can be read or written. A pool’s first value is that it pays this cost once per connection and then reuses the result: instead of paying it again for every request, the application borrows an already-open connection, uses it, and hands it back for the next request to borrow.

How large that upfront cost actually is depends on the engine, and this is where it is worth being precise rather than general. PostgreSQL’s own documentation describes a process-per-user model: a supervisor process, the postmaster, listens for incoming connections and forks a whole new backend process for every one it accepts. That is a real operating-system process, with its own memory and its own place in the kernel’s scheduler, not a lightweight handle inside something already running — which is a large part of why opening a PostgreSQL connection costs comparatively more than opening one elsewhere. Other engines make a different trade, handing a connection to a thread inside one long-running process instead of forking a new one, which costs less to set up and less to hold open. The saving a pool buys you is real either way, but exactly how much it is worth depends on which of those two you are actually running — a pool sized around assumptions from a thread-per-connection engine and then pointed at a process-per-connection one is carrying over a cost estimate it never actually earned.

Why two hundred is slower than twenty#

A server sits behind a fixed amount of hardware — some number of CPU cores, some number of disks, a fixed amount of memory for caching pages. Up to the point where those resources are fully busy, an additional connection genuinely buys more work happening at once. Past that point, the resources are already saturated, and an additional connection does not add capacity. It adds another competitor for cores and disks that are already at their limit, plus more overhead for the server itself in scheduling between a larger set of connections, plus contention over shared structures — locks, buffer pool entries — that every one of those connections has to take a turn with.

The result is a queue, whether or not anything is labelled a queue anywhere you can see. Work that would have finished quickly behind a short line now finishes behind a much longer one, because the hardware underneath still processes at the same rate no matter how many connections are asking it to. That is the whole shape of why two hundred connections can be slower than twenty: twenty were already enough to keep the machine’s real capacity busy, and every connection beyond that adds nothing but itself to the queue and to the overhead of coordinating it.

There is a widely repeated pool-sizing formula built around exactly this idea, and it is worth naming carefully rather than copying. HikariCP’s own documentation publishes one, tied to CPU core count and an effective disk count that the same page defines as workload-dependent rather than fixed — zero when the active data set is fully cached, rising toward the real number of spindles as the cache hit rate falls — and says plainly that the formula is attributed to the PostgreSQL project and offered only as a starting point to test around, not a setting to take on faith. What matters here is not its multiplier but its shape: it is a function of the machine, which is the same argument this section has just made in prose — the ceiling is a property of the hardware underneath a given workload, not a number that travels with the application to a different one.

Everything in this section — cores, disks, locks, scheduling — is state that changes once the database is already running, not something decided by how a query is written, which is the same theme running through the persistence pillar this page belongs to: not SQL syntax, but what the engine underneath is actually doing while it executes.

Growing the pool moves the queue#

The failure this page exists for is raising the pool size to fix a timeout. Requests are queueing for a free connection, the pool looks like the exhausted resource, so someone grows it — and the queue does not disappear, it moves. Instead of waiting inside the application, where the wait is visible and bounded by the request’s own timeout, the work now waits inside the database itself, competing with everything else running there, where that timeout no longer governs it. The request that first timed out eventually gets served; the damage shows up in whatever was waiting behind it, not in the single call someone happened to be watching.

That is the same reflex as forcing a query onto an index instead of asking what the planner’s own cost estimate actually favoured: both replace a diagnosis with a setting change, and both leave the actual condition harder to see afterward rather than easier, because the thing that got adjusted was never the real bottleneck.

Latency does not stay confined to the query someone was chasing — it rises across queries that share nothing with each other except the machine underneath, because the connections now competing for the same cores and disks are carrying work from every part of the application, not just the part somebody was trying to fix.

What no pool size can do is create capacity the server does not have. If the database is genuinely saturated, every pool size is a choice about where the queue sits, not about whether one exists — smaller, and it queues in the application, where a timeout can fail a request cleanly; larger, and it queues inside the database, where it fails far less predictably. The fix that actually works is upstream: fewer or cheaper queries, or more server underneath. Pool tuning does not remove the queue. It only decides how gracefully the system behaves, and how visibly it fails, while the real problem underneath is still there.