The first time I put PgBouncer in front of a busy PostgreSQL cluster, it was an emergency. A deploy had multiplied the app-worker count, every worker opened its own dozen connections, and the primary fell over under a few thousand mostly-idle backends each holding a few megabytes of memory and a slot in the process table. PgBouncer in transaction mode fixed it in an afternoon. The week after, it caused two subtler outages that took much longer to understand, and both of them were my fault for not respecting what transaction pooling actually means.
This is the guide I wish I had read first: what transaction mode really does, exactly which session state it breaks, why the instinctive fix of raising max_connections makes everything worse, and how to size and watch the pool so it stays boring.
Why you need a pooler at all
PostgreSQL forks one backend process per connection. That architecture is robust and simple to reason about, but it does not scale to thousands of connections gracefully. Each backend costs memory, each one shows up in the lock manager and the proc array, and snapshot operations like GetSnapshotData get more expensive as the backend count climbs. Idle connections are not free either: they still hold a process and still participate in every snapshot scan.
The usual trap is the connection-per-worker application model colliding with autoscaling. You size max_connections at 300 for a calm day, the platform team adds replicas for a launch, and suddenly 40 app instances times 20 connections each want 800 backends. The database does not degrade linearly here; it falls off a cliff. PgBouncer sits in the middle, keeps a small number of real server connections open, and multiplexes many client connections onto them. That multiplexing is where the mode decision lives.
Session vs transaction pooling, honestly
PgBouncer has three modes. Session pooling hands a server connection to a client for the life of the client connection, which is basically a queuing layer: it protects the database from connection floods but does not reduce the number of live backends for busy clients. Statement pooling returns the server connection after every statement, which breaks multi-statement transactions and is almost never what you want. Transaction pooling assigns a server connection to a client for the duration of a single transaction, then returns it to the pool — and it is the only one of the three that actually shrinks the live backend count while clients stay busy.
Transaction pooling is the sweet spot: a hundred busy clients can share ten server connections, because at any instant most clients are between transactions. But the word "for the duration of a transaction" carries the whole catch. Anything your application stores on the server connection outside a transaction boundary silently vanishes, or worse, leaks to the next client that reuses that backend.
What transaction mode actually breaks
Here is the list I recite to every team adopting it, because every item on it has bitten someone I know:
- Session-level prepared statements. PREPARE creates a statement tied to the backend. In transaction mode the next EXECUTE may land on a different backend that has never seen it, so you get "prepared statement does not exist". PgBouncer 1.21 and later can track protocol-level prepared statements across backend swaps, which rescues drivers that prepare through the extended query protocol, but SQL-level PREPARE still breaks, and older PgBouncer setups must disable prepared statements in the driver.
- SET and session GUCs. SET work_mem or SET statement_timeout in a session, then the backend goes back to the pool and the next tenant inherits your settings. SET LOCAL inside a transaction is safe; bare SET is not.
- Advisory locks. pg_advisory_lock is session-scoped by default. Under transaction pooling you can hold the lock on one backend while your "locked" work runs on another. The transaction-scoped variant pg_advisory_xact_lock is the correct tool here.
- LISTEN/NOTIFY. LISTEN registers interest on a specific backend; transaction pooling takes that backend away. Listen-based queue consumers need session pooling or a dedicated direct connection.
- Temporary tables and WITH HOLD cursors. Both live on the backend. A temp table created in one transaction is invisible to your next transaction on a different backend.
None of this is a reason to avoid transaction pooling. It is a reason to grep the codebase for SET, PREPARE, pg_advisory_lock, LISTEN, and temp tables before you flip the mode, not after. The failures are confusing precisely because they are intermittent: they only appear when the backend roulette lands wrong, which in practice means under load, which in practice means during your busiest hour.
If your drivers prepare through the extended query protocol — JDBC past its prepareThreshold, pgx, most modern ORMs — PgBouncer 1.21 and later can keep those statements alive across backend swaps. It is a PgBouncer setting, not a PostgreSQL one; there is no matching GUC to set on the server:
-- In pgbouncer.ini (PgBouncer 1.21+; nothing to change on PostgreSQL itself):
-- max_prepared_statements = 200
-- On the server, watch how many prepared statements backends are holding:
SELECT count(*) FROM pg_prepared_statements;With that on, drivers like recent JDBC and pgbouncer-aware ORMs keep using prepared statements through the pooler. SQL-level PREPARE still does not survive transaction pooling, and on PgBouncer older than 1.21 the whole feature is absent — in both cases, set prepareThreshold=0 in JDBC or its equivalent and accept the extra parse cost.
Why raising max_connections is the wrong fix
When the pool saturates, the symptom is clients waiting, and the instinctive fix is to open the taps: raise max_connections on the server, raise pool sizes, let everyone in. This is treating the thermometer. Past a certain backend count, adding more active connections lowers total throughput, because lock contention, snapshot overhead, and context switching grow faster than useful parallelism. I have watched a primary do measurably more work per second after we cut its effective concurrency in half.
The correct response to pool saturation is almost always one of three things: find the queries holding server connections too long and fix them, size the pool deliberately for the server's real capacity, or scale reads to replicas. Throwing connections at a saturated database is throwing fuel at a fire. If you are diagnosing lock pile-ups behind the pool, our notes on PostgreSQL lock manager contention cover the server-side symptoms you will see.
server_reset_query and pool hygiene
Because backends are reused across clients, the obvious question is who scrubs each server connection before the next tenant gets it. The honest answer in transaction mode is: nobody, by default. server_reset_query does default to DISCARD ALL, but PgBouncer only runs it when the backend is released if server_reset_query_always is switched on, and out of the box that is off — in transaction mode no reset happens at all. The pooler is trusting you not to leak session state, which is exactly why the breakage list above deserves a codebase grep before you flip the mode.
Two things are worth knowing. First, you can switch server_reset_query_always on and get a real DISCARD ALL between tenants, wiping session GUCs, prepared statements, advisory locks, and temp tables; it runs when the backend is released back to the pool and costs a round trip per transaction, which is visible on very high-churn workloads — one more reason to keep transactions short. Second, if you set server_reset_query to something lighter for performance, you own the cleanup: any session state your application leaves behind will leak to the next client. I have seen exactly one team do this deliberately and well, and several do it accidentally and spend a week chasing ghost SET commands. Leave it alone.
Sizing default_pool_size
The right pool size is derived from the server, not from the client count. Start from the number of connections the primary can actually execute well — for many OLTP workloads that is a small multiple of vCPU count, often in the 20-to-100 range total across all pools hitting that database — then divide among the PgBouncer pools (per user-database pair) that share it. default_pool_size of 20 with pool_mode=transaction handles a surprising amount of traffic when transactions are short.
Watch the ratio, not the absolute numbers. If cl_active (clients currently holding a server connection) is pinned at pool_size while sv_idle hovers near zero and clients queue, the pool is genuinely saturated. If sv_active is low and clients still queue, your transactions are long or slow, and the fix is query work, not pool size. reserve_pool_size and reserve_pool_timeout are a reasonable safety valve for brief bursts; max_client_conn should stay well above your expected client count so the queue forms in PgBouncer, where it is cheap, rather than in refused connections at the database.
Watching the pool: SHOW POOLS and friends
PgBouncer's admin console is a special database named pgbouncer; connect to it with psql as the admin user and the introspection commands are one word each:
-- Inside: psql -h pgbouncer-host -p 6432 -U admin pgbouncer
SHOW POOLS;
-- database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | ...
SHOW STATS;
-- total_xact_count, total_query_count, avg_wait_time, ...
SHOW SERVERS;
SHOW CLIENTS;The single most important metric there is avg_wait_time in SHOW STATS: how long clients wait for a server connection. Zero or near-zero means the pooler is invisible, which is the goal. Sustained wait time means the pool is the bottleneck and you now know exactly where to dig. cl_waiting above zero in SHOW POOLS is the same signal in instant form. Scrape these from your metrics system; the console is for humans, the trends are for alerts.
How MonPG fits into this picture
A saturated pool and a saturated server feel identical from the application side, and I have chased the wrong one more than once. MonPG monitors PostgreSQL today, so I watch the server half of that equation directly: active and idle backends against max_connections, wait events, long-running transactions, and pg_stat_statements history. When clients start queueing at PgBouncer, those numbers say immediately whether the server is out of capacity or out of patience. The PostgreSQL monitoring guide covers the baseline, and the MonPG blog has more on pool-adjacent failure modes. Get the server-side evidence in place and the pooler stays what it should be: plumbing.