Why Postgres Runs Out of Connections
The first serious operational issue many new Postgres users hit is a wall of errors like this:
FATAL: remaining connection slots are reserved for
non-replication superuser connections
Postgres caps its connection count via the max_connections setting, which defaults to 100. Cloud providers tend to be stricter: Heroku and Google Cloud Platform often limit databases to between 20 and 500 connections depending on tier. It's tempting to just raise the limit, but that's not a real fix, because the practical ceiling on connections is far lower than what the configuration might allow.
The Hidden Cost of Concurrency
Memory is the most obvious constraint — Postgres uses a process-per-connection model, where the Postmaster forks a backend process for each client. These start around 5 MB but can grow much larger depending on the data they touch.
But the more subtle bottleneck is shared memory. The Postmaster and its backends communicate through structures that require global scans or exclusive locks:
typedef struct PROC_HDR
{
/* Array of PGPROC structures (not including dummies for prepared txns) */
PGPROC *allProcs;
/* Array of PGXACT structures (not including dummies for prepared txns) */
PGXACT *allPgXact;
...
}
extern PGDLLIMPORT PROC_HDR *ProcGlobal;
Adding a process to the proc array takes an exclusive lock:
void
ProcArrayAdd(PGPROC *proc)
{
ProcArrayStruct *arrayP = procArray;
int index;
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
...
}
And GetSnapshotData, called constantly during normal operations, loops through every other process in the system:
Snapshot
GetSnapshotData(Snapshot snapshot)
{
ProcArrayStruct *arrayP = procArray;
...
/*
* Spin over procArray checking xid, xmin, and subxids. The goal is
* to gather all active xids, find the lowest xmin, and try to record
* subxids.
*/
numProcs = arrayP->numProcs;
for (index = 0; index < numProcs; index++)
{
...
}
}
These global bottlenecks mean per-backend performance degrades as the total number of active backends grows. A benchmark that runs parallel transactions — each inserting, selecting, and deleting ten rows — shows performance falling steadily as parallelism ramps from 1 to 1000:
So when a provider caps connections at 500 even on large instances, it's not arbitrary. Past that point, the answer isn't a bigger limit — it's using fewer connections more efficiently.
Connection Pools
A connection pool caches database connections locally within a process. Instead of paying the overhead to open a new connection for every request, workers check a connection out, use it, and return it. This is built into many adapters, including Go's database/sql, Java's JDBC, and Ruby's Active Record.
Pools also make connection usage predictable: configured with a max size, a single node's total connection count is deterministic. Workers that only acquire a connection during request handling can share a small pool across many concurrent tasks.
The weakness is that pools work best in single-process deployments. Ruby can't use real parallelism, so Rails apps often run under forking servers like Unicorn or Puma — and each forked process needs its own pool, which undermines efficiency.
Minimum Viable Checkouts
Most units of work have a critical span — the middle — where database access actually happens. An HTTP request, for instance, starts with reading and validating the payload, then runs core logic, then serializes the response and logs. A worker should hold a connection only during that core logic, not during the peripheral phases.
This minimum viable checkout approach lets a pool of connections serve a much larger pool of workers: idle workers hold no connections at all, and each connection is busy only for the shortest necessary window.
Release Connections During Foreign Calls
The same idea applies beyond request boundaries. If application work involves calling a foreign API, release the database connection while that slow network I/O is in flight. An application shouldn't be inside a transaction while mutating foreign state anyway. Check the connection back in, make the call, then reacquire it afterward.
PgBouncer for Cross-Node Pooling
Node-local pools reach their limit once an application scales horizontally. If you have N nodes each with M max connections, total capacity is N × M. To handle load imbalance — where one node might need more connections than another momentarily — you'd want that product to exceed the database's max_connections. But since nodes don't coordinate, a busy node can still hit the limit and get a connection error.
PgBouncer solves this by proxying connections to Postgres, acting as a global pool. It has three modes:
- Session pooling: a connection is assigned when a client opens one and held until the client closes it.
- Transaction pooling: connections are assigned only for the duration of a transaction and shared in between. This forbids connection-global features like
SET,LISTEN/NOTIFY, and prepared statements. - Statement pooling: connections are assigned per individual statement — which only works if the application abandons transactions entirely.
Transaction pooling is the best fit for applications already using a node-local pool correctly. It lets a cluster with N × M greater than max_connections approach near-maximum connection utilization while avoiding over-limit errors — though requests may still queue while waiting for a free connection.
A more common use of PgBouncer is as a node-local pool for applications that can't implement their own well — like Rails on Unicorn. Heroku's standard buildpack deploys a per-dyno PgBouncer for exactly this purpose. It works, but it's better to use a more sophisticated approach when the application architecture allows it.
Connection Budgets and Deploy Surges
Modern frameworks have spent years trying to abstract away the messy details of database connection handling. That simplification can carry an application for a while, but in practice, anyone running a serious Postgres deployment will eventually need to understand the mechanics underneath. Getting this right early, and architecting accordingly, is almost always cheaper than debugging a connection-starved production cluster later.
Every node in a cluster has a finite connection ceiling, defined by max_connections. The cluster-wide budget is that setting multiplied by the number of nodes. A common failure point is the deploy itself: a graceful rollout will often spin up fresh workers or nodes before the old ones have fully terminated, doubling the transient demand on the database. Plan for that temporary spike, not just steady-state traffic, when calculating headroom.
While the discussion here is Postgres-centric, the underlying constraint is universal. Any database will impose practical bottlenecks on concurrent sessions, and the same management strategies — pooling, scoping, and careful release — carry over cleanly to other engines.
The Real Cost of a Connection
It is tempting to read Postgres' default max_connections of 100 as an invitation to open 100 sessions per service. The limit is there for memory protection, not as a performance target. Each backend process reserves a slice of RAM, and while the per-connection overhead is small in isolation, it multiplies fast. Enabling huge pages can shrink the memory footprint substantially, because long-lived child processes tend to copy most of their parent’s page table into their own address space via copy-on-write. Huge pages are roughly 500 times larger than the standard 4 kB pages, so the page tables stay far leaner — often reducing overhead from tens of megabytes or more down to the order of a single megabyte.
The more insidious cost is concurrency itself. Beyond a modest threshold, adding connections does not add throughput; it adds contention. Locks, buffer pins, and I/O queues all lengthen as more sessions fight for the same resources. A simple benchmark that empties a table after each transaction shows measurable degradation as the session count climbs. The test does not isolate whether the bottleneck is Postgres-level locking or raw I/O, but the conclusion holds: performance decay under load is real, and it sets in well before you hit the hard connection ceiling.
Pool Sizing and the Sweet Spot
The pool is not a place to hoard connections; it is a buffer to smooth out demand. Too small, and requests queue at the application layer. Too large, and you recreate the same contention you were trying to avoid. The right size depends on your workload’s concurrency and the database’s total capacity across all services sharing the cluster.
When sizing, consider the entire fleet. If three applications each run a pool of 20, and you have three nodes, you have already consumed 180 of your budget. Leave room for maintenance windows, batch jobs, and the occasional runaway process, or you will trade a clean pool for a blocked deploy.
Minimum Viable Checkouts
Shorten the Hold Time
The most effective way to reduce pool pressure is to not hold a connection while you are not using it. Every database call should be wrapped in the tightest possible scope: check out a connection, run the query, and return it immediately. This is particularly important around slow operations that do not involve the database at all, such as an outbound HTTP request to a third-party API. Holding a connection open while waiting on a remote service to respond ties up a scarce resource for a gratuitous amount of time.
A common pattern that violates this rule is a transaction that performs a foreign mutation — a webhook call, a file upload, or similar — in the middle of its work. The fix is to restructure: commit the local transaction first, open the connection only for the database work, and then fire the external request without holding any database session. If the external call fails and requires compensation, handle that with a fresh connection and a compensating transaction, rather than blocking a pooled session indefinitely.
Process Models Matter
Threaded deployments in Ruby are possible, but the global interpreter lock (GIL) makes them fundamentally slower than a forking model for CPU-bound work. Each forked worker process is free to run concurrently, but it also needs its own connection. A threaded worker, by contrast, can share a smaller pool — just be sure the driver and pool library actually release the connection back to the pool between statements, or you will surprise yourself with how quickly threads consume the available slots.
PgBouncer and Inter-Node Pooling
When demand outgrows what a single pool per application can handle, PgBouncer provides a dedicated pooling layer in front of Postgres. It supports two primary modes. Session pooling assigns a server connection to a client for the duration of the client’s session — effectively a pass-through that adds no concurrency benefit. Transaction pooling is the useful one: PgBouncer hands a server connection to a client only for the length of a single transaction, then returns it to the shared set for the next client.
Transaction pooling lets you multiplex many application sessions over far fewer server connections, but it places constraints on application behavior. Prepared statements are the clearest example. Named prepared statements persist beyond a transaction boundary, so they are incompatible with transaction pooling unless the driver sticks strictly to unnamed prepared statements. The protocol supports both, so a driver that disciplines itself to use only unnamed statements can work fine. Anything that relies on session-level state, such as session-scoped settings or temporary tables, will also break when the pool swaps the underlying connection beneath a session.
For a multi-node application, the architecture becomes a question of where to put the pool. A single PgBouncer in front of the whole cluster centrally caps the number of server connections consumed, but it creates a single point of failure and a potential bottleneck. A PgBouncer per application node keeps failover domains smaller and isolates load, at the cost of more moving parts to monitor. The trade-off is the same as with any distributed system: centralize for control, decouple for resilience. Pick the topology that matches your tolerance for downtime versus your need for absolute connection caps.
