PostgreSQL Connection Pool Sizing & Little's Law: Why 15 Connections Outperform 200 Under High Concurrency

The High-Connection Fallacy in Relational Systems

One of the most persistent anti-patterns in backend engineering is the belief that high web concurrency requires an equally large database connection pool. When web applications experience latency spikes or connection timeouts during traffic surges, the reflexive response of many engineering teams is to raise max_connections = 500 in postgresql.conf and scale application pool sizes accordingly. In almost every scenario, this degrades database performance exponentially.

Unlike multithreaded database engines like MySQL or modern NoSQL data stores, PostgreSQL operates on a process-per-connection architecture. Every client connection spawns a dedicated operating system process (postgres: user db [client]). When 200 or 500 active client connections contend for resources on an 8-core or 16-core CPU, the database does not execute 500 queries in parallel—it forces the Linux kernel into catastrophic scheduler thrashing.

1. CPU Cache Invalidation and Context-Switching Thrashing

A physical CPU core can only execute instructions for one process at any given nanosecond. When 200 processes compete across 8 cores, the Linux Completely Fair Scheduler (CFS) must rapidly preempt running processes, saving and restoring registers, page tables, and stack pointers tens of thousands of times per second. This context switching incurs three massive penalties:

• Scheduler Overhead: Up to 35% of total CPU cycles are burned solely deciding which process runs next, rather than executing query execution plans.
• L1/L2 Cache Invalidation: Each context switch evicts the CPU's Level 1 and Level 2 cache lines, forcing the CPU to fetch query metadata and row buffers from high-latency main RAM.
• Lock Contention on Shared Buffers: PostgreSQL uses shared memory (shared_buffers) protected by lightweight locks (LWLocks) and semaphores. When hundreds of processes contend for lock acquisition on the same page headers, lock spinning burns CPU cycles without performing useful work.

2. Little's Law and the Mathematics of Queuing

Connection pool sizing is governed by Little's Law, a fundamental theorem of queuing theory:

L = λ × W
Where:
L = Average number of concurrent requests in the system (Active Connections)
λ = Request arrival rate (Transactions per Second)
W = Average response time (Query Latency in seconds)

Consider a high-throughput production API handling 2,500 database transactions per second (λ = 2,500). If queries are well-indexed and maintain an average latency of 4 milliseconds (W = 0.004s):

L = 2,500 × 0.004 = 10 Connections

Under these realistic metrics, exactly 10 concurrent database connections are required to sustain 2,500 transactions per second. Provisioning 100 or 200 connections does not increase capacity—it simply introduces an unmanaged queue inside the PostgreSQL kernel.

3. The Canonical PostgreSQL Connection Sizing Formula

Extensive benchmarking by PostgreSQL core contributors and hardware vendors yielded the battle-tested sizing formula for dedicated PostgreSQL hosts:

Optimal Connections = (CPU_Cores × 2) + Effective_Spindle_Count

For modern cloud instances with NVMe SSD storage, the effective spindle count represents the number of concurrent I/O operations the storage subsystem can handle before queueing begins (typically 1 to 4). On a dedicated 8-core NVMe server:

Optimal Connections = (8 × 2) + 2 = 18 Connections

Restricting active backend connections to 18 guarantees that all active queries remain resident in CPU hardware caches and execute to completion in minimum time.

4. PgBouncer in Transaction Mode: Multiplexing at Scale

To support thousands of frontend client connections while restricting PostgreSQL to its optimal connection window, a lightweight connection pooler must sit between application servers and the database. PgBouncer in pool_mode = transaction is the industry standard:

# /etc/pgbouncer/pgbouncer.ini
[databases]
app_db = host=127.0.0.1 port=5432 dbname=app_db pool_mode=transaction

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

# Client Connection Capacity
max_client_conn = 10000
default_pool_size = 20
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 2

In transaction mode, PgBouncer assigns a server connection only for the precise duration of a database transaction. The moment a COMMIT or ROLLBACK finishes, the physical PostgreSQL connection is released back into the pool to serve another waiting client, enabling 10,000 active web workers to share 20 physical database processes with zero context thrashing.