Scaling PostgreSQL Connection Pooling with PgBouncer (3820)

Technical Overview

At Votion Cloud we manage thousands of PostgreSQL instances serving millions of requests per second. The single biggest bottleneck we see is connection overhead: each new backend process consumes ~10 MB of RAM and 2–5 ms of fork() latency. PgBouncer solves this by multiplexing thousands of client connections over a handful of persistent server connections.

Pool Modes & When to Use Them

• Session pooling – safest for applications using prepared statements, SET commands, or advisory locks. One server connection per client session.
• Transaction pooling – releases the server connection at transaction end. Ideal for stateless workloads (ORMs, REST APIs). Reduces idle connections by 90%.
• Statement pooling – releases after every statement. Maximum density but breaks multi-statement transactions and named prepared statements.

Kernel & PgBouncer Tuning

# /etc/sysctl.d/99-pgbouncer.conf
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_tw_reuse = 1
fs.file-max = 1000000

Apply with sysctl --system. In pgbouncer.ini:

[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 250
min_pool_size = 50
reserve_pool_size = 50
reserve_pool_timeout = 5
max_db_connections = 500
max_user_connections = 500
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

Prepared Statement Handling

Transaction pooling discards prepared statements at transaction end. Two strategies:

1. Disable prepared statements in the driver (preferQueryMode=simple for pgJDBC, prepared_statements=false for Npgsql).
2. Use PgBouncer 1.18+ prepared_statements = true with pool_mode = transaction – it caches prepared statements per server connection and re-prepares transparently.

Monitoring & Alerting

Key metrics (exposed via SHOW STATS and Prometheus exporter):

• cl_active / cl_waiting – client pressure
• sv_active / sv_idle / sv_used – server pool utilisation
• avg_wait_time – queue latency (target < 1 ms)
• avg_query_time – backend latency

Alert when cl_waiting > 0 for > 30 s or sv_used / max_db_connections > 0.85.