Optimizing PostgreSQL Connection Pooling with PgBouncer (2411)
Technical Overview
Engineering breakdown of Optimizing PostgreSQL Connection Pooling with PgBouncer (2411). Bare-metal hardware performance requires isolated kernel parameters, NUMA-aware scheduling, and zero-copy networking paths. We'll dissect the connection lifecycle from client handshake through PgBouncer's pool manager to PostgreSQL backend attachment, identifying latency hotspots at each transition.
Connection Pooling Architecture
PgBouncer operates in three pooling modes: session, transaction, and statement. Each mode trades off connection reuse granularity against transaction semantics preservation. For high-throughput OLTP workloads, transaction mode typically delivers optimal balance—releasing connections back to the pool after each COMMIT/ROLLBACK while maintaining session-level prepared statements and advisory locks.
[databases]
app_db = host=pg-primary port=5432 dbname=production pool_mode=transaction
[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 = 10000
default_pool_size = 100
min_pool_size = 20
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 500
max_user_connections = 500
Kernel-Level Tuning for Connection Storms
When PgBouncer manages 10,000+ concurrent client connections, the OS network stack becomes the primary bottleneck. Critical sysctl parameters:
# /etc/sysctl.d/99-pgbouncer.conf
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 15
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_keepalive_intvl = 10
net.ipv4.tcp_keepalive_probes = 3
fs.file-max = 2000000
Apply with sysctl --system. Verify PgBouncer's effective limits via SHOW CONFIG and SHOW POOLS admin commands.
Prepared Statement Lifecycle Management
PgBouncer's transaction pooling mode requires careful prepared statement handling. The DEALLOCATE ALL behavior on connection release can cause 26000 (invalid_sql_statement_name) errors if application code assumes statement persistence across transactions.
-- Application pattern that breaks with transaction pooling
PREPARE get_user(int) AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user(42);
COMMIT;
-- Connection returned to pool, DEALLOCATE ALL runs
EXECUTE get_user(43); -- ERROR: prepared statement "get_user" does not exist
Solution: Use PREPARE with explicit DEALLOCATE per transaction, or leverage PgBouncer's prepare_threshold setting (default 0 = disable) to auto-prepare after N executions:
[pgbouncer]
prepare_threshold = 5
eBPF/XDP kernel filter evaluates TCP/UDP frames directly on server NIC.
Monitoring & Alerting Strategy
Key metrics to scrape via PgBouncer's SHOW STATS and SHOW POOLS:
- pool_wait_time - Time clients spend waiting for available connections (target: < 5ms p99)
- server_lifetime - Average backend connection age (detects connection leaks)
- query_count / tx_count - Throughput indicators
- avg_req / avg_recv - Network payload sizing
Prometheus exporter configuration:
# pgbouncer-exporter config
scrape_interval: 15s
static_configs:
- targets: ['pgbouncer-0:6432', 'pgbouncer-1:6432']
labels:
cluster: 'production'
role: 'connection-pooler'
# Alerting rules
groups:
- name: pgbouncer
rules:
- alert: PgBouncerPoolExhaustion
expr: pgbouncer_pool_waiting_count / pgbouncer_pool_max_client_conn > 0.8
for: 2m
labels:
severity: critical
annotations:
summary: "PgBouncer pool {{ $labels.instance }} at 80% capacity"
- alert: PgBouncerHighWaitTime
expr: histogram_quantile(0.99, pgbouncer_pool_wait_time_bucket) > 50
for: 5m
labels:
severity: warning