The Postgres process model and why raising max_connections degrades throughput

A team hits pool exhaustion. They raise max_connections from 100 to 1000. P99 gets worse. They raise it to 2000. The database starts OOM-crashing. Raising max_connections is the instinct; understanding why it fails is the skill.

The Postgres process model in detail

When a client connects, the postmaster (the supervisor process) forks a new backend process. Each backend has:

• Private memory: ~5–10 MB resident (per-query work memory, plan cache, libpq buffers). On a 16-core machine with 1,000 backends that is ~5–10 GB of private memory just for backends.
• A shared-memory slot: proc array entry (snapshot acquisition scans this), lock table entry, predicate-lock metadata. These structures are pre-allocated at startup, sized to max_connections. Doubling max_connections doubles the fixed shared-memory footprint.
• A kernel process with its own stack, file descriptor table, and scheduler entry.

Connection startup costs ~5 ms on the Postgres side (fork + initialization) plus TLS and auth on the network side.

Why processes and not threads? Postgres predates reliable pthreads on most Unix variants. The process model gives crash isolation (one backend OOM does not corrupt shared memory), simple private heap management, and easy parallel-query worker forking. The cost is irreducible per-connection overhead — which pooling absorbs.

Why raising max_connections degrades throughput

Naive teams raise max_connections from 100 to 1,000 when they see pool exhaustion. Four things go wrong:

1. Shared-memory scaling: max_connections = 1000 pre-allocates ~500 MB+ of fixed shared memory just for proc array, lock hash, and predicate-lock structures — measurable on small instances.

2. Context-switch overhead: at 1,000 active backends with load, kernel scheduling time becomes a measurable fraction of CPU. Each OS process competes for the scheduler.

3. Lock-manager contention: proc array iteration during snapshot acquisition is O(active_backends). At 1,000 active backends, every BEGIN scans 1,000 entries. Lock table scans and MultiXact buffer contention scale similarly.

4. Postmaster fork queue: connection establishment requires the postmaster to fork a new process; at high connection rates this serialises. PgBouncer’s ~2 KB per client connection vs Postgres’s ~10 MB per backend is the core asymmetry.

The throughput curve is non-linear: a 16-core Postgres peaks around 32–48 active backends and degrades past that. Adding backends past the peak means each backend gets a smaller share of CPU while paying more contention overhead.

The practical max_connections cap

The Postgres community guidance (unchanged for 25+ years): cap max_connections at a few hundred. The practical range for production OLTP is 200–500 — enough for admin connections, replicas, and some headroom above the pooler’s pool_size.

max_connections budget allocation:

• PgBouncer pool_size (primary) + reserve_pool_size — the real OLTP backends
• Replica connections (streaming replication)
• superuser_reserved_connections (default 3) — kept for emergency access
• Admin and monitoring connections
• PgBouncer pools for read replicas if any

Example: 8-core primary, pool_size = 24, reserve = 6, 2 replicas each with pool_size = 20, superuser_reserved = 3, admin/monitoring = 10. Total: 24 + 6 + 40 + 3 + 10 = 83. max_connections = 100 is fine; max_connections = 1000 is wasteful and harmful.