Database Connection Pooling, and Why Yours Is Exhausted

Opening a database connection is not like opening a file. On PostgreSQL, the server forks an entire operating-system process for each one, allocates its own memory, and runs authentication and TLS setup before a single query moves. That is milliseconds of latency and megabytes of memory per connection.

Doing it per request would be absurd. So we keep a set of them open and lend them out. That is the whole idea — and every problem people have with pools comes from the lending, not the pooling.

What “exhausted” actually means

TimeoutError: timed out acquiring a connection from the pool (pool size 10)

This does not mean the pool is too small. It means every connection is currently in someone else’s hands and nobody is giving one back fast enough.

A pool of 10 with queries averaging 5 ms serves roughly 2,000 requests per second. If you are exhausting it at 50 requests per second, connections are being held for something like 200 ms each, and the question is what they are doing for 200 ms.

Raising the pool size makes the error go away and the underlying problem worse: now more connections pile onto a database that was already the constraint, and you have converted a fast failure into a slow, degraded system.

The four things holding your connections

A slow query. The honest case. Find it in pg_stat_activity, read the plan, add the index.

An N+1 loop. Fifty small queries in sequence hold the connection for the sum of fifty round trips. The connection is idle almost the whole time and still checked out. See the N+1 problem.

Network I/O inside a transaction. The worst one, and common:

await db.transaction(async (tx) => {
  const order = await tx.order.create({ data });
  await paymentProvider.charge(order);   // ← 800 ms, holding a connection
  await tx.order.update({ ... });
});

The HTTP call takes as long as it takes, and a database connection plus an open transaction waits for it. Ten concurrent checkouts and the pool is gone.

Leaked connections. An error path that returns without releasing. Modern frameworks with scoped helpers make this rare; manual acquire/release code makes it inevitable.

Sizing the pool

The counter-intuitive part: smaller is usually faster.

A database with 8 cores can genuinely execute about 8 queries at once. Give it 100 concurrent connections and it does not do 100 things — it context-switches between 100 things, each finishing later than it would have. Throughput plateaus, latency climbs, and everything looks like the database is slow.

A widely used starting formula:

pool size ≈ (2 × CPU cores) + effective spindle count

For an 8-core instance on SSD, somewhere around 16–20. In total, across every application instance — which is the part people miss. Ten containers with a pool of 20 each is 200 connections asking a database configured for 100.

total connections = instances × pool size   ≤   max_connections − reserved

Leave headroom for migrations, admin sessions and your monitoring.

Timeouts you should set

A pool without timeouts converts a slow query into a site-wide outage.

• Acquire timeout — how long to wait for a connection. A few seconds. Fail fast; a queued request holding a web worker is its own problem.
• Statement timeout — the database-side cap on a single query. Set it in Postgres (statement_timeout), not only in the client, so a runaway query is killed at the source.
• Idle-in-transaction timeout — kills sessions holding a transaction open doing nothing. This is the one that saves you from the payment-provider example above.

When you need an external pooler

Framework pools live inside one process. Once you have many processes, they stop coordinating.

PgBouncer sits between application and database and multiplexes. In transaction mode — the useful one — a server connection is assigned only for the duration of a transaction, so hundreds of idle client connections share a handful of real ones.

Two constraints come with transaction mode, and both bite in production:

• Session state does not survive. Prepared statements, SET commands, advisory locks and LISTEN/NOTIFY are tied to a session you no longer own between transactions. Many ORMs need prepared statements disabled to work behind it.
• It cannot fix slow queries. It multiplexes waiting, it does not reduce work.

Serverless is where this becomes mandatory rather than optional: every warm function instance holds its own connection, concurrency is elastic, and a traffic spike opens connections faster than the database can accept them. Postgres providers now ship HTTP or WebSocket drivers for exactly this reason.

What you have actually built

Not “more capacity”. A pool adds no throughput to the database — it cannot; the database does the same work either way.

What a pool gives you is a queue with a known limit, in front of a resource that degrades badly when overloaded. The value is the limit. When you raise it to stop seeing errors, you are removing the protection and keeping the load, and the next symptom will be less clear than the one you silenced.