Diagnosing Postgres connection exhaustion (too many clients already)

Symptoms

• New connection attempts fail with `FATAL: sorry, too many clients already`
• Requests hang rather than error outright, because the app-side pool is waiting for a server connection that never frees up
• The problem worsens under load and clears itself after a deploy or restart

The diagnostic path

9 steps, exactly as this revision published them.

1. Step 1 · StartNew connections are being refused with 'too many clients already'

   Postgres has a hard max_connections ceiling; once every slot is in use, every further connection attempt is rejected at the protocol level, before authentication even completes.

   What happens next

   • passed → step 2, Check whether you're actually at max_connections

2. Step 2 · TestCheck whether you're actually at max_connections

   Run as any role that can connect — this reads shared server state, not per-role limits.

   Read-onlysql

   Copy

   SELECT (SELECT count(*) FROM pg_stat_activity) AS current_connections,
          (SELECT setting FROM pg_settings WHERE name = 'max_connections') AS max_connections;

   Expected result

    current_connections | max_connections 
   ----------------------+-----------------
                      98 | 100
   (1 row)

   What happens next

   • passed → step 3, Check for connections stuck idle in an open transaction
   • failed → step 4, Not actually at the connection limit
   • unknown → step 4, Not actually at the connection limit

3. Step 3 · TestCheck for connections stuck idle in an open transaction

   This is the single most common actionable cause: a connection that isn't doing any work but is still holding its slot because its transaction was never committed or rolled back.

   Read-onlysql

   Copy

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

   Expected result

    pid  | usename | application_name |        state        |    idle_for     |               query                
   ------+---------+-------------------+----------------------+------------------+-------------------------------------
    8821 | appuser | api-worker-3      | idle in transaction | 00:14:52.331     | UPDATE orders SET status = $1 ...
    8790 | appuser | api-worker-1      | idle in transaction | 00:11:07.998     | SELECT * FROM accounts WHERE ...
   (2 rows)

   What happens next

   • passed → step 5, Application code is leaving transactions open
   • failed → step 6, Genuine concurrency is exceeding max_connections
   • unknown → step 6, Genuine concurrency is exceeding max_connections

4. Step 4 · EndNot actually at the connection limit

   If current connections aren't close to max_connections, this specific error isn't happening because of server-side exhaustion right now. Check the app-side pool's own configuration and error (see the node-postgres connection-timeout playbook) or a transient network issue — out of scope here.

5. Step 5 · Root causeApplication code is leaving transactions open

   Connections sitting in idle in transaction aren't doing work but still count fully against max_connections. Common causes: a missing COMMIT/ROLLBACK on an exception path, a connection checked out of a pool and never released back to it (see the node-postgres pool-leak playbook), or a long pause inside a transaction — e.g. waiting on an external API call — before committing.

   What happens next

   • passed → step 7, Free the stuck connections, then stop the leak

6. Step 6 · Root causeGenuine concurrency is exceeding max_connections

   If there's no meaningful idle-in-transaction backlog, the connections are legitimately active/short-lived and the workload has simply outgrown the configured limit. Each Postgres connection is a full backend process — raising maxconnections carelessly trades one problem (connection refusal) for another (memory pressure from workmem × connections, and more contention on shared resources). The standard fix at this point is a connection pooler such as PgBouncer in front of Postgres, or reducing how many connections each application instance opens, rather than an unbounded increase to max_connections.

7. Step 7 · FixFree the stuck connections, then stop the leak

   For immediate relief, terminate the backends found above that have been idle-in-transaction well beyond any legitimate reason. This is destructive to whatever those transactions were doing — only run it once you've confirmed they're actually stuck, not just slow. For the durable fix: find and correct the code path that isn't committing/rolling back, and set idle_in_transaction_session_timeout so a future leak can't silently hold connections open indefinitely.

   Destructivesql

   Copy

   Can delete data or break a running service. This is not reversible.

   Forcibly kills every backend that has been idle inside an open transaction for more than 10 minutes. Each killed connection's in-progress transaction is rolled back and the client sees its connection drop — any work that transaction had not committed is lost.

   I understand what this command does.

   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE state = 'idle in transaction'
     AND now() - state_change > interval '10 minutes';

   What happens next

   • passed → step 8, Confirm there's real headroom again

8. Step 8 · Verify the fixConfirm there's real headroom again

   Re-run the same check used at the start.

   Read-onlysql

   Copy

   SELECT (SELECT count(*) FROM pg_stat_activity) AS current_connections,
          (SELECT setting FROM pg_settings WHERE name = 'max_connections') AS max_connections;

   Expected result

    current_connections | max_connections 
   ----------------------+-----------------
                      41 | 100
   (1 row)

   What happens next

   • passed → step 9, Resolved
   • failed → step 6, Genuine concurrency is exceeding max_connections
   • unknown → step 6, Genuine concurrency is exceeding max_connections

9. Step 9 · EndResolved

   Connection count has real headroom. Keep the idleintransactionsessiontimeout in place so this doesn't quietly recur.

Sources

• 20.3. Connections and Authentication— PostgreSQL
• Chapter 28. Monitoring Database Activity— PostgreSQL