PGBouncer and the Real Limits of Postgres Connection Pooling

Session pooling assigns a Postgres connection to a client for the whole session, which is the safest mode and the one that changes the least about how your app behaves, at the cost of pooling almost nothing at scale. Transaction pooling releases the connection back to the pool as soon as a transaction commits, which is where most of the real capacity gain comes from, but it breaks any feature that depends on session state persisting across transactions: prepared statements, session-level advisory locks, and settings that aren't scoped to a transaction.

Statement pooling, the most aggressive mode, releases the connection after every single statement and is rarely worth the compatibility cost outside very specific batch workloads.

The instinct is to size the pool close to your application's expected concurrency. The right number is usually much smaller than that, because Postgres itself performs best with a connection count in the low hundreds, and a well-tuned pool of real connections behind PGBouncer can serve a large multiple of that in concurrent app requests if your queries are fast.

If you're raising the pool size to fix a slowdown, check your query latency first. A pool that's too small surfaces as connection wait time in PGBouncer's own stats, not as a Postgres error, which is why it's easy to misdiagnose as a database problem.

• Prepared statement errors after switching to transaction pooling, which show up as a driver-level error mentioning an unknown prepared statement name; disable the driver's prepared statement cache or switch that connection to session pooling.
• Connection pile-up during a deploy, where old and new app instances briefly hold two full pools at once; stagger your rollout or add a grace period before old instances release connections.
• A pool sized for steady-state traffic that falls over during a spike, which PGBouncer's pool status command will show as a growing wait queue well before Postgres itself reports any error.
• Idle-in-transaction connections holding a slot indefinitely because application code opened a transaction and never closed it, which a statement timeout on the Postgres side will catch.

Say your app runs fifteen backend instances, each configured for fifty concurrent database connections, for a theoretical peak of several hundred direct connections, well past what a single Postgres instance handles comfortably. Route all fifteen instances through PGBouncer in transaction pooling mode with a much smaller real pool to Postgres.

Under normal load, that small real pool comfortably serves far more app-side connections because each one is only held for the duration of a transaction, not the duration of a request. The real test is whether your queries are fast enough that a transaction holds its connection for milliseconds, not seconds; if queries are slow, pooling can't fix the underlying capacity problem.

Teams that instrument Postgres carefully often leave PGBouncer as a black box in between, which is exactly backwards for diagnosing a connection problem, since PGBouncer's own stats are usually where the early warning shows up first. Its pool status output reports active, waiting, and idle connections per pool, and a client wait time that's climbing is the earliest signal that demand is outrunning your real connection count, well before Postgres itself shows any strain.

Export those stats into the same dashboard you already use for Postgres metrics, so an on-call engineer investigating a slowdown checks one place instead of two, and doesn't rule out pooling as the cause just because Postgres's own metrics still look calm.

A single PGBouncer pool shared by every workload means a slow analytical query and a fast transactional lookup are competing for the same limited real connections. Splitting into separate pools, one for latency-sensitive transactional traffic and another for longer-running reporting or batch queries, keeps a slow report from starving the checkout flow of connections it needs immediately.

This costs a bit more configuration up front but pays for itself the first time a badly written report query would otherwise have taken down an unrelated feature by exhausting the shared pool.