🚨 PostgreSQL Error Guide: 'remaining connection slots are reserved for non-replication superuser connections'

Overview

PostgreSQL allows at most max_connections backends at once, but it sets aside a handful of those slots so that a superuser (and, on PG16+, designated reserved roles) can always log in to fix problems — even when the database is otherwise full. When a normal application role tries to connect and only those reserved slots remain, the connection is refused with a FATAL error instead of taking the last seats.

You will see this on the client and in the server log:

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

LOG:  connection received: host=10.0.4.22 port=51112
FATAL:  remaining connection slots are reserved for non-replication superuser connections

It occurs when active backends reach max_connections - superuser_reserved_connections (minus reserved_connections on PG16+). The root cause is almost never “too many real users” — it is usually leaked connections, sessions stuck idle in transaction, monitoring or cron jobs that never close their handles, or simply running without a connection pooler so every client process opens its own backend.

Symptoms

• Application connections suddenly fail en masse with the “reserved for … superuser” FATAL, often under load or after a deploy.
• A superuser can still connect via psql (because the reserved slots are intact) — useful for triage.
• pg_stat_activity count sits right at the non-reserved ceiling.
• Many sessions show state = 'idle' or idle in transaction rather than active work.

SELECT count(*) AS total,
       count(*) FILTER (WHERE state = 'active')              AS active,
       count(*) FILTER (WHERE state = 'idle')                AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;

 total | active | idle | idle_in_txn
-------+--------+------+-------------
   197 |      6  |  171 |          20

SELECT setting::int AS max_connections,
       (SELECT setting::int FROM pg_settings WHERE name = 'superuser_reserved_connections') AS superuser_reserved
FROM pg_settings WHERE name = 'max_connections';

 max_connections | superuser_reserved
-----------------+--------------------
             200 |                  3

197 of 200 used with only 3 reserved slots left — the next non-superuser login fails.

Common Root Causes

1. max_connections nearly exhausted, hitting the reserved floor

The simplest case: real demand exceeds capacity and backends climb to the non-reserved ceiling.

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

 in_use | max_conn | su_reserved
--------+----------+-------------
    197 |      200 |           3

in_use (197) has reached max_conn - su_reserved (197). Either raise max_connections (costs memory per slot) or, far better, put a pooler in front — see cause 3.

2. Connection leaks from the application

An app that opens connections but fails to return them to its pool (or doesn’t pool at all) accumulates idle backends until the cluster fills.

SELECT usename, application_name, client_addr,
       count(*) AS conns,
       count(*) FILTER (WHERE state = 'idle') AS idle
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1,2,3
ORDER BY conns DESC
LIMIT 5;

  usename  | application_name | client_addr | conns | idle
-----------+------------------+-------------+-------+------
 appuser   | orders-api       | 10.0.4.22   |   142 |  140
 appuser   | billing-worker   | 10.0.4.31   |    28 |   26

142 connections from a single service with 140 idle is a leak — likely a pool sized too large or handles never released. Fix the app’s pool config, not the database.

3. No connection pooler in front of PostgreSQL

Each application process holding its own backend doesn’t scale: 50 app pods × 20 connections each is 1000 backends. A pooler (PgBouncer/pgcat) multiplexes many clients onto few backends.

SELECT client_addr, count(*) AS conns
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY client_addr
ORDER BY conns DESC
LIMIT 10;

  client_addr | conns
--------------+-------
 10.0.4.22    |    20
 10.0.4.23    |    20
 10.0.4.24    |    20
 10.0.4.25    |    20
 ...          |   ...

Many hosts each holding ~20 direct connections means total demand scales with pod count. Putting PgBouncer in transaction-pooling mode collapses this to a small, fixed backend count.

4. Sessions stuck idle in transaction

A session that runs BEGIN, does some work, then never commits or rolls back holds its slot (and any locks) indefinitely. Enough of these and the cluster fills.

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

  pid  | usename | application_name |   idle_for   |           query
-------+---------+------------------+--------------+--------------------------
 60112 | appuser | orders-api       | 00:41:18.221 | UPDATE orders SET ...
 60140 | appuser | orders-api       | 00:39:02.114 | INSERT INTO line_items...

Sessions idle in a transaction for 40+ minutes are holding slots hostage. Set idle_in_transaction_session_timeout so PostgreSQL reclaims them automatically.

5. Monitoring, cron, or migration jobs piling up connections

Scheduled jobs that overlap (a slow query still running when the next tick fires) or a monitoring agent that opens a fresh connection per metric can quietly consume dozens of slots.

SELECT application_name, count(*) AS conns,
       max(now() - backend_start) AS oldest
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name
ORDER BY conns DESC
LIMIT 6;

 application_name  | conns |     oldest
-------------------+-------+----------------
 orders-api        |   142 | 00:58:11.20114
 metrics-exporter  |    34 | 01:22:44.88210
 pg_cron           |     9 | 00:14:02.11233

34 long-lived metrics-exporter connections means the agent isn’t reusing a single connection. Reconfigure it to use one persistent connection or a small pool.

6. reserved_connections (PG16+) tightening the non-superuser ceiling

PostgreSQL 16 added reserved_connections, which reserves slots for roles with the pg_use_reserved_connections privilege — on top of superuser_reserved_connections. If set, the effective ceiling for ordinary roles is lower than you expect.

SELECT name, setting FROM pg_settings
WHERE name IN ('max_connections', 'superuser_reserved_connections', 'reserved_connections');

              name               | setting
---------------------------------+---------
 max_connections                 | 200
 reserved_connections            | 10
 superuser_reserved_connections  | 3

Here ordinary roles can use only 200 - 10 - 3 = 187 slots. If you sized capacity assuming 197, you’ll hit the FATAL 10 connections sooner than expected.

