Out of Postgres Connections? Find the Limit You Hit and Get Back In

"No connections left" is not one symptom. It is at least four different limits with four different fixes, and picking the wrong one makes things worse. I filled a Nano-sized Supabase lab on purpose and worked through the recovery steps one at a time, with the real error text and real timings.

Three things I did not expect: when the database is full, postgres cannot connect either; terminating idle backends recovers it in milliseconds; and raising the pool size made throughput worse for one of the two query shapes I tested.

How this was made: I wrote this with Claude's help: it drafted, and I ran every step, edited the text, and checked the results. The mistakes are mine.

The companion piece — how connections are counted in the first place, and why they multiply: https://dev.to/tomozilla/supabase-connection-budgets-pool-size-roles-modes-and-limits-4lc8
The code: https://github.com/tomozilla/connection-budget-en-demo

What I did and did not do

• Everything new here ran in a local lab: a Supabase CLI stack with max_connections 90, pooler pool size 15, client cap 200 — Nano-like limits where going past a limit hurts nobody. Cost: $0.
• I never filled a hosted database. It is shared infrastructure. So every "while full" result below is from the lab.
• The hosted numbers I reuse (the client-cap scope, the live pool-size change, the Micro → Small upgrade) were measured on a throwaway Pro project that I created and deleted the same day, in the companion article.
• Each experiment ran a few times at most.

1. First, read the error text

This is the step people skip. "Out of connections" covers at least four separate limits, and the fix differs for each.

→ The first three are inside the pooler; your database may still have room. The fourth means the database itself is full. Those are different emergencies.

Don't skip the last row. In the companion article, going from 5 clients to 100 changed throughput not at all — only the wait, from 0.1 s to 0.6 s — with zero errors the whole time.

2. When it is full, you cannot get in — not even as postgres

This surprised me most, and it changes what "preparation" means.

I filled max_connections with idle connections on one role, then tried to log in as several roles.

filled: 51 connections held, 39 refused remaining connection slots are reserved for roles with the SUPERUSER attribute 

→ postgres is refused too, because on Supabase it is not a superuser and cannot use the reserved slots (superuser_reserved_connections, which is 3 on both hosted and the lab). The role you normally administer with is unavailable exactly when you need it.

That makes this a preparation problem, not a recovery step:

• Decide before the incident how you will get in. Searching for a way while it is down is too late.
• The dashboard's SQL editor reaches the database by a different path than a client connection. Mine worked when the database was not full; I did not test it against a full database, so I can't promise it.
• PostgreSQL 17 has reserved_connections: set it, grant a role pg_use_reserved_connections, and that role still connects when full. It worked in the lab. I have not confirmed you can set it on a hosted project (the hosted value was 0), so do not build your plan on it.
• In practice, knowing who can open the dashboard is the reliable answer.

3. See what is actually holding the connections

Once you are in, count before you act.

select usename, application_name, state, count(*) as n, max(now() - state_change)::interval(0) as longest_in_state from pg_stat_activity where backend_type = 'client backend' group by 1, 2, 3 order by n desc; 

Three columns matter. usename tells you whether one role dominates, or whether it is a Supabase service (supabase_auth_admin, supabase_storage_admin). application_name tells you the route: Supavisor means through the pooler, PostgREST 14.x means the Data API, empty usually means a direct connection. And then state:

→ Lots of idle is a connection problem. Lots of active is a query problem. Skip this distinction and §7 will send you the wrong way.

4. Stop the bleeding: terminate

If idle is the culprit, kill those backends.

-- In a real incident, always filter by age so you don't kill connections that just opened select pg_terminate_backend(pid) from pg_stat_activity where backend_type = 'client backend' and usename = 'r1' and state = 'idle' and now() - state_change > interval '1 minute'; 

From a full database in the lab:

→ Recovery is effectively instant. The slow part of an incident is deciding to do this, not doing it.

Two cautions. Drop state = 'idle' from that query and you will terminate running queries too, rolling back whatever they were writing. And what the killed client sees depends on the library: node-postgres' Pool crashed the process without an error handler (Unhandled 'error' event), recovered from the next query with one, and through the pooler produced no event at all.

Terminating does not fix the cause. The same app will reopen the same connections within minutes, so continue to §8.

5. Stop the bleeding: change the pool size (no restart)

If the pooler is the bottleneck, the pool size is the dial. The question I actually cared about was whether changing it forces a restart, because a restart during an incident is its own problem.

Measured on a hosted project: the same load — 10 clients each holding an 8-second query — run twice, changing only the pool size in the dashboard between runs.

→ It took effect immediately, with no restart. It works downward too: if one role is hogging the database, lowering the pool size hands capacity back to the others.

Before you reach for "raise it", though, read §7.

6. Stop the bleeding: split roles — but only for EMAXCONN

If you hit the client cap, this is different, because that cap is per role. Measured on a hosted project, with idle connections and no queries at all:

→ A second role really does buy another 200 client slots. As a stopgap for EMAXCONN, it works.

Now the catch. While those 300 clients were connected, the database held:

300 clients (2 roles) -> 2 backends 

