PgBouncer has two modes that look similar and behave very differently. Most outages happen because teams pick the wrong one.
In session mode, a server connection is allocated for the entire client session—from CONNECT to DISCONNECT. The client owns that server connection completely. Everything Postgres supports works: prepared statements, SET commands, advisory locks, LISTEN/NOTIFY. Session mode is a transparent proxy. The cost is that you're still limited to roughly one server connection per active client; the pooler just adds a connection queue.
In transaction mode, the server connection is held only for the duration of a transaction and returned to the pool immediately after COMMIT or ROLLBACK. This is where the numbers get interesting: 1,000 application connections can share 20 server connections if they're not all active simultaneously. Cloud-native apps with short-lived requests see 95–99% connection reuse. Transaction mode is where PgBouncer delivers its core value.
But transaction mode breaks things. Specifically, it breaks anything that expects state to persist across transactions on the same connection:
- Prepared statements: PostgreSQL prepared statements are connection-scoped. In transaction mode, the server connection changes between transactions. The statement doesn't exist on the new connection. If your driver uses the extended query protocol (most do), this causes
ERROR: prepared statement does not exist. Fix: setprepared_statements=0in PgBouncer and usedisable_prepared_statements=truein your driver, or use statement-level caching in the application layer. - SET commands:
SET search_path TO myschemasets state on the server connection. When the connection is returned to the pool, that state persists for the next client. Fix: usesearch_pathin the role default or database default, not per-session SET. - Advisory locks:
pg_advisory_lock()is connection-scoped. In transaction mode, the lock is held on a server connection that will be reassigned. The lock appears to the application as held, but the session on the other end has changed. Fix: use the transaction-scoped variants (pg_advisory_xact_lock()) or avoid advisory locks entirely in a pooled environment. - LISTEN/NOTIFY: Pub/sub requires a persistent server connection. Transaction mode makes this impossible. Fix: use a dedicated connection for LISTEN outside PgBouncer, or use a Postgres logical replication approach.
PgBouncer vs Pgpool-II
Pgpool-II also does connection pooling, but its primary purpose is read replica load balancing and query routing. If you need to distribute reads across replicas, Pgpool-II is the right tool. If you need connection multiplexing—the common case—PgBouncer is simpler, faster, and easier to operate. Running both is not unusual in large deployments: PgBouncer at the edge for multiplexing, Pgpool-II for replica routing.
Sizing the pool
The canonical formula for Postgres server connections is: connections = (core_count × 2) + effective_spindle_count. For an 8-core server with SSDs (spindle count ≈ 1), that's 17 connections. Add 3 for system processes and you're at 20. Set max_pool_size in PgBouncer to that number per database per user. Total server connections = max_pool_size × pool_count.
Don't ignore server_idle_timeout. By default, idle server connections are held for 600 seconds. In transaction mode, connections cycle back to the pool immediately after each transaction, which means the pool fills up with idle connections if traffic drops. Setting server_idle_timeout = 60 lets the pool drain during low-traffic periods and avoids Postgres holding open idle connections longer than needed.
What pooling doesn't fix
Slow queries still block server connections for their entire duration. A 30-second query holds a server connection for 30 seconds regardless of pool mode. Lock contention is the same: a transaction waiting on a lock holds its server connection. PgBouncer reduces the connection count overhead; it doesn't reduce the work Postgres has to do. If your p99 query latency is 2 seconds and you have 500 concurrent users, the math doesn't improve with pooling alone.
The connection queue in PgBouncer is bounded by max_client_conn. If your application tries to open more connections than that, PgBouncer will refuse. This is a hard limit, not a soft one. Set it high enough for your expected concurrent client count, but monitor cl_waiting (clients waiting for a pool connection) in SHOW POOLS—sustained non-zero values there mean you need more server connections, not more client slots.
---
Published on Anethoth. Built at builds.anethoth.com.