When web traffic surges, our immediate engineering instinct is almost always to scale out resources. If our backend receives 1,000 concurrent HTTP requests, we naturally assume the database pool needs hundreds of open connections to avoid blocking. Yet in production, dialing max_connections or pool limits up to 300 or 500 usually does not make queries faster. Instead, it frequently leads to skyrocketing CPU utilization, degraded query latency, and eventual database lockups.
Database concurrency does not scale linearly with connection counts. Understanding how PostgreSQL processes work under the hood reveals why a small, well-bounded pool often outperforms an oversized one by orders of magnitude.
The Cost of the Process-Per-Connection Model
Unlike modern web runtimes that leverage lightweight threads or coroutines, PostgreSQL uses a classic process-per-connection architecture. Whenever a client opens a connection, the database server forks a dedicated OS process (postgres: backend) to manage session state, parsing, query execution, and working memory.
Each process consumes roughly 5 to 10 MB of memory just to exist, not counting work_mem allocated during complex sorts or hash joins. When you configure your services to open 400 connections simultaneously, you force the Linux kernel to schedule 400 heavy OS processes across a finite set of CPU cores.
If your database instance has 8 vCPUs, the CPU can literally execute only 8 instruction streams at any given microsecond. The remaining 392 processes sit in execution run-queues. The operating system must constantly swap register states, flush CPU L1/L2 caches, and arbitrate lock contentions in memory. Instead of running your SQL queries, your database cores spend most of their cycles executing context switches.
The Proven Pool Sizing Formula
The PostgreSQL community and the maintainers of the high-performance HikariCP connection pool popularized a practical rule of thumb for connection sizing:
optimal_pool_size = (cpu_cores * 2) + effective_spindle_count
On modern cloud infrastructure running NVMe SSDs, effective_spindle_count is typically counted as 1 because SSDs do not suffer from mechanical rotational latency.
For a standard 8-core database server, this formula yields an optimal pool size of roughly:
(8 * 2) + 1 = 17 connections
Seeing a pool size of 17 or 20 for an application serving thousands of requests per second often looks unsettling. However, a query that executes in 2 milliseconds on an unburdened 8-core CPU allows a single connection to finish 500 queries every second. A pool of 20 connections can smoothly process 10,000 queries per second—provided queries are not held up by context-switching bottlenecks.
Queueing: In the App vs. In the Database
Traffic spikes inevitably exceed instantaneous database execution capacity. The critical architectural decision is where that queue should live.
When your connection pool is oversized, the queue forms inside the database kernel. Hundreds of queries compete for disk bandwidth, shared buffer locks (LWLock), and CPU cycles simultaneously. All queries slow down together, raising the average latency of every request in flight.
When your connection pool is tightly bounded, the queue forms in your application memory or connection pooler. Application threads wait a few milliseconds for an available database handle. The database processes each query at maximum hardware efficiency, completes it, and immediately yields the handle to the next waiting client. Queueing in Node.js, Go, or Java application memory costs almost nothing; queueing inside the PostgreSQL engine costs system stability.
Bridging Horizontal Scaling with PgBouncer
In microservice architectures or horizontally scaled web clusters, having 30 app containers each holding 10 connections still results in 300 active database sessions.
To prevent this fan-out from overloading PostgreSQL, place an intermediate proxy like PgBouncer in front of the database running in transaction pooling mode:
; /etc/pgbouncer/pgbouncer.ini
[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
reserve_pool_size = 5
In this topology, thousands of client microservices can open persistent TCP connections to PgBouncer. PgBouncer holds the client requests in memory and multiplexes them over just 20 to 25 real PostgreSQL server connections, releasing each connection back to the pool the moment a transaction finishes.
Practical Optimization Steps
To tune your existing database for sustained high throughput, start with these baseline adjustments:
- Benchmark before increasing limits: Check
pg_stat_activityfor connections inactivevsidlestates. If active query latency climbs as connection count rises, your pool is too large. - Set strict connection timeouts: Configure short acquire timeouts on the client (e.g., 2000ms) rather than letting requests hang indefinitely.
- Eliminate idle-in-transaction connections: Code that makes third-party API calls while holding open database transactions locks up pool slots without executing work. Keep transactions strictly scoped to database writes.
Restricting connection counts feels counterintuitive, but aligning database concurrency with underlying CPU cores ensures predictable latency, higher queries-per-second, and robust system stability under peak load.
Loading comments…