Deep Dive: PostgreSQL Connection Pooling with PgBouncer (1086)

Technical Overview

Engineering breakdown of Deep Dive: PostgreSQL Connection Pooling with PgBouncer (1086). Bare-metal hardware performance requires isolated kernel parameters, but connection pooling remains the single highest-leverage optimization for PostgreSQL workloads. PgBouncer operates as a lightweight, single-threaded connection pooler that multiplexes thousands of client connections over a handful of backend PostgreSQL connections, dramatically reducing fork/exec overhead and memory pressure on the database server.

Pooling Modes

• Session pooling – Client retains a backend connection for the entire session. Best for applications using prepared statements, advisory locks, or session-level settings.
• Transaction pooling – Backend connection returned to pool after each transaction. Highest concurrency; requires application to avoid session-scoped features.
• Statement pooling – Connection returned after each statement. Rarely used; breaks multi-statement transactions.

Key Configuration Knobs

[databases]
app_db = host=pg-primary port=5432 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 100
reserve_pool_size = 20
reserve_pool_timeout = 5
max_db_connections = 200
max_user_connections = 200
log_connections = 1
log_disconnections = 1
stats_period = 60

Tuning default_pool_size relative to PostgreSQL's max_connections and CPU cores is critical. A rule of thumb: default_pool_size = (cpu_cores * 4) + effective_spindle_count. For cloud instances with NVMe, spindle count approximates 1.