Postgres Connection Pools and Throughput Limits

At 10:02, an API tier begins timing out while its Postgres host still shows unused CPU. The first dashboard suggests an obvious correction: requests are waiting for database connections, so the pool limit is raised from 20 to 100. For several minutes, checkout waits fall. Then p99 latency climbs, transaction duration lengthens, lock waits appear, and the same endpoint begins exhausting its larger pool. The incident has not been fixed; the waiting room has been moved and the database has been asked to schedule more simultaneous work.

The failure is treating a connection as a unit of throughput. A connection is a session and, in Postgres's process-per-connection design, it normally corresponds to a backend process while active. It permits a transaction to submit work, hold snapshots, acquire locks, retain session state, and consume server resources. It does not make an index scan faster, add a CPU core, make a contended row less contended, or increase storage bandwidth. Pool sizing is therefore an admission-control problem, not a capacity-setting knob.

The pool is a queue before it is a cache

A connection pool has two jobs that are often conflated. Reusing established sessions avoids repeated authentication, process startup, TLS negotiation, and setup work. More importantly under load, the pool limits how many callers can concurrently enter the database. When all slots are checked out, later callers wait in the application or proxy rather than creating unbounded new backend processes. That waiting is visible and can be bounded by a checkout timeout, which makes the pool a deliberate overload boundary.

This is why a pool can improve system behavior without raising the database's maximum completion rate. If arrivals exceed the rate at which transactions finish, a queue forms somewhere. A small bounded pool holds that queue near the callers; a large pool moves more of it into runnable processes, lock wait queues, disk queues, and memory pressure inside Postgres. Neither placement changes the underlying service time. It changes observability, fairness, cancellation behavior, and how much collateral work overload creates.

Throughput follows service time and concurrency

For a stable workload, achievable throughput is governed by the amount of useful concurrent work the database can sustain divided by average transaction service time. If a transaction occupies a connection for 25 milliseconds and the database can execute 40 such transactions without material interference, the rough upper bound is 1,600 completions per second. Increasing the pool beyond 40 can only help if those 40 were not actually enough to cover idle gaps such as network waits or external coordination. Once the active set reaches the bottleneck, extra concurrency increases waiting rather than completions.

The important quantity is not query time in isolation but connection hold time. A checkout may cover transaction setup, multiple statements, client-side result processing, an ORM issuing incidental queries, an open transaction waiting on a remote service, and commit. A pool of 30 can therefore be saturated by modest query execution if connections remain checked out while application code does unrelated work. Which is what breaks when pool capacity is sized from request concurrency rather than from the lifetime of database ownership.

Postgres makes excess sessions expensive

Postgres does not multiplex many client sessions through a small fixed executor pool in the way some database engines do. A connected client is represented by a backend process, with per-session memory, process scheduling overhead, local execution state, and participation in shared-memory coordination. The exact cost varies with configuration and workload, but the direction is fixed: a larger active session population gives the operating system and database more entities to schedule, more state to inspect, and more work to coordinate.

Idle pooled connections are not equivalent to actively executing connections, but they are not free either. They consume connection slots and retain session state; large fleets can force higher connection limits and make failures more disruptive. Active backends are the sharper concern. When many are simultaneously parsing, planning, scanning, sorting, allocating memory, or waking on locks, scheduler overhead and cache disruption become part of each transaction's service time. That feedback means a larger pool can reduce throughput after a saturation point, not merely leave it unchanged.

Contention turns concurrency into slower work

CPU saturation is only one form of interference. Concurrent queries compete for buffer-cache residency and memory bandwidth, and simultaneous scans can convert a cache-friendly working set into more physical reads. Sorts and hashes may spill when memory policy and concurrent operators exceed available memory. On write-heavy paths, WAL insertion, fsync cadence, index maintenance, vacuum interaction, and storage queueing all constrain progress. More connections expose more work to these shared resources at once, but they do not partition those resources among requests.

Locks make the distinction especially visible. If many transactions update the same account, inventory row, or job record, one transaction owns the conflicting lock while others wait. Raising pool size admits more waiters, enlarges the active population, and can make the pool appear busy even though only one transaction is making useful progress on that key. Long-lived transactions worsen this by holding locks and snapshots longer. The resulting diagnosis is not "the pool is too small"; it is that concurrency exceeds the independence available in the workload.

