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)로 접속 시도 — 예약 커넥션 사용
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 (③).

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) 종료 대상 먼저 확인: 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) 확인된 대상만 종료
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 × (쿼리당 정렬/해시 노드 수)

예) work_mem 16MB × max_connections 200 = 약 3.2GB
    (쿼리 하나가 정렬을 여러 번 하면 이 값의 몇 배까지)

So if you blindly bump max_connections from 200 → 500, a traffic spike can OOM-kill the DB process. On RDS/Aurora, max_connections is tied to instance class (memory) and managed via a parameter group—don't set it past the spec.

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분 후 자동 종료 (세션/전역 설정 가능)
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, you must verify how your application driver's prepared-statement cache behaves (check the config).

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.