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.

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