Fix “remaining connection slots are reserved” in PostgreSQL

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

This error usually arrives in a burst. A deploy goes out, traffic rises, or a batch of serverless functions wakes up, and suddenly every new request fails at login with FATAL: remaining connection slots are reserved for non-replication superuser connections. Requests that already hold a connection keep working. Anything that needs a new one gets turned away.

Postgres is telling you it is nearly full. It still has a handful of connection slots, but it keeps those for superusers so an administrator can always get in to fix things. Your app’s role isn’t a superuser, so it gets refused. The fix is to find who is holding the other slots, release the ones nobody is using, and stop your clients from asking for more than the database has.

What “remaining connection slots are reserved” means

Every client connection to Postgres takes one slot out of a fixed pool sized by max_connections. The PostgreSQL docs say the default is typically 100, and the setting can only be changed at server start. A second setting, superuser_reserved_connections, keeps some of those slots back. Its default is 3. Once the number of active connections reaches max_connections minus superuser_reserved_connections, only superusers can connect.

The check lives in InitPostgres() in src/backend/utils/init/postinit.c. On PostgreSQL 15 and earlier, a regular backend for a role that isn’t a superuser asks whether there are still ReservedBackends free slots. If not, it raises FATAL with SQLSTATE 53300, which errcodes.txt names too_many_connections.

PostgreSQL 16 split the reserve in two. The release notes add a reserved_connections setting for roles with the pg_use_reserved_connections role, and the REL_16_STABLE source rewords the message. You now see one of these, still with SQLSTATE 53300:

FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute
FATAL:  remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role

The first appears when fewer free slots remain than superuser_reserved_connections. The second appears when the free slots sit inside the reserved_connections band and your role lacks pg_use_reserved_connections. reserved_connections defaults to 0, so on most PostgreSQL 16 and later servers you only ever see the SUPERUSER wording.

When the last slot goes too, everyone gets sorry, too many clients already, from the same SQLSTATE. So the ceiling for your app’s roles is:

usable slots = max_connections - superuser_reserved_connections - reserved_connections

On a default server that is 100 - 3 - 0 = 97.

Why the slots ran out: who holds the connections

Before you change anything, connect as a superuser (the reserve exists for exactly this) and count the connections by role, application and state. pg_stat_activity has one row per server process. The statistics docs say backend_type is client backend for ordinary client sessions, and state is one of active, idle, idle in transaction, idle in transaction (aborted), fastpath function call or disabled.

SELECT usename,
       application_name,
       client_addr,
       state,
       count(*)                              AS connections,
       max(now() - state_change)             AS longest_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, client_addr, state
ORDER BY connections DESC;

Then compare the total with the limit:

SELECT current_setting('max_connections')::int                AS max_connections,
       current_setting('superuser_reserved_connections')::int AS superuser_reserved,
       current_setting('reserved_connections')::int           AS reserved,  -- PostgreSQL 16+
       (SELECT count(*) FROM pg_stat_activity
        WHERE backend_type = 'client backend')                AS client_connections;

On PostgreSQL 15 and earlier, drop the reserved_connections line, because the setting doesn’t exist there.

On Supabase, the role tells you which service owns a connection. The connection management guide maps them: authenticator is the Data API (PostgREST), supabase_auth_admin is Auth, supabase_storage_admin is Storage, supabase_admin is Supabase monitoring and Realtime, and postgres or your own roles are the Dashboard and external tools such as Prisma, SQLAlchemy and psql.

The result usually points at one of four causes.

Serverless functions connecting straight to Postgres

How to tell it’s yours: a large count for one role, many distinct client_addr values, short connection lifetimes, and the error lines up with traffic spikes or a burst of function invocations.

Each function instance opens its own connection, or its own pool, and many instances run at once. Supabase’s connection guide recommends the shared pooler in transaction mode for serverless and edge functions, because they open many short-lived connections. Transaction mode hands a server connection to a client only for the length of a transaction, so many clients share a few Postgres slots.

Fix: send serverless traffic through a transaction-mode pooler.

1. Copy the transaction pooler string from the Supabase Dashboard. It uses port 6543 and a pooler host you can’t build from the region alone:

   postgresql://postgres.[PROJECT-REF]:[YOUR-PASSWORD]@[POOLER-HOST]:6543/postgres

2. Create the client once at module scope, outside the request handler, so warm invocations reuse it.
3. Set the pool to 1 connection per function instance.
4. Turn off prepared statements, which transaction mode doesn’t support. Supabase lists prepare: false for Postgres.js and Drizzle, pgbouncer=true for Prisma, statement_cache_size=0 for asyncpg and prepareThreshold=0 for JDBC.
5. Set SSL to require.

For Postgres.js, that looks like:

import postgres from "postgres";

// Module scope: one client per function instance.
const sql = postgres(process.env.DATABASE_URL!, {
  max: 1,
  prepare: false,
  ssl: "require",
});

export default async function handler() {
  const rows = await sql`select now()`;
  return Response.json(rows);
}

Keep migrations off the transaction pooler. Supabase says to run them over the direct connection, because they are single sessions using native Postgres commands.

Outside Supabase, PgBouncer does the same job. Its configuration reference defaults pool_mode to session, which only returns a server connection to the pool after the client disconnects. Set it to transaction for serverless clients:

[databases]
app = host=10.0.0.5 port=5432 dbname=app

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

default_pool_size (default 20) is the most server connections PgBouncer opens per user and database pair. max_client_conn (default 100) is how many clients it accepts. Keep the sum of server-side pools below your usable slots.

Idle and leaked connections

How to tell it’s yours: most rows have state = 'idle' or 'idle in transaction', and longest_in_state runs to minutes or hours. Often one application_name dominates.

