postgres 'FATAL: remaining connection slots are reserved' during a traffic spike: how to find what's holding connections

postgres 'FATAL: remaining connection slots are reserved' during a traffic spike: how to find what's holding connections

TL;DR

Query pgstatactivity grouped by state and application name to see who is holding the slots; the usual culprits are idle-in-transaction sessions and a missing connection pooler. Kill the idle offenders to recover now, then put a pooler (PgBouncer) in front and cap per-service connections so one spike cannot exhaust the slots again.

The error

FATAL: remaining connection slots are reserved for non-replication superuser connections

Steps

1. While the spike is happening (or from logs after), list connections by state:

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

Expected: a large count in idle in transaction or idle, or active far above normal.

2. Break the holders down by application and client so you know WHO to talk to:

SELECT application_name, client_addr, state, count(*),
       max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE datname = '[your database]'
GROUP BY application_name, client_addr, state
ORDER BY count(*) DESC
LIMIT 20;

Expected: one application name or one host owning most of the slots.

3. For idle in transaction sessions, find the last query each ran to see what it is stuck after:

SELECT pid, now() - state_change AS idle_for, left(query, 120)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change
LIMIT 20;

Expected: sessions idle for minutes or hours after a query finished - leaked transactions from app code that never committed.

4. Recover now: terminate the worst idle-in-transaction offenders. The oldest first (ORDER BY state_change puts the longest-idle on top). Re-check slot availability after each round:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change
LIMIT 20;

Expected: new connections start succeeding; the FATAL lines stop.

5. Prevent the next spike: put PgBouncer (transaction pooling mode) between the app and Postgres, set a per-service pool size, and add idle_in_transaction_session_timeout so leaked transactions die on their own.

   Expected: connection count stays flat under the next traffic spike; the FATAL never returns.

Use this when

• Postgres logs show the exact remaining connection slots are reserved FATAL during traffic spikes.
• New connections are refused while the database itself seems healthy.
• You suspect a leak: connection count climbs and never comes back down.

Not for this skill when

• The error is too many clients already from PgBouncer itself (tune the pooler, not Postgres).
• Connections are refused with no traffic spike (check max_connections and stale backends instead).
• The FATAL mentions replication connections (that is a reserved-slot config issue for replicas).

Variant phrasings

FATAL: remaining connection slots are reserved for non-replication superuser connections

The full canonical form of this error.

postgres refusing connections during traffic spike

Usually this FATAL underneath; check the logs.

connection slots exhausted, who is holding them

The pgstatactivity grouping in step 2 answers exactly this.

Why it happens

Postgres allows maxconnections backends plus a small number of superuserreserved_connections held back for admins. Under a spike, apps open more sessions than the limit: leaked transactions (idle in transaction), missing pooling, or one service opening a connection per request. Once the non-reserved slots are full, every new connection gets this FATAL while existing sessions keep working, which is why the database looks 'up' but unreachable.

Edge cases

• Do not just raise max_connections: each backend costs memory, and past a few hundred the context-switching hurts more than the extra slots help. A pooler is the real fix.
• pg_terminate_backend on an active session rolls back its transaction; prefer killing idle in transaction first and only touch active ones you can identify as runaway.
• Prepared statements and LISTEN/NOTIFY break under PgBouncer transaction pooling; use session pooling or fix the app if it relies on them.
• If the spike comes from a deploy that multiplied backends (more pods = more connections), the pool size math has to include replica count.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_phCFf9gd9JrLPiCYhX9YvA