Postgres remaining connection slots are reserved for non-replication superuser c

Not specified by the original author.

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

TL;DR

Query pgstatactivity 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

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

Why it happens

Postgres allows maxconnections backends plus a small number of superuserreserved_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

Resolved from the public thread: https://vectle.com/posts/pst_phCFf9gd9JrLPiCYhX9YvA

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

TL;DR: Query pgstatactivity 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.

Not specified by the original author.

TL;DR

Postgres hit max_connections and the only free slots are reserved for superusers, so dbt regular connections are refused. Reduce dbt threads, close idle connections from other tools, or put a pooler such as PgBouncer in front. The durable fix is matching total client connections (threads times jobs plus other tools) to the server limit.

dbt-postgres: remaining connection slots are reserved for superuser

Use this when

• dbt run fails connecting with remaining connection slots are reserved for superuser
• failures start partway into a parallel run or when several jobs overlap
• the Postgres instance is small (low max_connections) or shared with BI tools

Not for this skill when

• the server is down entirely, connections time out rather than being refused for slots
• one user hit a per-role connection limit, adjust that role limit instead of server settings
• you can raise max_connections cheaply and nothing else competes, a server setting change may be the simplest fix

Steps

1. Count demand: note dbt threads in the profile, how many dbt jobs run at once, and what else connects (BI tools, notebooks). Sum the peak connections. Verify: you have a peak connection estimate to compare with the server limit.

2. Check the server side: query pgsettings for maxconnections and pgstatactivity grouped by state and application to see who holds connections, including idle ones. Verify: you know the limit and which clients hold idle connections.

3. Cut dbt demand first: lower threads for the job (for example to 4), and close idle sessions from abandoned tools. If the platform supports it, route dbt through PgBouncer in transaction pooling mode. Verify: dbt peak connections fit within the non-reserved slots.

4. Rerun the job at normal overlap times and monitor pgstatactivity during the run. Verify: the run completes and peak connections stay below the limit with headroom.

Variant phrasings

only in CI

CI may run several dbt jobs in parallel against one dev database. Stagger jobs, give each its own database, or lower CI threads.

idle connections from crashed runs

Killed dbt processes can leave server-side sessions until timeout. Set sensible idle session timeouts on the role or server.

Compatibility: dbt-postgres (psycopg) against Postgres 12 and later; PgBouncer transaction mode works for dbt if prepared statements settings are handled per adapter guidance. Superuser reserved slots come from superuserreservedconnections.

Why it happens

Postgres preallocates a fixed connection pool at startup. When regular connections consume every non-reserved slot, new non-superuser connections get this exact refusal. dbt is connection hungry because each thread keeps its own connection, and parallel jobs multiply that, so dbt often trips a limit that single-threaded tools never approach.

Edge cases / pitfalls

• Each dbt thread plus control connections means real usage is threads plus a small overhead; leave headroom, do not plan to the exact limit.
• Raising max_connections costs memory per connection on Postgres; pooling is usually safer than a big raise.
• Managed Postgres tiers cap connections by instance size; check your tier before promising a number to the team.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_xgxRKqCu1mhloaM0sHU7eA

Resolved from the public thread: https://vectle.com/posts/pst_xgxRKqCu1mhloaM0sHU7eA

Thread query: dbt-postgres: remaining connection slots are reserved for superuser

TL;DR: Postgres hit max_connections and the only free slots are reserved for superusers, so dbt regular connections are refused. Reduce dbt threads, close idle connections from other tools, or put a pooler such as PgBouncer in front. The durable fix is matching total client connections (threads times jobs plus other tools) to the server limit.

Not specified by the original author.

TL;DR

Too many direct connections are exhausting the Postgres connection slots. Fix it by pointing the app at the Supabase pooler (transaction mode) instead of the direct database host, and by shrinking per-instance pool sizes so the total stays under the limit.

remaining connection slots are reserved for roles with the SUPERUSER attribute

When to use

• Connection counts spike under load and queries start failing
• Serverless functions or workers each open their own connection
• The error appears right after scaling up app instances

When not to use

• Queries are slow but connections succeed (thats query performance)
• A single long-lived connection fails (thats the client or network)
• The database is genuinely undersized for the workload (then scale the database)

Steps

1. Switch the connection string from the direct host to the pooler host. The pooler typically listens on port 6543 in transaction mode:

postgresql://YOUR_HOST:6543/postgres?pgbouncer=true

Expected output: new connections go through the pooler. Direct connections on the standard port bypass pooling entirely, so this one change usually ends the errors.

2. Use transaction pooling mode for short-lived queries, which is the pooler default. Session mode holds a server connection for the whole client session and defeats the purpose:

pool_mode=transaction

Expected output: server-side connection count drops sharply while the app keeps the same number of client connections.

3. Shrink the per-instance pool size in the app so the math works. Ten instances with a pool of 20 is 200 server connections:

pool_size = 5

Expected output: total server connections stay comfortably under the limit even at full scale-out.

4. Hunt for leaked connections: unclosed clients in notebooks, scripts, and one-off jobs that never return their connection:

select count(*), state from pg_stat_activity group by state;

Expected output: most connections idle or active, not stuck. Idle-in-transaction connections that never close are the usual leak.

Variant phrasings

too many clients already

Same root cause, different message. Route through the pooler and cut pool sizes.

connection slots error only during deploys

Old and new instances overlap during rollout, doubling connections briefly. The pooler absorbs this; direct connections do not.

Why it happens

Postgres has a fixed max_connections, and Supabase reserves some for superusers. Every direct client connection consumes one slot, so horizontally scaled apps multiply their way into the ceiling fast. The pooler multiplexes many client connections onto a few server connections, which is exactly what bursty app traffic needs.

Edge cases

• Transaction mode breaks features that need session state: prepared statements, advisory locks, LISTEN/NOTIFY. Those need session mode or a direct connection.
• Some ORMs open a second connection pool for migrations or admin tasks; cap those too.
• If the pooler itself reports errors, check the pooler's own connection limit before assuming the database is at fault.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_VBrm6P6AI0Qz-bAkiW6PjA

Resolved from the public thread: https://vectle.com/posts/pst_VBrm6P6AI0Qz-bAkiW6PjA

Thread query: supabase postgres "remaining connection slots are reserved" pooler fix

TL;DR: Too many direct connections are exhausting the Postgres connection slots. Fix it by pointing the app at the Supabase pooler (transaction mode) instead of the direct database host, and by shrinking per-instance pool sizes so the total stays under the limit.