An idle session is waiting for its next command. An app that opens connections and never closes them, or a pool with no idle timeout, fills the slots this way. idle in transaction is worse. The client defaults docs say an open transaction also stops vacuum from removing recently dead rows, which leads to table bloat.

Fix: release them now, then stop them building up.

1. List the worst offenders:

   SELECT pid, usename, application_name, state, now() - state_change AS for_how_long
   FROM pg_stat_activity
   WHERE backend_type = 'client backend'
     AND state IN ('idle', 'idle in transaction')
   ORDER BY state_change
   LIMIT 50;

2. End the idle sessions of the app that leaks. pg_terminate_backend sends SIGTERM to the backend. The admin functions docs say you need to be a member of the target’s role or have pg_signal_backend, and only superusers can end superuser sessions:

   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE backend_type = 'client backend'
     AND state = 'idle'
     AND application_name = 'my-api'
     AND state_change < now() - interval '10 minutes';

3. Set idle_in_transaction_session_timeout for the app’s role. It ends any session that stays idle inside an open transaction for longer than the value, and its default of 0 disables it:

   ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

   New sessions for that role pick up the setting. Existing ones keep the old value until they reconnect.
4. Fix the leak in code. Release every connection in a finally block, or use the pool’s query helper, which checks a connection out and back in for you.

idle_session_timeout also exists, for sessions idle outside a transaction. The Postgres docs warn against using it on connections that come through a pooler or other middleware, which may not react well when the server closes a connection without warning. Apply it per role for interactive users, if at all.

Pool size multiplied by replicas and services

How to tell it’s yours: the count per client_addr is steady and equals your pool size, and the total grew when you scaled out, added a worker, or deployed a new service on the same database.

Every app instance keeps its own pool. The total is pool size times instances, summed across every service. The Prisma connection pool docs give two defaults worth knowing. Prisma ORM 6 used num_cpus::get_physical() * 2 + 1 connections per instance. The Prisma 7 pg driver adapter uses max: 10. Ten replicas on Prisma 7 can ask for 100 connections before anything else connects.

Fix: budget the slots and cap each pool.

1. Write down every client: app replicas, workers, cron jobs, migrations, BI tools and admin sessions.
2. Divide the usable slots between them, with headroom. On Supabase, the guide says to stay under 40% of the database’s max connections for the pooler if you use the Data API heavily, and up to 80% otherwise, to leave room for Auth and other services.
3. Set each pool’s maximum explicitly. With the Prisma 7 pg adapter:

   import { PrismaPg } from "@prisma/adapter-pg";
   import { PrismaClient } from "./generated/prisma/client";
   
   const adapter = new PrismaPg({
     connectionString: process.env.DATABASE_URL,
     max: 5,
     idleTimeoutMillis: 10_000,
   });
   export const prisma = new PrismaClient({ adapter });

4. Recheck the budget whenever autoscaling limits change. The number that matters is the maximum replica count.

max_connections set too low for the workload

How to tell it’s yours: the connections are all legitimate and mostly active, pools are already capped, a pooler is already in place, and you still hit the limit at peak.

On Supabase, max_connections follows the compute size. The compute docs list 60 for Nano and Micro, 90 for Small, 120 for Medium, 160 for Large and 240 for XL, rising to 500 on the largest sizes. The Supabase troubleshooting entry for this error says that if you already pool and still hit the limit, upgrade the compute add-on.

Fix: raise the ceiling, knowing what it costs.

1. On Supabase, upgrade the compute size in the Dashboard, or set the value directly with the CLI. The config guide marks max_connections as CLI only, and changing it restarts the database:

   supabase postgres-config update --project-ref <project-ref> --experimental \
     --config max_connections=200

2. On self-managed Postgres, edit postgresql.conf and restart:

   max_connections = 200
   superuser_reserved_connections = 3

3. On a standby, set max_connections to the same value or higher than on the primary. The Postgres docs say queries aren’t allowed on the standby otherwise.

PostgreSQL sizes some resources, including shared memory, directly from max_connections. A bigger limit costs memory on every server, so treat it as the last lever.

Confirming the database has headroom again

Run the count query again during normal traffic and at peak. The fix holds when:

SELECT count(*) AS client_connections,
       current_setting('max_connections')::int
         - current_setting('superuser_reserved_connections')::int
         - current_setting('reserved_connections')::int AS usable_slots
FROM pg_stat_activity
WHERE backend_type = 'client backend';

client_connections stays clearly below usable_slots, and your logs show no new FATAL lines with SQLSTATE 53300. Redeploy or scale out once on purpose and watch the count. It should rise by your per-instance pool size and no more.

Stopping the slots filling up again

• Alert on connection pressure. Alert well before client connections reach the usable slots. Supabase’s Grafana dashboard has a Client Connections graph for both Supavisor and Postgres, and the Dashboard’s Database client connections chart (Teams and Enterprise) breaks them down by Postgres, PostgREST, Reserved, Auth, Storage and other roles.
• Alert on the log line. Match remaining connection slots are reserved and too many clients in your Postgres logs. Either one means users are already seeing errors.
• Cap every pool in code. Never rely on a driver’s default, which differs between libraries and versions.
• Put a budget in the deploy checklist. Max replicas times pool size, summed across services, must fit inside the usable slots.
• Keep the idle-in-transaction timeout on for application roles.
• Leave the superuser reserve alone. It is what lets you log in and run the queries above during an incident.

Tracing a full Supabase connection pool with Polylane

Polylane connects a Supabase organisation, syncs each project’s database with logs and checks on it, and links the auth, storage, realtime and REST services to the Postgres database they sit in front of. It also triages the account’s alerts. When a confirmed cause is a code defect, such as a pool with no cap, the fix arrives as a pull request for you to review.

Running on Supabase? See how Polylane monitors Supabase in production.