PgBouncer in Production: Pool Modes and the Trade-offs Most Teams Get Wrong

PgBouncer is one of the most useful pieces of Postgres infrastructure that small teams reach for too late and configure too quickly. The problem it solves is real: a single Postgres instance has a hard limit on simultaneous connections (the default max_connections is 100, and each connection consumes meaningful memory and CPU even when idle), while a typical application stack with a few services and worker pools can easily try to hold thousands of connections open. PgBouncer multiplexes the application's many connections onto a much smaller pool of actual Postgres connections, often at a 100:1 ratio or higher.

The catch is that PgBouncer offers three different pooling modes, each with different semantics for what Postgres features still work. The choice between them is one of the most consequential database infrastructure decisions a team makes, and the documentation lays out the trade-offs in a single paragraph that is easy to read past. We have run PgBouncer (and PgBouncer-equivalent connection pools) in front of various database workloads across DocuMint, CronPing, FlagBit, and WebhookVault, and the failure modes are consistent enough to be worth a longer treatment than the docs give them.

What PgBouncer actually does

The basic idea is simple. The application opens a connection to PgBouncer instead of directly to Postgres. PgBouncer maintains a pool of real Postgres connections (the server pool) and assigns one to each incoming application connection (the client connection) according to the pool mode. When the client is done, the server connection returns to the pool to be reused.

The benefit at the application level is that the application can hold open many lightweight connections to PgBouncer without worrying about exhausting Postgres. The benefit at the database level is that Postgres only ever sees a small number of long-lived connections, which is what it is optimized for. PgBouncer also handles connection-level concerns like graceful failover, load balancing across replicas, and pause/resume for maintenance.

The cost is that PgBouncer is now a dependency. It adds a network hop, it can become a bottleneck if it is undersized or misconfigured, and the choice of pool mode determines which Postgres features the application can still use safely.

Session pooling: the safest mode that defeats the point

Session pooling is the simplest mode. Each client connection gets a server connection assigned for as long as the client is connected, and only when the client disconnects does the server connection return to the pool. From the Postgres side, this looks identical to a direct connection: every Postgres feature works exactly as documented.

The problem is that session pooling does not actually help much with the connection-exhaustion problem it was supposed to solve. If the application opens 500 long-lived connections to PgBouncer, PgBouncer needs 500 server connections to back them. The multiplexing benefit only kicks in when the application opens and closes many short-lived connections, which is the opposite of how most modern application frameworks behave.

Session pooling is the right choice when the application uses a feature that requires session state (more on which features below) and the team has not yet engineered around it. It is rarely the right long-term choice, because the actual connection-management benefit is small.

Transaction pooling: the right default with sharp corners

Transaction pooling is the mode where PgBouncer earns its reputation. A server connection is assigned to a client only for the duration of a single transaction; when the transaction commits or rolls back, the server connection returns to the pool immediately. The same client can use a different server connection for its next transaction.

The benefit is large. A workload of a few hundred clients each running thousand-row updates per minute can be backed by a server pool of 20 connections, because each connection is in use for only a few tens of milliseconds at a time.

The cost is that anything Postgres-side that depends on session state breaks. Most prominently:

• Prepared statements at the protocol level do not work, because the prepared statement lives on the server connection where it was created and the next transaction may use a different server connection. Most modern client libraries (psycopg, node-postgres, the Go pq driver) have configuration options to disable server-side prepared statements; the application has to use them. Statement plan caching at the server level is also lost.
• Session-level SET commands do not survive the transaction. SET search_path, SET timezone, SET application_name all need to be issued inside the same transaction as the queries that depend on them. The right pattern is SET LOCAL for transaction-scoped settings and connection-string parameters for things that need to be set on every connection.
• Temporary tables do not survive transactions and may be assigned to a different server connection on the next call. The workaround is to wrap creation and use in a single transaction or to use unlogged regular tables.
• LISTEN/NOTIFY does not work because the listener registration is per-connection. Applications using LISTEN/NOTIFY need a separate, dedicated connection that bypasses PgBouncer.
• Advisory locks taken at the session level (pg_advisory_lock) are bound to the server connection and may be invisible to subsequent transactions from the same client. Use transaction-scoped advisory locks (pg_advisory_xact_lock) instead.

