Connection pooling, explained properly

Connection pool sizing is done by copying a number from a blog post, and the number is usually wrong in a specific and expensive way.

why connections are expensive#

In Postgres specifically, each connection is a separate operating system process with its own memory. A few megabytes of baseline, plus work memory for sorting and hashing, plus its share of shared buffer access.

Four hundred connections means four hundred processes. The scheduler is context switching between them, they are contending for the same locks and buffers, and the memory is largely wasted because most of them are idle.

The counterintuitive result, which has been measured many times: throughput frequently goes down as connection count goes up, past a fairly low threshold.

More connections does not mean more concurrency. It means more contention.

the actual number#

A widely used starting formula:

connections = (core_count × 2) + effective_spindle_count

For an 8-core machine with SSD storage, that is somewhere around 16 to 20.

That number seems shockingly low to people running pools of 100 or more. It is correct, and the reasoning is straightforward: a query is either using CPU or waiting on I/O. You need enough connections to keep the cores busy and to have some work queued behind I/O waits. Past that, additional connections are queued at the database instead of queued in your pool, and queueing at the database is worse because it consumes resources.

Test it. Take your load test, run it at pool sizes of 10, 20, 40, 80, and 160, and plot throughput and p99 latency. The curve rises, flattens, and then degrades. Most people are on the degrading side and have never looked.

the pooler layers#

Three, and they do different things.

Application-side pool. In-process, reuses connections across requests. Every ORM and database driver has one. This is the minimum.

External pooler — PgBouncer, pgcat, or a cloud provider's equivalent. Sits between your application and the database and multiplexes many client connections onto few server connections.

This is what you need when you have many application instances. Twenty instances with a pool of 20 each is 400 connections to the database, even if each instance is mostly idle. A pooler collapses that to the number the database actually wants.

The database's own limit. max_connections. Set it lower than you think — it is a safety valve, and setting it high does not make the database faster, it makes the failure mode worse.

pooling modes, and the one that bites#

External poolers have modes and choosing wrong causes subtle correctness bugs.

Session pooling. A client gets a server connection for the duration of its session. Safe, and provides little multiplexing benefit.

Transaction pooling. A server connection is assigned per transaction and returned after commit. This is where the big multiplexing win is, and it is what most people want.

The catch: anything that depends on session state breaks.

• Prepared statements (unless the pooler supports them explicitly, which newer ones do)
• SET at the session level
• Session-level advisory locks
• LISTEN/NOTIFY
• Temporary tables
• WITH HOLD cursors

If your ORM uses server-side prepared statements by default — many do — you must either disable them or use a pooler that handles them. This is the single most common transaction-pooling problem and it manifests as intermittent errors under load, which is a miserable thing to debug.

Statement pooling. A connection per statement. Maximum multiplexing, breaks multi-statement transactions. Almost never what you want.

the settings that matter#

Beyond size:

Connection timeout. How long a request waits for a connection before failing. This should be short — a few seconds. A request waiting thirty seconds for a connection has already been abandoned by its caller.

Idle timeout. Return connections to the database when not in use. Important with many application instances.

Max lifetime. Recycle connections periodically. This handles the case where a database failover happens and your pool is holding connections to the old primary — without a max lifetime, some pools hold those indefinitely.

Validation. Test a connection before handing it out, or handle the failure on first use. Something has to deal with connections that died while idle.

the diagnostic#

SELECT state, count(*), max(now() - state_change) AS longest
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;

What you are looking for:

• Many idle in transaction — the worst state. A transaction is open and doing nothing, holding locks and preventing vacuum. This is an application bug: a transaction opened and not committed, usually because of an early return or an exception path.
• Many idle — pool is oversized. Harmless but wasteful.
• Many active with long durations — queries are slow; the pool is not the problem.

idle in transaction is the one to alert on. It is always a bug and it causes outages that look like database problems and are application problems.

the summary#

Size your pool from a load test, not from a blog post. It is smaller than you think. Use an external pooler in transaction mode if you have many application instances, and check your prepared statement behavior when you do.