Postgres Connection Pooling with PgBouncer: Production FAQ
Answers to the most common production questions about Postgres connection pooling with PgBouncer — pool modes, sizing, transaction mode caveats, and common failure patterns.
A Postgres application can exhaust connection slots when concurrent requests or application instances open too many connections. PgBouncer can share a smaller pool of server connections, with trade-offs that depend on its pooling mode and configuration.
What Does PgBouncer Do?
Postgres creates a process for each client connection. These processes consume memory and the database has a hard ceiling on the total number of connections (max_connections, which defaults to 100 on many managed instances). When your application opens more connections than Postgres can handle, requests fail.
PgBouncer sits between your application and Postgres. The application maintains a pool of connections to PgBouncer. PgBouncer maintains a much smaller pool of connections to Postgres, and multiplexes application requests across them. Your application can have hundreds of open connections to PgBouncer while Postgres sees only tens of actual connections.
What Are the Three Pool Modes?
Session Mode
Session pooling assigns a server connection for the client session and returns it to the pool when the client disconnects. It supports session state but offers less multiplexing for long-lived clients.
Transaction Mode
A Postgres server connection is held only for the duration of a transaction, then returned to the pool. This is the most efficient mode and the one most production deployments use. A pool of 20 server connections can serve hundreds of application connections if transactions are short.
Caveats in transaction mode: Several Postgres features do not work correctly in transaction mode because they rely on per-session state:
SETand session-level configuration (the configuration resets when the connection returns to the pool)- Prepared statements (unless PgBouncer is configured to track them per client)
- Session-level advisory locks
LISTEN/NOTIFY- Temporary tables intended to persist across transactions
If your application uses any of these features, configure them at the connection level or use a dedicated connection that bypasses PgBouncer for those queries.
Statement Mode
Each individual SQL statement gets a server connection. Even more efficient than transaction mode but incompatible with multi-statement transactions. Rarely appropriate for application workloads.
How Many Connections Should the Pool Have?
The rule of thumb: the number of server connections in the pool should match the number of CPUs on the Postgres host, multiplied by a small factor based on your I/O wait characteristics. For an I/O-bound workload, the multiplier can be 2 to 4. For a CPU-bound workload, stay closer to 1 to 2.
A pool larger than the database can actually utilize adds queue time without adding throughput — connections wait in PgBouncer's queue rather than being served faster.
A reasonable starting point for a 4-core Postgres instance:
pool_size = 10
max_client_conn = 500Ten server connections serve five hundred application connections. Tune from there based on latency and queue depth metrics.
How Do I Handle Prepared Statements in Transaction Mode?
The most common incompatibility between ORMs and PgBouncer transaction mode is prepared statements. Most ORMs (Prisma, SQLAlchemy, Django ORM, Rails ActiveRecord) use prepared statements by default.
Option 1: Disable prepared statements in the ORM.
In Prisma, set pgbouncer=true in the connection string. In ActiveRecord, configure prepared_statements: false. In SQLAlchemy, set prepare_args={'prepared_statement_cache_size': 0} in certain adapters.
Option 2: Use prepared-statement tracking where supported.
A nonzero max_prepared_statements enables tracking for protocol-level named prepared statements in supported PgBouncer versions. SQL PREPARE statements are different; verify driver compatibility.
Option 3: Use session mode.
Accept lower connection efficiency in exchange for full compatibility. Appropriate when connection counts are manageable and transaction mode caveats are a maintenance burden.
What Is the server_reset_query Setting?
server_reset_query is the SQL command PgBouncer runs on a server connection before returning it to the pool, to clean up session state. The default is DISCARD ALL, which clears temporary tables, prepared statements, advisory locks, and session-level settings.
By default, server_reset_query runs in session pooling, not transaction pooling. Do not rely on it to preserve or reset application session state between transactions.
How Do I Monitor PgBouncer in Production?
PgBouncer exposes a virtual database called pgbouncer with admin commands:
-- Connect to the pgbouncer admin
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
-- Show pool status
SHOW POOLS;
-- Show client connections
SHOW CLIENTS;
-- Show server connections
SHOW SERVERS;
-- Show configuration
SHOW CONFIG;Key metrics to monitor:
cl_waiting: clients waiting for a server connection. If this is non-zero regularly, your pool is undersized.sv_idle: idle server connections available in the pool. If this is consistently zero, connections are fully utilized.avg_query_time: average query time through the pool. Spikes here indicate slow queries, not pool problems.
Export these metrics to your monitoring system (Prometheus via pgbouncer_exporter is common).
What Are the Most Common PgBouncer Failure Patterns?
cl_waiting queue buildup: All server connections are in use and new requests queue up. Symptoms: latency spikes, eventually timeout errors. Fix: increase pool size, reduce query duration, or add a read replica.
Connection string not routing through PgBouncer: After adding PgBouncer, the application still connects directly to Postgres on port 5432 instead of PgBouncer on port 6432. Verify the connection string in each service.
Prepared statement errors after pool reassignment: Statements prepared on one server connection are not available on another. Fix: check driver and PgBouncer compatibility, configure supported statement tracking, or use session pooling.
PgBouncer single point of failure: A single PgBouncer process is itself a bottleneck and a failure point. For production workloads, run multiple PgBouncer instances behind a load balancer, or use a managed pool (AWS RDS Proxy, Supabase's built-in pooler).