Pool size must account for every pool

A per-process pool limit is not a database-wide limit. Eight application processes with a pool maximum of 25 can present 200 connections before migration jobs, administrative sessions, workers, reporting processes, and failover traffic are counted. Autoscaling changes that number dynamically, and rolling deployment can temporarily create old and new populations together. Connection limits sized from one process's configuration therefore fail precisely when the system is under transition or recovery pressure.

That leaves a capacity budget rather than a single preferred number. Reserve connections for operators and essential maintenance, identify all clients that can connect, and decide how much simultaneous database work the host can tolerate for the target workload. Divide the remaining allowance across independently scaled application instances, then use a lower operating pool limit where practical. The result need not maximize open sessions. Its purpose is to ensure that a traffic burst has a controlled queue before it becomes uncontrolled backend contention.

Transaction shape determines the useful limit

There is no universal pool-size formula because the useful concurrency changes with query mix and transaction shape. A workload dominated by short indexed reads may benefit from more concurrent requests than one dominated by parallel aggregates, wide sorts, or write contention. A service that holds a transaction only around two SQL statements has a different profile from one that opens a transaction before validating input, calling another service, or serializing a large response. Those patterns alter both mean hold time and tail behavior.

Connection lifecycle semantics matter as much as the count. Returning a connection immediately after the transaction prevents application work from consuming database admission slots. Transaction pooling can further increase reuse for clients that do not require session affinity, but session features complicate that choice: temporary objects, session variables, prepared-statement behavior, advisory locks, cursor lifetime, and transaction-scoped semantics may require a pinned session. The pool cannot safely multiplex state that the application has chosen to keep session-local.

Measure queues before changing limits

The first measurement is pool checkout latency and timeout rate, separated from database execution time. A rising checkout wait with stable transaction duration indicates that the pool is intentionally limiting a demand level the database may still handle; additional capacity can be tested cautiously. Rising checkout waits together with rising execution time indicates that work inside Postgres is slowing. Enlarging the pool in that condition commonly accelerates the feedback loop. Request rate, connection hold time, transaction latency, and active-versus-idle sessions establish the basic picture.

Inside the database, wait states, lock relationships, active query shape, transaction age, cache behavior, I/O pressure, CPU runnable load, and memory spill indicators identify the constraining resource. The target is not a zero-length pool queue at every instant. A small amount of queueing can be the cost of protecting a finite executor from bursts. The target is bounded latency and stable service time at a concurrency level that permits the database to complete work predictably.

Backpressure is part of the application contract

Once a pool is treated as an admission boundary, timeout and retry behavior become architectural choices. A request that cannot acquire a connection before its deadline should usually fail or degrade without retaining upstream resources indefinitely. Blind retries are dangerous when the database is already slow: they increase arrivals exactly when the system needs fewer. Retries require bounded budgets, jitter, idempotency where writes are involved, and awareness that a client timeout does not necessarily mean the server stopped executing the transaction.

Workload separation can make this boundary more useful. An interactive request path and an unconstrained report should not necessarily compete in the same queue, because the report can occupy slots long enough to alter user-facing tails. Separate pools or roles can reserve admission capacity, while query and schema changes reduce the service time that created pressure in the first place. This is why the strongest pool tuning often ends outside the pool: shorter transactions, fewer round trips, better access paths, and reduced contention raise useful throughput more directly.

The unresolved choice is where to spend latency

Reasonable engineers disagree on aggressive pool caps because the correct answer depends on workload variance and failure goals. A tighter cap protects Postgres and makes overload explicit, but it can reject work during bursts that a somewhat larger active set could have absorbed. A looser cap can preserve short-term success when transactions are mostly independent and resources remain underutilized, but it has less margin when a lock hotspot, storage slowdown, or deployment fan-out changes the operating point. Neither policy is inherently correct.

External poolers, transaction pooling, per-service quotas, and database-side limits each choose a different place to enforce fairness and state isolation. They also change debugging and operational failure modes. The settled principle is narrower than a sizing formula: open connections are permission to contend, not throughput capacity. The contested work is selecting how much contention is acceptable, which callers receive scarce admission slots, and how quickly the system declines work when its actual bottleneck is reached.