PostgreSQL uses a process-based client architecture. Unlike multi-threaded databases like MySQL, PostgreSQL forks a separate operating system process for every connected client.

Each backend process allocates roughly 5 to 10 Megabytes of RAM for query state and execution buffers. When 500 web application container instances connect directly to PostgreSQL, the database host consumes 4 Gigabytes of RAM solely maintaining idle connections. Under peak loads, context-switching between hundreds of competing backend processes degrades query throughput and triggers FATAL: sorry, too many clients already.

Deploying PgBouncer as an intermediate connection pooler is essential for maintaining database stability under high concurrency.

Choosing the Right Pool Mode

PgBouncer operates in three distinct pooling modes:

  • Session Pooling: Client holds a dedicated server connection until disconnect. Lowest resource savings; acts like a traditional proxy.
  • Transaction Pooling (Recommended): Client borrows a server connection only for the duration of a single database transaction (BEGIN ... COMMIT). As soon as the transaction finishes, the connection returns to the pool to serve other clients. A single physical Postgres connection can serve dozens of active web clients.
  • Statement Pooling: Connection returns after every individual SQL statement. Does not support multi-statement transactions.

    • Sizing the Pool: The Little's Law Formula

    Many administrators assume setting max_connections = 1000 in postgresql.conf improves performance. In reality, optimal PostgreSQL performance is achieved with a relatively small connection pool that matches available hardware resources:

    \text{Pool Size} = (\text{Core Count} \times 2) + \text{Effective Spindle Count}

    For an 8-core NVMe server:

    \text{Optimal Connections} = (8 \times 2) + 1 = 17 \text{ to } 25 \text{ connections}

    By keeping the number of active database processes close to the number of physical CPU cores, you eliminate context switching and maximize CPU cache hit rates.

    Production pgbouncer.ini Configuration

    ini
    [databases]
app_db = host=127.0.0.1 port=5432 dbname=app_production user=app_user

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

# Pooling mode
pool_mode = transaction

# Connection limits
max_client_conn = 5000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 5

# Timeouts & Keepalives
server_idle_timeout = 600
client_idle_timeout = 120
server_connect_timeout = 15
query_timeout = 30

    With PgBouncer handling connection pooling, thousands of stateless application containers or serverless API instances can query the database concurrently without exhausting system memory or causing database connection crashes.