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

This error means PostgreSQL has reached max_connections and the only slots left are the handful reserved for superusers. Every new application connection is refused, which usually presents as a full outage: web requests fail immediately because they cannot get a database connection at all. The database itself is typically healthy. Something is consuming connections faster than it releases them, and the usual culprits are a connection leak, an oversized application pool multiplied across many instances, or no pooler at all.

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

What this error means

PostgreSQL allows max_connections concurrent sessions (100 by default) and holds back superuser_reserved_connections of them (3 by default) so an administrator can always get in. When ordinary connections have consumed everything else, non superuser logins fail with this exact message. Each connection is a dedicated backend process, so the ceiling exists to protect memory and scheduler capacity. Hitting it means the demand for simultaneous open connections exceeded the configured supply, whether through genuine load, leaked sessions that never close, or pools that hold far more connections than the server was sized for.

Common causes

• A connection leak: application code opens connections and never returns them, so idle sessions accumulate until the ceiling is hit.
• No connection pooler, with each web worker or Lambda invocation opening its own direct connection.
• Application pool sizes that look fine per instance but multiply across autoscaled instances (20 per pod times 15 pods is 300 connections).
• Long transactions or idle in transaction sessions holding slots while doing nothing.
• Cron jobs, migrations, or analysts opening extra sessions on top of steady state usage.
• max_connections left at the default while the application fleet grew around it.

How to fix it

STABILIZE first. Connect through a reserved superuser slot and free connections so the application can get in again, then fix the source.

1. DIAGNOSE: connect as a superuser (this works while ordinary logins fail) and see who is using the slots.

psql -U postgres -h 127.0.0.1 -d postgres

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

2. DIAGNOSE: look at the oldest idle sessions, which are the leak suspects.

SELECT pid, usename, application_name, state,
       now() - state_change AS idle_for,
       left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction')
ORDER BY state_change
LIMIT 20;

3. STABILIZE: terminate sessions that are clearly abandoned, starting with long idle ones. This restores login capacity immediately.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < now() - interval '30 minutes'
  AND usename = 'app';

4. FIX the leak. In the application, ensure every connection is returned to the pool in a finally block or context manager, and set pool timeouts so nothing is held forever. Match the sum of all instance pools to what the server can actually accept.

5. FIX the architecture: put PgBouncer in transaction pooling mode between the application and PostgreSQL, so thousands of client connections share a small backend pool.

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
pool_mode = transaction
default_pool_size = 20
max_client_conn = 2000

6. FIX the guardrails so idle and leaked sessions get cleaned up by the server itself:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
ALTER SYSTEM SET idle_session_timeout = '30min';
SELECT pg_reload_conf();

Raise max_connections only after pooling is in place, and remember it requires a restart.

How to prevent it

• Run PgBouncer (or your platform’s pooler) in front of PostgreSQL for any application with more than a handful of workers.
• Budget connections explicitly: sum every instance’s pool size plus jobs and admin sessions, and keep it comfortably below max_connections.
• Alert on connection count at around 80 percent of max_connections so you act before logins fail.
• Set idle_in_transaction_session_timeout so stuck transactions cannot hold slots indefinitely.
• Give humans and cron jobs their own roles with connection limit set, so they cannot starve the application.
• Never run the application as a superuser, so the reserved slots stay available for emergencies like this one.

When to call a senior engineer

Call for help when connection exhaustion keeps recurring after you added a pooler, when you cannot find which service is leaking, or when the connection budget genuinely exceeds what one server can hold and you need to weigh read replicas, pooling layers, or sharding. Erzon engineers can trace every connection back to its source, right size the pools across your fleet, and leave you with monitoring that flags the next leak before it becomes an outage.