PgBouncer: Scaling PostgreSQL to 10,000+ Connections

PostgreSQL creates an OS process per connection consuming ~5-10MB RAM each. PgBouncer multiplexes thousands of application connections onto a small pool of database connections.

The Three Pooling Modes

Session Pooling: One server connection per client session. Supports SET, advisory locks, LISTEN/NOTIFY. Least efficient. Use for legacy apps.

Transaction Pooling (Recommended): Server connection assigned only during transactions. 10-100x more efficient. Cannot use session-level features.

Statement Pooling: Connection returned after each statement. Autocommit only. Rarely used.

Production Configuration

[databases]
production = host=postgres.internal port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 5432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
default_pool_size = 25       # Server connections per DB/user pair
max_client_conn = 10000      # Max application connections
reserve_pool_size = 5        # Extra connections for bursts
max_db_connections = 100     # Total server connections per DB

server_connect_timeout = 15
client_idle_timeout = 600
server_idle_timeout = 600
server_lifetime = 3600

server_reset_query = DISCARD ALL

Correct Pool Sizing

The formula: optimal connections = cpu_cores * 2 (SSD) or cpu_cores * 4 (HDD). Beyond this, performance degrades from context switching overhead.

def calculate_pool_size(max_connections=200, num_instances=2, cpu_cores=8):
    available = max_connections - 3  # Reserve for superuser
    optimal = cpu_cores * 2
    per_instance = available // num_instances
    return {
        'pool_size': min(optimal, per_instance),
        'max_clients': per_instance * 200,
        'reserve': max(5, per_instance // 5),
    }
# Example: 8 CPU, 200 max_connections, 2 instances
# Result: pool_size=16, max_clients=2800

Monitoring

psql -U pgbouncer -d pgbouncer

SHOW POOLS;
-- cl_waiting > 0: Pool exhausted! Clients are waiting
-- maxwait > 100ms: Connection latency too high
-- sv_idle high: Pool may be oversized

SHOW STATS;  -- req/s, avg query time

RELOAD;            -- Apply config without restart
PAUSE production;  -- For maintenance
RESUME production;

services:
  pgbouncer-exporter:
    image: prometheuscommunity/pgbouncer-exporter:latest
    environment:
      DATA_SOURCE_NAME: "postgresql://pgbouncer:pass@localhost:5432/pgbouncer"
# Key alerts:
# cl_waiting > 0 sustained => pool exhausted
# maxwait_seconds > 0.1 => connection latency too high

Transaction Mode Limitations

# BROKEN: Prepared statements
# EXECUTE may run on different server connection than PREPARE
# Fix: conn = psycopg3.connect(dsn, prepare_threshold=None)

# BROKEN: SET search_path = myschema
# Next query may run with default search_path
# Fix: ALTER USER appuser SET search_path TO myschema;

# BROKEN: Advisory locks (pg_advisory_lock/unlock on different connections)
# Fix: Use SELECT FOR UPDATE or Redis distributed locks

# BROKEN: LISTEN/NOTIFY
# Fix: Dedicated non-pooled connection for LISTEN

High Availability

backend pgbouncer_pool
    mode tcp
    balance leastconn
    server pgbouncer1 10.0.0.1:5432 check
    server pgbouncer2 10.0.0.2:5432 check
    server pgbouncer3 10.0.0.3:5432 check

3 PgBouncer instances x 25 pool size = 75 total PostgreSQL connections serving 10,000+ application connections.

Troubleshooting Guide

# "too many connections" errors
psql -c "SELECT count(*) FROM pg_stat_activity;"
# Near max_connections: reduce pool size or add instances

# Slow queries after switching to PgBouncer
psql -c "SHOW search_path;"
# Missing session config: use ALTER USER/DATABASE instead of SET

# Pool exhaustion during spikes
watch -n1 'psql -U pgbouncer -d pgbouncer -c "SHOW POOLS;"'
# Increase reserve_pool_size, reduce reserve_pool_timeout

PgBouncer lets a 200-connection PostgreSQL instance serve thousands of concurrent connections—one of the highest ROI infrastructure changes available.