PostgreSQL too many clients already: 30-second triage recovery runbook

You searched for this error and landed here, right?

FATAL: sorry, too many clients already — if you pasted that exact message into a search box and ended up here, your service cannot connect to the database right now. We'll skip the theory. This post is a recovery sequence. Run the commands in the order you scroll, and the outage ends.

Here's the order of operations.

1. First, use the 30-second triage table to classify the cause (spike / leak / misconfiguration).
2. Confirm the current state with copy-paste diagnostic SQL, then terminate sessions safely as an emergency measure.
3. Move on to permanent fixes by cause type.
4. Finish with a pgbouncer adoption decision and recurrence-prevention alerts.

One thing up front. MySQL's ERROR 1040: Too many connections is often fixed by simply raising max_connections. PostgreSQL is different: it uses a process-per-connection model (one OS process per connection), so blindly raising the cap can blow memory. That's why pooling is the core fix for PostgreSQL. If you're on MySQL, use a separate ERROR 1040 runbook. For PostgreSQL, this post is the whole path.

Scope: PostgreSQL 10+, RDS/Aurora PostgreSQL, and self-hosted Linux. Commands assume you are already connected via psql.

First, get a session: secure an admin connection

Regular connections may already be exhausted, so even psql might fail. PostgreSQL reserves superuser_reserved_connections (default 3) for superusers. Connect as a superuser and you can still get in through those reserved slots.

# (postgres) when —
psql -U postgres -h <host> -p 5432 -d postgres

Once you're in, go to the triage table below. If even this fails, reserved slots are exhausted too—scale down some application instances to force connections back, then reconnect.

30-second triage table: which case is this?

The time pattern of the incident tells you the cause. Find your symptom in the table and jump to that section.

The key distinction: a leak (②) gets worse over time; a spike (①) piles up then drains; misconfiguration (③) was wrong from the start. If you're unsure, run the diagnostic SQL next—the data will tell you.

Immediate diagnosis & emergency recovery SQL

Step 1 — Connection distribution by state

Run this first. You'll see at a glance which states the connections are piled into.

--
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count DESC;

How to branch on the result:

• Mostly active → real query load, spike (①). Suspect slow queries or lock waits.
• Many idle in transaction → leak (②) confirmed. Sessions that opened a transaction and never committed/rolled back. Go to step 2.
• Many idle → the pool is holding connections it isn't using. Likely oversized pool (③).

application_name and client_addr tell you right away which service is holding the connections.

-- Connections by service (application) and state
SELECT application_name, client_addr, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3
ORDER BY count(*) DESC;

If you set application_name in the driver (ApplicationName for JDBC), you can pinpoint the offending service immediately during an incident.

Step 2 — Find long-abandoned idle in transaction sessions

idle in transaction is the classic cause of PostgreSQL connection exhaustion. If the app opens a transaction and never commits, that connection stays occupied and even blocks cleanup (VACUUM) for other transactions.

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

If you see sessions with idle_dur of several minutes or more, the query column gives you a clue to trace which application code left the transaction open.

Step 3 — Safe terminate (always the two-step workflow)

⚠️ Do not run pg_terminate_backend blindly. First SELECT the targets and eyeball them, then terminate. Do not kill system processes or your own session.

-- (1) quit check: 5 idle in transaction,
SELECT pid, usename, now() - state_change AS idle_dur, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes'
  AND pid <> pg_backend_pid();

Once the list matches what you expect, terminate with the same predicates.

-- (2) check quit
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes'
  AND pid <> pg_backend_pid();

The pg_backend_pid() predicate protects your own psql session. After terminating, rerun the step-1 aggregate SQL to confirm connections were released. Once they are, the application can reconnect. That is the emergency recovery. If the service is back, take a breath and move on to the root-cause fixes below.

Permanent fixes by root cause

(a) Raising max_connections — calculate the memory tradeoff first

Check the current values first.

SHOW max_connections;
SHOW superuser_reserved_connections;
SHOW work_mem;

There is a trap you must account for before raising max_connections. PostgreSQL allocates work_mem per session for each sort/hash operation, and a complex query can use work_mem multiple times in one session. A rough worst-case estimate:

 ≈ work_mem × max_connections × ( /when )

) work_mem 16MB × max_connections 200 = 3.2GB
 ( to)

So if you blindly bump max_connections from 200 → 500, a traffic spike can OOM-kill the DB process. On RDS/Aurora, the default max_connections is calculated from instance memory. You can change it in a custom parameter group, but raising it without memory headroom carries the same OOM risk.

