Connection exhaustion and the storm that follows · PostgreSQL

Connection exhaustion looks like a capacity problem and is usually a consequence of something else. Understanding the loop matters because the intuitive response — allow more connections — feeds it.

The loop

flowchart TD
    A["Something slows down\nlock, storage, a bad plan"] --> B["Each request holds its\nconnection for longer"]
    B --> C["Pool saturates;\nnew requests wait"]
    C --> D["Application timeouts fire"]
    D --> E["Clients retry and reconnect"]
    E --> F["More backends: more memory,\nmore contention, more forks"]
    F --> A

Every arrow is an application or a database behaving as designed. The loop is emergent, and it converges on an outage rather than away from one, because each turn adds load to a system that was already struggling.

The critical property is that the first arrow is not connections. Something else slowed down. Treating the visible saturation as the cause leads to a remediation aimed at the fifth box.

Telling the three cases apart

Exhaustion has three common causes with different evidence and opposite remediations.

Cause	`state` breakdown	`xact_age`	What to do
Leak	Many `idle` or `idle in transaction`, few `active`	Some very old	Find the code path; bound with timeouts
Slowness	Many `active`, waiting	Uniformly rising	Find what they wait on; reduce arriving work
Genuine demand	Many `active`, progressing	Short and stable	Pool, or add capacity

One query separates them:

psql -U postgres -c \
  "SELECT state,
          count(*),
          max(now() - xact_start)   AS oldest_xact,
          max(now() - state_change) AS longest_in_state
     FROM pg_stat_activity
    WHERE backend_type = 'client backend'
    GROUP BY state ORDER BY count(*) DESC"

A cluster with 190 idle and 4 active is leaking or over-pooled. A cluster with 190 active, all waiting on the same wait_event, has a bottleneck the connections are queued behind. The second case is where adding connections does the most harm.

Getting back in

If you set reserved_connections and granted pg_use_reserved_connections to an operator role, as the earlier lesson recommended, you connect normally. If not, the options narrow.

Read-only / Safethe two refusals, and what each leaves you— Both captured from live 18.6 clusters.

$ psql -h 127.0.0.1 -U app -d postgres -c 'SELECT 1'

-- reserved slots remain; a privileged role can still connect
FATAL:  remaining connection slots are reserved for roles with privileges
      of the "pg_use_reserved_connections" role

-- nothing remains at all, for anyone
FATAL:  sorry, too many clients already

The first message is the better one: capacity exists that your role is not entitled to. Connect as a superuser, or as a role holding pg_use_reserved_connections.

The second means every slot including the reservations is consumed. There is no privileged route in, and the remaining options are to wait for a disconnection — which a storm will immediately consume — or to reduce the arriving load from outside the database.

The most effective action is usually outside the database

When a storm is running, the database cannot win the race: every slot freed is taken by the next retry. Reducing what arrives is what breaks the loop, and the lever is almost always upstream.

Scale the application down, or take a subset of replicas out of the load balancer. Fewer replicas is fewer pools. This is counter-intuitive during an incident and it is frequently the fastest route to a responsive database.

Pause the batch job, the queue consumer or the cron task if one is implicated. These often have far larger pools than the request path and tolerate being stopped.

If a pooler is already in front, lower its pool size toward the database rather than raising it. A pooler’s job during a storm is to queue work outside the database, and a smaller pool queues more.

Then, with the database responsive again, diagnose the first arrow.

Terminating sessions, carefully

-- Ask politely: cancel the running query, leave the session connected
SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid = 41822;

-- End the session entirely
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 41822;

pg_cancel_backend ends the current statement and the session survives, its transaction rolled back to the point of the cancelled statement. It does nothing to an idle session, because there is no statement to cancel.

pg_terminate_backend ends the session. Its transaction is rolled back, the connection drops, and the application learns about it at its next attempt to use that connection.

Bulk-terminating idle sessions to free slots during a storm

The query is easy to write and it is the wrong first action twice over.

It feeds the loop. Every terminated connection is a connection the application immediately reopens, so you have spent effort producing the reconnection you were trying to reduce, and the freed slots are taken by retries rather than by useful work.

And it destroys the evidence. application_name, client_addr, transaction age and last query are how you identify which code path is responsible, and they vanish with the session. The incident then recurs, because nothing was learned.

Capture first, always:

CREATE TEMP TABLE incident_sessions AS
SELECT now() AS captured_at, pid, usename, application_name, client_addr,
       state, backend_start, xact_start, state_change,
       backend_xid, backend_xmin, wait_event_type, wait_event, query
  FROM pg_stat_activity WHERE backend_type = 'client backend';

One statement, and the investigation survives whatever you do next.

There is a narrow case where terminating is correct: a single identified session holding a lock that blocks everything else. That is targeted, evidence-led and reversible in its effects. Bulk termination by state is none of those things.

Why a storm is self-reinforcing rather than self-limiting

A well-behaved queue sheds load when saturated: arrivals are refused cheaply and the system stabilises at capacity. A connection storm does the opposite, for three compounding reasons.

Refusal is not cheap. The connection limit is checked after authentication, so a rejected connection has already cost the server a fork, the authentication exchange, and the backend initialisation up to that point. At high arrival rates the work spent rejecting connections is itself a significant load, and it competes with the queries that would have relieved the pressure.

Retry rates rise as latency rises. Most clients retry on timeout, so a slower database produces more timeouts and therefore more connection attempts per unit of work. The arrival rate is a function of how badly the system is performing, which is the sign that makes the loop positive rather than negative.

Each admitted connection makes the next one slower. A new backend adds memory pressure and contention, so admitting connections during saturation reduces the throughput available to drain the queue.

The consequence is that the system has two stable states — healthy, and collapsed — and no stable state in between. Once it crosses into the storm it does not drift back, which is why the remedy has to be a step change in arriving load rather than a small adjustment.

This is also the argument for a pooler with a bounded pool and a queue. The queue is the load-shedding mechanism the database itself does not have: work waits outside the database rather than arriving as connections, and the database sees a stable concurrency regardless of how the application is behaving.

Production discipline

Read the state breakdown before acting. Idle-heavy and active-heavy exhaustion have opposite remediations.
Capture pg_stat_activity to a table before terminating anything. The identifying columns are the only route to prevention.
Reduce arriving load from outside the database first. Scaling the application down during a storm is counter-intuitive and usually the fastest route to a responsive database.
Lower a pooler’s database-side pool during a storm, not raise it. Queuing outside the database is the point.
Prefer pg_cancel_backend to pg_terminate_backend where a statement is the problem, and target a specific identified session rather than a state.
Set reserved_connections before you need it. Once the second refusal message appears there is no privileged route in.

Cross-course references

Observability for Production Sysadmins — Part XX (Alert quality) covers alerting on connection headroom and rising transaction age rather than on the exhaustion itself, which arrives too late to act on.
Kubernetes for Production Sysadmins — Part LXXXII (HPA) covers autoscaling that reacts to latency, which during a database storm scales the application up and makes the loop worse.
Linux for Production Sysadmins — Part LXXXI (Incident) covers evidence capture before remediation, which is the discipline this lesson applies to sessions.