Taming Database Connection Pools: PGBouncer and Connection Spikes
Why throwing more CPU at your database server won't fix high connection counts, how PostgreSQL processes handle memory per backend, and how to size transaction-level connection pools properly.
A common rite of passage for backend engineering teams occurs when a successful product launch causes web application instances to auto-scale. Suddenly, the PostgreSQL database becomes unresponsive, CPU spikes to 100%, and application logs drown in FATAL: remaining connection slots are reserved for non-replication superuser connections.
The instinctive reaction is to increase max_connections in postgresql.conf from 100 to 1,000. This is almost always a catastrophic mistake that degrades performance further.
Understanding PostgreSQL's process architecture reveals why connection counts must be strictly capped, and how connection pooling solves the problem.
The Process-Per-Connection Cost
Unlike multithreaded databases, PostgreSQL uses a process-based model. Every client connection spawns a dedicated operating system process (postgres backend worker):
- Memory Consumption: Each process allocates its own private memory buffers (
work_mem, connection state, cached plans). Having 500 idle connections can quietly consume several gigabytes of RAM that could otherwise be used for the database buffer cache. - Context Switching Overhead: When hundreds of active processes contend for a limited number of CPU cores (e.g., 4 or 8 vCPUs), the operating system kernel spends significant CPU cycles merely switching context between processes, contending for internal locks (LWLocks) rather than executing queries.
A PostgreSQL database on an 8-core server will typically deliver higher aggregate throughput with 30 active connections than with 500 connections.
Sizing Pools with PGBouncer
The solution is placing a lightweight connection pooler like PGBouncer between your application tier and PostgreSQL. PGBouncer can hold tens of thousands of client connections open while funneling them into a small, fixed pool of actual database backends.
PGBouncer supports three pooling modes:
- Session Pooling: A server connection is tied to the client for the entire duration of the client connection. Useful for legacy applications that rely heavily on session-level variables, but saves little concurrency.
- Transaction Pooling (Recommended): A server connection is only leased to a client for the duration of a single transaction. As soon as the transaction commits, the connection is instantly returned to the pool to serve another client.
- Statement Pooling: The connection is returned after every single SQL statement. Breaks multi-statement transactions; rarely used.
A standard pgbouncer.ini configuration for high-throughput transaction pooling:
[databases]
app_db = host=127.0.0.1 port=5432 dbname=app_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
# Total client connections PGBouncer can accept from application pods
max_client_conn = 5000
# Strict limit of actual connections opened to PostgreSQL
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
The Optimal Connection Formula
PostgreSQL core developers have long cited an empirical formula for sizing database connection pools:
$$\text{Connections} = (\text{CPU Cores} \times 2) + \text{Disk Spindle Count}$$
For a modern cloud server with 8 vCPUs and NVMe SSD storage, the ideal number of concurrent connections actively executing queries is typically between 20 and 35.
By placing PGBouncer in transaction mode in front of PostgreSQL, hundreds of serverless Lambdas or auto-scaled container pods can connect freely without ever overwhelming the database engine.