max_connections is only read at server start, so changing it requires a restart.

ALTER SYSTEM SET max_connections = 300;
-- A reload does not apply it. Restart PostgreSQL, then check with SHOW max_connections

Bottom line: a small bump of the cap is an emergency patch; the real fix is pooling so you need fewer connections. If you must raise it, keep superuser_reserved_connections at least 3–5 so you still have an admin path in.

(b) Connection pool misconfiguration — instance count × pool size

The typical cause of hits the cap immediately after restart (③) is that the sum of application pools exceeds max_connections. Do the math first.

 × ≤ max_connections − reserved

) 20 × HikariCP maximumPoolSize 10 = 200
 → max_connections 200 0,

In serverless/container autoscaling, when pods go from 40 to 80, that product explodes and connections exhaust in seconds. That's the main reason this error is showing up more often.

Turn on HikariCP's leakDetectionThreshold (e.g. 30000ms) and it logs connections that were never returned. That's decisive for finding leaky code.

(c) ORM leak patterns: transactions left open

If idle in transaction keeps accumulating, the root cause is in the code. Common patterns:

• Holding a connection while doing external API calls or long computation inside a transaction block
• Exception paths that skip rollback/close
• Autocommit off (manual transactions) and a forgotten commit
• Web framework config that doesn't close the session per request

Fix: open transactions short, close them short, and move external I/O outside the transaction. As a server-side safety net, set idle_in_transaction_session_timeout so abandoned transactions are terminated automatically.

-- 5 after quit (/ settings )
SET idle_in_transaction_session_timeout = '5min';

pgbouncer adoption decision table

If you have too many instances to absorb the connection product, the answer is a connection pooler. pgbouncer (or RDS Proxy) sits between the app and the DB, reuses connections, and serves hundreds of client requests with a handful of real DB connections. Mode choice is the key decision.

When to adopt: instance count is variable (autoscaling) and instance count × pool size is already approaching max_connections. If you use transaction mode, check the prepared-statement conditions below.

Minimal pgbouncer configuration

; /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000        ; clients that can connect to pgbouncer
default_pool_size = 20        ; real DB connections per database/user pair
max_prepared_statements = 200 ; 1.21+ (default 200 since 1.24)

userlist.txt lists one user name and password hash per line. For SCRAM, you can use the value of pg_authid.rolpassword from the database as is.

"app_user" "SCRAM-SHA-256$4096:..."

In the application DSN, change only the host and port to pgbouncer (postgresql://app_user:****@pgbouncer-host:6432/mydb).

Transaction mode and prepared statements: since PgBouncer 1.21, setting max_prepared_statements to a non-zero value makes it track protocol-level prepared statements, so they work in transaction mode (SQL-level PREPARE/EXECUTE is not covered). On older versions you must turn off server-side prepared statements in the driver; for the PostgreSQL JDBC driver, for example, add prepareThreshold=0 to the DSN. Session variables (SET), advisory locks, and LISTEN/NOTIFY are not guaranteed in transaction mode regardless of version.

Preventing recurrence: alert queries

Don't stop at emergency recovery. Put monitoring in place so you catch the same failure before it happens.

-- + max_connections 80%
SELECT count(*) AS current_conns,
       current_setting('max_connections')::int AS max_conns,
       round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct
FROM pg_stat_activity
HAVING count(*) > current_setting('max_connections')::int * 0.8;

-- idle in transaction 10
SELECT count(*) AS idle_in_tx
FROM pg_stat_activity
WHERE state = 'idle in transaction'
HAVING count(*) > 10;

Schedule these two queries in your monitoring stack (e.g. Prometheus postgres_exporter, CloudWatch custom metrics). You'll get an alert before connections hit the wall.

If you hit ERROR 1040: Too many connections on MySQL, the process model and the response are different—use a separate MySQL connection runbook.

FAQ

Q. Can I just raise max_connections? A. As a temporary measure, yes. But PostgreSQL forks a process per connection and allocates work_mem per session, so a large bump raises OOM risk at traffic peaks. The real fix is pooling (pgbouncer/RDS Proxy) so you need fewer connections.

Q. Does killing a session with pg_terminate_backend corrupt data? A. The in-flight transaction on that session is rolled back. Only uncommitted work is cancelled, so data integrity is preserved. Always SELECT the targets first so you don't kill the wrong session.

Q. What's the difference between idle in transaction and idle? A. idle is a normal wait for the next command with no open transaction. idle in transaction is waiting for the next command with a transaction still open—it keeps holding the connection and locks and blocks VACUUM, so it's the main leak culprit.