Connection pooling and PgBouncer

One connection is one process

PostgreSQL forks a backend process per connection. That process gets its own memory, its own catalog caches, its own plan cache, and a slot in every shared structure sized by max_connections, including the lock table and the array the snapshot mechanism walks. None of that is free, and none of it is returned until the client disconnects.

The consequence is that idle connections are not harmless. A thousand connections, of which twenty are doing anything, still cost a thousand process control blocks, a thousand sets of local memory, and a longer walk every time a snapshot is taken. Raising max_connections to make the errors stop makes the server slower at the workload it was already struggling with, which is why it is the wrong first move almost every time.

A pooler solves this by putting a small number of long-lived server connections in front of a large number of short-lived client ones. PgBouncer is the usual choice because it is a single lightweight process that speaks the wire protocol and does nothing else. The extreme case of a short-lived client is a process that exists for the length of one call and then exits, which is how a good many agent integrations are built and what makes their connection cost worth measuring separately; observability for AI agents has the two per-database numbers that show it.

Three pooling modes, one of which people mean

pool_mode decides when a server connection is handed back.

In session mode the client keeps its server connection until it disconnects. This is safe for everything and saves you only the cost of establishing connections, which is real but modest.

In transaction mode the server connection is returned at the end of every transaction. This is where the ratio becomes dramatic, because a web request that spends most of its life waiting on the application side holds no backend at all. It is also where things break, and the breakages are subtle rather than loud.

In statement mode the connection is returned after every statement and multi-statement transactions are rejected outright. It exists for specific sharding proxies and is not what anyone means by pooling.

What transaction mode breaks is everything that carries state across transaction boundaries on a server connection: session-level SET, LISTEN and NOTIFY, advisory locks taken outside a transaction, temporary tables, WITH HOLD cursors, and classically, prepared statements. PgBouncer gained protocol-level prepared statement support in recent versions through max_prepared_statements, which tracks and re-prepares them on whichever server connection the client lands on, so a driver using the extended query protocol now works. Anything else on that list still does not, and it fails at runtime rather than at deploy time, on whichever request happens to get a different server connection.

Reading the admin console

PgBouncer answers queries on its own admin database. SHOW POOLS is the one to run first, and the columns worth knowing are these:

SHOW POOLS;

cl_active is clients currently bound to a server connection; cl_waiting is clients that have sent a query and are waiting for one to free up. sv_active, sv_idle and sv_used split the server side into connections currently serving a client, connections idle and freshly checked, and connections idle but not yet re-verified. maxwait and maxwait_us give the wait of the oldest client in the queue.

maxwait is the number to alert on, and it is worth being precise about why. cl_waiting tells you how many clients are queued, which rises and falls with traffic and is a poor signal on its own. maxwait tells you how long the unluckiest one has been there, which is the thing the user is actually experiencing. A steady maxwait above zero means default_pool_size is smaller than the concurrency the workload needs; a maxwait that spikes to seconds and recovers usually means a slow query is occupying a pooled connection while everyone else queues behind it.

SHOW STATS gives per-database totals and averages, of which total_wait_time and avg_wait_time are the ones that correlate with user-visible latency. SHOW CLIENTS and SHOW SERVERS give the individual connections with their state, wait and application_name, which is how you find the one holding things up.

Reading the database side

From inside Postgres the pooler is invisible, so you diagnose by shape rather than by name:

SELECT state,
       count(*)                                                  AS sessions,
       max(now() - state_change)                                 AS longest_in_state,
       max(now() - xact_start) FILTER (WHERE xact_start IS NOT NULL)
                                                                 AS longest_transaction
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY sessions DESC;

A large idle count with longest_in_state in the hours is a pool that is oversized for the workload, which is wasteful but not urgent. A non-trivial count in idle in transaction, with longest_transaction growing, is urgent: each of those is holding a server connection the pool cannot reuse, and each is also pinning the cleanup horizon described in autovacuum and table bloat. Behind a transaction-mode pooler the effect compounds, because the application sees the pool exhaust and opens more client connections, which queue, which makes the requests slower, which makes them hold transactions open for longer.

idle_in_transaction_session_timeout is the blunt instrument that stops this becoming an outage. Set it at the database or the role, not at the session, so that an application which forgets to commit is disconnected rather than allowed to block a cluster. A session in that state is also holding whatever locks it took, so the queue it creates is the one described in lock contention and blocking trees. A monitoring connection left inside a transaction has a quieter version of the same problem, because from PostgreSQL 15 the statistics it reads are frozen for the life of that transaction: see the default that freezes your counters.

Sizing the pool

The common instinct is to make default_pool_size large enough that cl_waiting is always zero. That is the wrong target. Queuing at the pooler is cheap; queuing inside Postgres, where every waiting backend still holds a process and a lock-table slot, is expensive. A pool sized close to the number of connections the server can actually keep busy, with a queue in front of it, gives better tail latency than a pool sized to never queue.

The number the server can keep busy is bounded by cores and by disk concurrency, not by how many clients exist. Start from a small multiple of the core count, measure maxwait and query latency together, and raise default_pool_size only while both improve. reserve_pool_size gives a small overflow for brief spikes, and query_wait_timeout is the deadline after which a client that has been queued too long is told so rather than left hanging.

Three settings on the Postgres side matter alongside these. max_connections still bounds the total, and behind a pooler it can usually come down rather than up. server_idle_timeout in PgBouncer decides how long an unused backend is kept before it is closed, which is how the pool shrinks again after a spike. And superuser_reserved_connections is what keeps a session available for you when the pool has consumed everything else, which is the difference between diagnosing the problem and rebooting.

To put numbers on the pool size rather than a rule of thumb, the connections and pooling calculator works out how many connections your transaction rate ever keeps busy, how many the server will hand an ordinary role once the reservations come off, and how far apart those two are from what your instances are configured to open.