Why Connection Pooling Matters
Every PostgreSQL client connection is backed by a dedicated OS process on the server, forked at connect time and holding a fixed chunk of memory (for its process state, catalog caches, and per-backend buffers) for the connection's entire lifetime. Establishing that process is comparatively expensive — tens of milliseconds — and with too many concurrent connections, context-switching overhead and memory pressure degrade throughput well before the max_connections limit is even reached. PgBouncer sits between applications and PostgreSQL as a lightweight proxy, maintaining a small pool of real backend connections and multiplexing many more client connections onto them, so PostgreSQL never sees more backend processes than the pool actually needs.
Cricket analogy: It is like a stadium that can only field 11 players on the pitch at once: instead of every fan trying to bowl a ball themselves, a rotation system lets a small squad of active bowlers handle deliveries for the whole crowd's requests, cycling through efficiently.
Pooling Modes
PgBouncer offers three pooling modes with very different guarantees. Session pooling assigns a client a backend connection for the entire duration of its session, releasing it only on disconnect — the safest mode, compatible with everything, but offering the least connection reuse. Transaction pooling assigns a backend connection only for the duration of a single transaction, returning it to the pool the instant the transaction commits or rolls back, which allows far higher client-to-backend multiplexing ratios but breaks any feature that depends on session state persisting across transactions. Statement pooling, the most aggressive mode, releases the connection after every single statement and is rarely used because it breaks multi-statement transactions entirely.
Cricket analogy: Session pooling is like reserving a hotel room for a player's entire tour; transaction pooling is like only booking the room for the duration of each individual match, freeing it the moment the game ends for the next team; statement pooling would be booking the room per single delivery, which makes no sense for a match that needs continuity.
; pgbouncer.ini
[databases]
shop = host=pg-primary.internal port=5432 dbname=shop
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 600
log_connections = 1
log_disconnections = 1Configuring PgBouncer
The core sizing knob is pool_size (per database/user pair), which bounds how many real PostgreSQL backend connections PgBouncer will open, while max_client_conn bounds how many client connections PgBouncer itself will accept — the whole point being that max_client_conn can be much larger than pool_size because most clients are idle most of the time in transaction mode. auth_type and auth_file configure how PgBouncer authenticates incoming clients (commonly scram-sha-256 backed by a userlist.txt of usernames and password hashes), and server_idle_timeout controls how long an unused backend connection sits open before PgBouncer closes it and gives the slot back to the pool.
Cricket analogy: pool_size is like the number of match balls officially available on the field at once, while max_client_conn is like the total number of players in the squad who might ever need one — far more players exist than balls in play at any given moment.
Transaction pooling is incompatible with any feature that relies on session-level state persisting across transactions: prepared statements (unless using PgBouncer's protocol-level prepared statement support in newer versions), session-level advisory locks, LISTEN/NOTIFY, temporary tables, and SET-based session configuration all behave unpredictably or fail outright in transaction mode, because the underlying backend connection can change between transactions.
Monitoring and Tuning
PgBouncer exposes a special administrative console database (typically named pgbouncer) accessible via a normal psql connection, where SHOW POOLS reports per-pool client and server connection counts (cl_active, cl_waiting, sv_active, sv_idle), SHOW STATS reports query throughput and average query/wait times, and SHOW CLIENTS / SHOW SERVERS list individual connections. A consistently non-zero cl_waiting count is the clearest signal that pool_size is too small for the current load and clients are queueing for a backend connection.
Cricket analogy: It is like a ground announcer reading out a live queue report — how many players are currently batting versus waiting in the pavilion — so team management immediately knows if the rotation is falling behind and needs more overs allocated.
Setting default_pool_size too high defeats the purpose of pooling: if PgBouncer's total configured pool sizes across all databases approach or exceed PostgreSQL's max_connections, you lose the protection pooling was meant to provide, and a burst of activity can still exhaust the database's connection limit. Size pools deliberately below max_connections, leaving headroom for superuser/maintenance connections.
- Each PostgreSQL connection is a full OS process with fixed memory overhead, making raw connection scaling expensive.
- PgBouncer multiplexes many client connections onto a small pool of real backend connections.
- Session pooling is the safest but least efficient mode; transaction pooling gives the best multiplexing but breaks session-dependent features.
- pool_size bounds real backend connections per database/user; max_client_conn bounds accepted client connections, which can be much larger.
- Transaction mode is incompatible with session-level state: advisory locks, LISTEN/NOTIFY, temp tables, and uncoordinated SET statements can misbehave.
- SHOW POOLS and SHOW STATS in the pgbouncer admin console are the primary tools for diagnosing undersized pools via cl_waiting.
- Pool sizes across all databases should stay comfortably below PostgreSQL's max_connections to preserve pooling's protective benefit.
Practice what you learned
1. Why is PgBouncer's connection pooling valuable for PostgreSQL?
2. In PgBouncer's transaction pooling mode, when is a backend connection returned to the pool?
3. What does the pool_size setting control in PgBouncer?
4. Which PostgreSQL feature commonly breaks or behaves unpredictably under PgBouncer's transaction pooling mode?
5. What does a consistently high cl_waiting value in SHOW POOLS indicate?
Was this page helpful?
You May Also Like
PostgreSQL High Availability Patterns
Learn how to design a highly available PostgreSQL deployment combining replication, automated failover, connection routing, and backups to meet real RTO/RPO targets.
Streaming Replication Explained
Learn how PostgreSQL ships write-ahead log records from a primary to standby servers in real time, and how to configure, secure, and monitor a physical replication topology.
Partitioning Large Tables
Learn how PostgreSQL's declarative partitioning splits large tables into manageable pieces for faster queries, easier maintenance, and efficient data lifecycle management.