high riskservice affecting~30 min

Runbook: Respond to Connection Exhaustion

1 · Prerequisites

Confirm every item is in place before any state change.

The refusal message the application received, verbatim, because it names which tier of connection slots is exhausted
Shell access to the database host, since the last tier of exhaustion locks out the local socket as well
Knowledge of the pool configuration: pods times pool maximum, and whether the pool minimum equals its maximum
Authority to terminate sessions, and to ask the application tier to scale down or restart
The history of previous max_connections changes, because this incident tends to recur with a larger number each time

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

· Read the refusal message. PostgreSQL 18 produces three different ones and each names the tier that is gone. That is free diagnosis at the moment you have least time for it.
· Determine whether you can connect at all. If every tier is exhausted, even a superuser over the Unix socket is refused with FATAL: sorry, too many clients already. A slot limit is a slot limit and there is no privileged transport that bypasses it.
· Check whether the cluster is otherwise healthy. Existing connections continuing to work, with low CPU and idle disks, is the signature of connection exhaustion rather than of overload.
· Check whether this is exhaustion or a storm. Exhaustion is a static shortfall — the pools ask for more than the cluster offers. A storm is a feedback loop in which latency causes the pool to open more connections. pg_stat_activity state distribution distinguishes them: mostly idle is exhaustion, mostly active with LWLock waits is a storm.
· Check for a recent rolling restart. New pods opening their full allocation before old pods drain briefly doubles demand, and it is the most common trigger.
· **Look up the previous max_connections changes.** If it has been raised before in response to this symptom, the cause was never addressed and raising it again will defer it further.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1Free one slot without a connection, if you cannot get one. ps -o pid,args -C postgres on the host, then kill -TERM against a single backend process. SIGTERM is exactly what pg_terminate_backend() sends and it is safe.
2**Never kill -9 a backend.** SIGKILL causes the postmaster to treat it as a crash, terminate every other session, and enter cluster-wide crash recovery — trading a connection shortage for a full restart.
3Do not restart the cluster to obtain a connection. That is a full outage in exchange for a diagnosis you can get without one.
4Measure before acting. SELECT state, count(*), count(*) FILTER (WHERE state_change < now() - interval '10 minutes') AS idle_over_10m FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY state ORDER BY 2 DESC;
5Identify who holds the slots. SELECT usename, application_name, client_addr, count(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT 20; This names the pods, which is what you need to fix it at the right layer.
6Shed long-idle sessions, not busy ones. SELECT pg_terminate_backend(pid), usename, client_addr, now() - state_change AS idle_for FROM pg_stat_activity WHERE backend_type = 'client backend' AND state = 'idle' AND state_change < now() - interval '15 minutes';
7Understand that this buys minutes. The pools will reopen the connections. Use the time to reduce demand at the application rather than to repeat the shedding.
8Reduce demand at the application. Lower the pool maximum so that pods times pool maximum sits comfortably under max_connections, and set the pool minimum low so pods do not claim their full allocation at start-up.
9If the application tier can be restarted or scaled down, that is legitimate and fast. It is often the only mechanism that reliably reduces connection count.
10**Do not raise max_connections as the response.** Each connection is an operating system process with its own memory, work_mem is per operation rather than per server, and raising the limit removes the backpressure that makes the pool misconfiguration visible.
11Configure the reserve tiers so the next incident is diagnosable. GRANT pg_use_reserved_connections TO ops; and set reserved_connections to a small number. An operator who can still connect during exhaustion can diagnose it.
12**Set idle_session_timeout for application roles**, so a pool that never releases cannot accumulate indefinitely: ALTER ROLE app SET idle_session_timeout = '10min';

4 · Verification

Confirm the procedure actually fixed the problem.

✓New connections succeed, from the application and from an operator.
✓Backend count sits well below max_connections, with headroom that survives a rolling restart — tested by performing one and watching the count.
✓The count of sessions idle for more than ten minutes is small: SELECT count(*) FROM pg_stat_activity WHERE state = 'idle' AND state_change < now() - interval '10 minutes';
✓The server log contains no further refusal messages of any of the three kinds.
✓An operator connection succeeds during peak load, using a role that holds pg_use_reserved_connections. Test this once deliberately rather than discovering during the next incident that the reserve was never configured.
✓pods x pool_max is a number somebody has written down and it is under max_connections.
✓max_connections was not raised, or if it was, the reason is recorded and is not "the same symptom as last time".

5 · Rollback

If verification fails, undo the procedure in reverse order.

↶Terminated sessions cannot be restored; the pools reopen them, which is the intended outcome.
↶If idle_session_timeout was set aggressively during the incident, review it afterwards against what the application actually needs. An emergency value left in place produces mysterious disconnections later.
↶If sessions were terminated indiscriminately and an application's in-flight work was lost, record which and tell its owner. Terminating active sessions rolls back their transactions.
↶If max_connections was raised under pressure, plan the reduction deliberately — it requires a restart, and leaving it high defers the real fix indefinitely.
↶If pg_use_reserved_connections was granted broadly during the incident, narrow it afterwards. A reserve everybody holds is not a reserve.
↶If the application tier was scaled down to relieve pressure, restore its capacity once the pool configuration is corrected, and confirm the connection count stays within budget as it scales back up.

6 · Escalation

When the runbook isn't enough, contact:

· You cannot obtain a connection and cannot reach the host: escalate to whoever has host access. The recovery from full exhaustion requires either a SIGTERM from the host or an application-side reduction.
· The connection count is dominated by an application nobody can identify: escalate to the platform owner. An unattributed pool is an inventory problem, and terminating its sessions repeatedly is not a fix.
· The pool configuration cannot be changed quickly: escalate to the application owner with the arithmetic — pods, pool maximum, and max_connections — so the shortfall is a number rather than an opinion.
· The pattern is a feedback loop rather than a static shortfall: escalate as a connection storm. Latency rising, the pool adding connections, and latency rising further needs the pool capped, not more slots.
· Somebody proposes raising max_connections for the third time: escalate to the service owner with the history. Each previous raise deferred the real fix and increased the worst-case memory footprint.
· The cluster is saturated rather than merely full: escalate to whoever owns capacity. A cluster past the knee of its throughput curve makes every query slow, and no connection setting addresses that.

The refusal message tells you which tier of slots is gone. Read it first; it is the cheapest diagnosis available and it arrives before you have done anything.

Three messages, three tiers

max_connections is divided, and each division has its own refusal

Measured on PostgreSQL 18.6 with max_connections=15, superuser_reserved_connections=3, reserved_connections=2:

Slots	Who may use them	Message when this tier is exhausted
1–10	any role	`...reserved for roles with privileges of the "pg_use_reserved_connections" role`
11–12	holders of `pg_use_reserved_connections`	`...reserved for roles with the SUPERUSER attribute`
13–15	superusers only	`sorry, too many clients already`

The ordinary ceiling is `max_connections - superuser_reserved_connections

reserved_connections`. Here that is 10, and the eleventh ordinary connection is refused while five slots remain.

So the message names the tier. That is worth knowing before 02:00.

The last tier locks out the local socket too

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
failed: FATAL:  sorry, too many clients already

Measured, not assumed. A slot limit is a slot limit; there is no privileged transport that bypasses it.

Any runbook whose answer to this incident is “connect locally as postgres” is wrong.

Getting a connection when there are none

# as root or the postgres OS user on the database host
ps -o pid,args -C postgres | grep 'postgres: app '
# then SIGTERM exactly one of the pids that listed
kill -TERM 12345

SIGTERM is exactly what pg_terminate_backend() sends. Measured on 18.6 with every slot consumed: after one SIGTERM, a local-socket connection succeeded.

Exhaustion or storm?

They look similar and have opposite fixes.

	Exhaustion	Storm
Session states	Mostly `idle`	Mostly `active`
Wait events	Few	`LWLock`, `Lock`
CPU	Low	Saturated, user time
Cause	Pools ask for more than the cluster offers	Latency → pool grows → latency rises
Fix	Cap the pools	Cap the pools and stop them growing on latency

SELECT state, count(*),
       count(*) FILTER (WHERE state_change < now() - interval '10 minutes') AS idle_over_10m
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state ORDER BY 2 DESC;

SELECT usename, application_name, client_addr, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT 20;

The second query names the pods. That is what you need to fix this at the right layer.

Blast radius

Action	Reversible?	What it costs if wrong
`SIGTERM` to one backend	No	One session’s open transaction
Terminating long-idle sessions	No	Nothing; the pools reopen them
Terminating `active` sessions	No	In-flight work, and a retry storm on top
Scaling down the application tier	Yes	Capacity, deliberately
Raising `max_connections`	Needs a restart	The backpressure that would have found the real fault
`kill -9` on a backend	No	Cluster-wide crash recovery

The change that has already been made twice

Before you leave

Two settings that make the next occurrence diagnosable and smaller:

-- an operator who can still get in during exhaustion
GRANT pg_use_reserved_connections TO ops;

-- a pool that never releases cannot accumulate indefinitely
ALTER ROLE app SET idle_session_timeout = '10min';

Then test the first one during peak load, once, deliberately — rather than discovering during the next incident that the reserve was zero.