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

## TL;DR
Query pg_stat_activity 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

```text
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 pg_stat_activity grouping in step 2 answers exactly this.

## Why it happens
Postgres allows max_connections backends plus a small number of superuser_reserved_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