🚨 Postgres Error: 'remaining connection slots are reserved' — Cause, Fix, and Troubleshooting Guide

Overview

PostgreSQL refuses a new connection with this error when the number of open backends has reached max_connections minus superuser_reserved_connections. The last few slots are held back so a superuser can still log in to fix the problem; ordinary roles are turned away first.

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

This is the cousin of too many clients already: the cluster is at (or near) its connection ceiling. The difference is that this specific message means the only slots left are the reserved superuser ones, so a normal application role cannot connect even though a handful of physical slots technically remain.

Symptoms

• Application connections fail intermittently at peak load with the FATAL above.
• A superuser can still connect via psql while application roles cannot.
• Connection count sits at or just below max_connections.
• Health checks and new pods/workers fail to start while existing traffic keeps working.

SELECT count(*) AS total,
       current_setting('max_connections')::int AS max_conn,
       current_setting('superuser_reserved_connections')::int AS reserved
FROM pg_stat_activity;

 total | max_conn | reserved
-------+----------+----------
    97 |      100 |        3
(1 row)

97 backends against a 100 limit with 3 reserved means every non-superuser slot is gone.

Common Root Causes

1. Too many idle or idle-in-transaction connections

Most “out of slots” incidents are not real concurrency — they are connections sitting idle that were never returned to a pool.

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

        state        | count
---------------------+-------
 idle                |    71
 active              |    18
 idle in transaction |     8
(3 rows)

71 idle plus 8 idle-in-transaction backends are holding slots while doing no work.

2. No connection pooler in front of the database

Each application process opening its own direct connection (and multiplying by replicas) blows past max_connections quickly. Postgres backends are heavyweight; hundreds of direct clients is an anti-pattern.

3. max_connections set too low for the workload

A default of 100 is fine for one app but not for a fleet of serverless functions or many worker replicas.

SHOW max_connections;

4. Connection leaks in application code

Code paths that open a connection and never close it (missing finally/defer, exceptions before release) slowly consume slots until the cluster is full.

How to diagnose

Step 1: See who is holding the slots

SELECT usename, application_name, client_addr, state,
       count(*) AS conns
FROM pg_stat_activity
GROUP BY usename, application_name, client_addr, state
ORDER BY conns DESC
LIMIT 10;

A single application_name or client_addr dominating the list points straight at the leaking service.

Step 2: Find long-lived idle-in-transaction backends

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

Backends idle in a transaction for minutes are both hoarding a slot and holding locks.

Step 3: Confirm the headroom math

SELECT current_setting('max_connections')::int
     - current_setting('superuser_reserved_connections')::int AS usable_slots,
       (SELECT count(*) FROM pg_stat_activity WHERE usename IS NOT NULL) AS in_use;

When in_use reaches usable_slots, non-superuser logins start failing.

Fixes

Reclaim leaked slots immediately

Terminate idle-in-transaction backends older than a threshold to free slots right now:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes';

Put a pooler in front of Postgres

Route the application through PgBouncer in transaction mode so hundreds of client connections share a small pool of real backends. This is the durable fix for direct-connection sprawl.

Cap connections at the application/pool layer

Set the client pool max below the database’s usable slots (e.g., app pool of 20 per replica, sized so all replicas together stay under max_connections).

Raise max_connections only if the box can afford it

Each backend costs memory; bumping the limit without adding RAM (or lowering work_mem) risks out of memory. Change it and restart:

ALTER SYSTEM SET max_connections = 200;
-- requires a restart to take effect
SELECT pg_reload_conf();  -- reload does NOT apply max_connections; restart the server

Reap idle connections automatically

ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

What to watch out for

• Raising max_connections without more RAM trades this error for out of memory — pool instead of scaling backends.
• pg_reload_conf() does not apply a new max_connections; that setting needs a full restart.
• Terminating idle in transaction backends rolls back their open transaction — make sure the app retries cleanly.
• Set superuser_reserved_connections high enough (default 3) that you can always log in to triage; never drop it to 0.
• Monitor connection count as a first-class metric so you see the ceiling approaching before logins fail.

Related

• Postgres Error: ‘too many clients already’
• Postgres Error: ‘could not connect to server: Connection refused’
• Postgres Error: ‘out of shared memory’