The list looks intimidating, but most of these are addressable by application-side configuration changes. The team that runs into trouble is the team that flips to transaction pooling without auditing the application for these patterns and discovers them through subtle correctness bugs in production.

Statement pooling: the mode you almost never want

Statement pooling is the most aggressive mode: a server connection is held only for the duration of a single statement, and even multi-statement transactions are not supported. The application has to be written to never use multi-statement transactions, which rules out almost all real-world database use.

The mode exists for cases where the application is essentially read-only and stateless and the maximum possible connection multiplexing is required. For SaaS APIs of the shape we run, statement pooling is the wrong choice. It exists more as a documentation point than a practical option.

The configuration that gets missed

Beyond the pool mode, three settings deserve explicit attention:

default_pool_size is the number of server connections per database/user pair. The right value depends on workload, but the formula is similar to direct Postgres connection sizing: a few times the number of CPU cores on the database, with headroom for traffic bursts. Setting it too high turns PgBouncer into a connection storm and defeats the point; setting it too low causes client wait time. Monitor PgBouncer's SHOW POOLS output for clients waiting on server connections.

max_client_conn is the total number of client connections PgBouncer will accept. The default of 100 is much too low for the cases where PgBouncer makes sense; it should be sized to the application's actual concurrency limit, often in the thousands.

server_reset_query runs on every server connection when it returns to the pool. The default DISCARD ALL in transaction pooling mode resets all session state, which is what you want for safety; if you have engineered around the session-state issues, you can sometimes simplify or remove the reset for a small performance gain.

The operational signals to monitor

PgBouncer exposes several useful metrics through its admin interface:

• cl_waiting in SHOW POOLS: the number of clients waiting for a server connection. Anything above zero on a sustained basis means the pool is undersized for the workload.
• maxwait_us in SHOW POOLS: the longest a client waited for a server connection. Sudden spikes here indicate either traffic bursts or a slow query holding connections.
• SHOW STATS bytes received vs sent: useful for spotting unusually large query results or clients that pull more data than they need.
• Total server connections vs max_connections on the Postgres backend: leave headroom of at least 10-20% for direct administrative connections that bypass PgBouncer.

The single most useful operational habit is to graph cl_waiting over time. A healthy PgBouncer deployment shows zero almost always, with occasional small spikes during traffic bursts that resolve quickly. A struggling one shows sustained nonzero values that correlate with application latency increases.

Where PgBouncer does not help

PgBouncer reduces the number of server connections an application needs; it does not help with the underlying database load. If the database is slow because queries are inefficient, locks are contended, or the workload exceeds the hardware capacity, PgBouncer at best papers over the symptom and at worst makes diagnosis harder by hiding which client is responsible for which load.

The honest test is whether reducing the application's connection count fixes the problem. If yes, PgBouncer helps. If the problem is query latency or throughput at the database layer, PgBouncer is the wrong tool and adding it just adds another component to debug.

The deeper observation

PgBouncer is a piece of infrastructure that pays back its complexity at a specific point in a system's lifecycle and then stays around forever. The point where it earns its keep is when the application is running into Postgres connection limits and the team has already exhausted the easier interventions of reducing connection counts in the application's pool configuration. Once installed, PgBouncer becomes a permanent part of the stack and a permanent constraint on what database features the application can use freely.

The team that gets the most out of PgBouncer treats it as a thoughtful constraint: pick transaction pooling, audit the application for the session-state issues, document the constraints in the team's database guidelines, and monitor the relevant signals. The team that treats it as a magic connection multiplier ends up debugging subtle correctness bugs that turn out to be session-state assumptions that broke when the connection moved.