→ Two. One per role, because nothing was running a query. Adding a role buys client slots and zero backend slots. If those 300 clients all start querying, you have moved the queue to max_connections, not removed it.

7. "Just raise the pool size" is half right

This is the part I got wrong before measuring.

Remember state from §3. Whether raising the pool helps depends entirely on whether the backends are waiting or burning CPU. To separate the two I used queries that both take about 100 ms for opposite reasons:

• wait: pg_sleep(0.1) — the backend is idle, waiting (think an external API call or a lock).
• cpu: count(*) over 2,000,000 rows — the backend is saturating a core (think a heavy aggregate).

60 concurrent clients, closed loop, pool size 15 vs 30:

→ The same change that nearly doubled one workload made the other measurably worse. The cpu row was 38, 44 and 44/s across three runs, against 48–51/s at pool 15.

The reason is obvious in hindsight: raising the pool does not add CPU. Thirty-one backends contending for the same cores each run slower, and the extra context switching costs more than it returns. Queries that are merely waiting have nothing to contend over, so they scale cleanly.

So the procedure is:

1. Look at state (§3).
2. Mostly idle or waiting → raising the pool size is worth it (§5, and it applies live).
3. Mostly active → raising the pool makes it worse. Fix the query: pg_stat_statements, an index, fewer rows returned. If you can't, go to §9.

8. Remove the cause, or it comes back

A leaked transaction: idle in transaction

If §3 showed idle in transaction, fix that first. The connection never comes back on its own, and it blocks vacuum on the tables it touched.

I opened a transaction and did nothing:

state: idle in transaction, held: 2 seconds (and counting, indefinitely) 

The fix is a timeout the client cannot wait out:

alter role r1 set idle_in_transaction_session_timeout = '5s'; 

→ The server killed it at 5.0 s: terminating connection due to idle-in-transaction timeout.

Fixing the application bug is the real answer, but this setting is a seatbelt you can fasten today, before you find the bug.

One role starving everyone

Per-role limits work, but the value matters. With pool size 15 and 40 clients running 5-second queries:

→ Set it to at least pool size + 1. At exactly the pool size, the 16th backend is refused and clients that could have waited turn into errors instead. (Supavisor's transaction mode opens pool size + 1; that is measured in the companion article.)

Apply this to a role you created, not to authenticator (it would stop the Data API) and not to postgres.

A backstop the client cannot remove

statement_timeout is not a guardrail, because any role can change its own: a client can run SET statement_timeout = 0, and on the transaction pooler that zero is then inherited by the next client. Permissions do not stop this.

So the backstop has to live outside the session:

select cron.schedule('cancel-long-queries', '5 seconds', $$ select pg_cancel_backend(pid) from pg_stat_activity where usename = 'batch_user' -- scope it to a role you created and state = 'active' and now() - query_start > interval '6 seconds' $$); 

→ A client that had removed its own timeout and started a 30-second query was cancelled after 10–11 s (6 s plus the job interval).

Expect connections to linger

Closing clients does not free the slots at once: about 2 minutes in my measurements, and the docs say up to 5. Budget for 5. During an incident, "I stopped it and nothing freed up" is expected, not a second fault.

9. Last resort: a bigger instance

If §7 put you in the active column, this is where you end up. I upgraded the throwaway project to see what actually changes:

→ Connections and client slots both grow, but your pool size does not. A bigger instance does not widen the pool for you — raise it yourself (§5), or you will have paid for headroom you aren't using.

One more thing worth knowing: moving to a paid plan buys no connections at all. Nano (free) and Micro (smallest paid) both have 60. Only compute size moves that number. Compute is billed hourly and is not covered by the Spend Cap.

A checklist for when it is quiet

1. Decide who can get in when it is full. postgres cannot (§2).
2. Keep the §3 query somewhere you can paste it, with the three meanings of state.
3. Set idle_in_transaction_session_timeout on your roles now (§8).
4. Set each pooled role's connection limit to pool size + 1 or more (§8).
5. Put a query-killing backstop outside the session (§8).
6. Alert on p95 latency, not on error count — the failure mode is silence (§1).
7. Run the budget equation from the companion article against your current setup.

Takeaways

• "Out of connections" is four different limits. Read the error text before acting (§1).
• When the database is full, postgres cannot connect — only supabase_admin could. Arrange access in advance (§2).
• state in pg_stat_activity decides your next move: idle → terminate, active → fix the query, idle in transaction → top priority (§3).
• Terminating idle backends is instant: 51 in 12 ms, a normal connection 3 ms later (§4).
• Pool size changes apply live, no restart: the same work took 25.3 s at pool 3 and 9.3 s at 15 (§5).
• Splitting roles buys client slots and no backend slots: 300 clients held 2 backends (§6).
• Raising the pool size helped waiting queries (154 → 295/s) and hurt CPU-bound ones (48 → 44/s). Check state first (§7).
• statement_timeout can be removed by the client; the backstop must live outside the session (§8).
• A bigger instance raises max_connections and the client cap but leaves the pool size at 15 (§9).
• Every "while full" result here is from a local lab. I did not fill a hosted database.

The lab scripts for all of this are in the repo: https://github.com/tomozilla/connection-budget-en-demo