Diagnostic Workflow

Step 1: Measure usage against the real ceiling

SELECT (SELECT count(*) FROM pg_stat_activity) AS in_use,
       current_setting('max_connections')::int AS max_conn,
       current_setting('superuser_reserved_connections')::int AS su_reserved,
       current_setting('reserved_connections', true) AS reserved;  -- PG16+

If in_use equals max_conn - su_reserved (- reserved), you’re at the limit. Connect as a superuser to run the rest — the reserved slots keep that path open.

Step 2: Break down connections by state

SELECT state, count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state ORDER BY count(*) DESC;

A large idle count points to a leak/over-sized pool; a large idle in transaction count points to sessions not committing.

Step 3: Attribute connections to a client, user, or app

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

This usually names the culprit immediately — one service or host holding a disproportionate share.

Step 4: Reclaim slots safely

-- Terminate long idle-in-transaction sessions (verify before running)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '15 minutes';

This frees slots immediately so the application recovers; then fix the underlying leak so they don’t refill.

Step 5: Set guardrails so it can’t refill silently

ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
ALTER SYSTEM SET idle_session_timeout = '10min';   -- PG14+, reaps stray idle clients
SELECT pg_reload_conf();

These let PostgreSQL reclaim stuck and abandoned sessions on its own. Pair them with a pooler so total backends stay bounded regardless of client count.

Example Root Cause Analysis

After a Kubernetes deploy scaled the orders-api from 8 to 20 pods, application logs fill with “remaining connection slots are reserved for non-replication superuser connections”. A superuser can still log in, so we triage from there.

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

 in_use | max_conn | su_reserved
--------+----------+-------------
    197 |      200 |           3

Full. Breaking it down by client:

SELECT application_name, count(*) AS conns,
       count(*) FILTER (WHERE state = 'idle') AS idle
FROM pg_stat_activity WHERE backend_type = 'client backend'
GROUP BY application_name ORDER BY conns DESC LIMIT 3;

 application_name | conns | idle
------------------+-------+------
 orders-api       |   180 |  176
 metrics-exporter |    11 |   10

The 20 orders-api pods each open a connection pool of 9, so 180 backends — almost all idle — but the app never pooled across pods, and there’s no PgBouncer. Scaling pods multiplied direct connections until the cluster filled.

Fix: reclaim the idle slots now, then put a pooler in front so pod count no longer drives backend count:

-- immediate relief
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'orders-api' AND state = 'idle'
  AND now() - state_change > interval '5 minutes';

-- 150+ rows returned; slots freed, app reconnects through the pooler

With PgBouncer in transaction mode capping server-side connections at, say, 40, the 20 pods share a bounded backend pool and the FATAL stops recurring. The durable changes are the pooler plus idle_session_timeout to reap any strays.

Prevention Best Practices

• Run a connection pooler (PgBouncer/pgcat) in transaction mode so total backends stay bounded no matter how many app instances scale up. This is the single biggest fix for slot exhaustion.
• Size application pools deliberately: total pool size across all instances must stay below max_connections - superuser_reserved_connections (- reserved_connections).
• Set idle_in_transaction_session_timeout and idle_session_timeout so PostgreSQL reclaims stuck and abandoned sessions automatically.
• Give monitoring, cron, and exporter jobs a single persistent connection or a tiny dedicated pool — never one connection per metric or per tick.
• Alert on connection utilization (pg_stat_activity count vs. max_connections) at ~80%, so you see the climb before the FATAL.
• Account for PG16+ reserved_connections when computing the real non-superuser ceiling.
• When connections spike unexpectedly, the free incident assistant can turn a pg_stat_activity breakdown into the likely leaking service and a remediation step.

Quick Command Reference

-- Usage vs. the real ceiling
SELECT (SELECT count(*) FROM pg_stat_activity) AS in_use,
       current_setting('max_connections')::int AS max_conn,
       current_setting('superuser_reserved_connections')::int AS su_reserved,
       current_setting('reserved_connections', true) AS reserved;  -- PG16+

-- Breakdown by state
SELECT state, count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend' GROUP BY state ORDER BY count(*) DESC;

-- Attribute connections to user / app / host
SELECT usename, application_name, client_addr, count(*) AS conns
FROM pg_stat_activity WHERE backend_type = 'client backend'
GROUP BY 1,2,3 ORDER BY conns DESC LIMIT 10;

-- Find long idle-in-transaction sessions
SELECT pid, application_name, now() - state_change AS idle_for, query
FROM pg_stat_activity WHERE state = 'idle in transaction'
ORDER BY state_change LIMIT 10;

-- Reclaim slots (verify first!)
-- SELECT pg_terminate_backend(pid) FROM pg_stat_activity
--   WHERE state = 'idle in transaction' AND now() - state_change > interval '15 minutes';

-- Set guardrails
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
ALTER SYSTEM SET idle_session_timeout = '10min';  -- PG14+
SELECT pg_reload_conf();

Conclusion

This FATAL means active backends reached max_connections minus the reserved slots, so only a superuser can still get in. The usual root causes:

1. max_connections genuinely exhausted, hitting the reserved floor.
2. Application connection leaks or over-sized client pools.
3. No connection pooler, so backend count scales with every app instance.
4. Sessions stuck idle in transaction holding slots (and locks).
5. Monitoring, cron, or migration jobs piling up long-lived connections.
6. PG16+ reserved_connections lowering the non-superuser ceiling below expectations.

Reclaim slots from idle/stuck sessions for immediate relief, then fix the cause — almost always a pooler plus idle timeouts so backend count stays bounded. For more PostgreSQL troubleshooting, see the Postgres guides.