PostgreSQL 'too many clients already': From a 5-Minute Diagnosis to a PgBouncer Fix

PostgreSQL 'too many clients already': From a 5-Minute Diagnosis to a PgBouncer Fix

You're probably here because this line just showed up in your app logs.

FATAL: sorry, too many clients already

In plain English: every connection slot is taken. PostgreSQL hard-limits concurrent connections with max_connections. Any new connection that exceeds that limit is rejected immediately, before authentication. The database isn't dead — it just closed the door because there's no room.

Don't panic. Follow this in order, like an ER playbook.

1. Assess the situation — see who's eating connections (2 min)
2. Temporary recovery — kill dangerous sessions to bring the service back (1 min)
3. Root-cause fix — prevent recurrence with PgBouncer (do this calmly afterward)

1. 5-minute diagnosis: hunt down the culprit with pg_stat_activity

First you need a console session. If regular connections fail, try the 3 reserved superuser connections first. Once you're in, run these three SQL queries in order.

① Total connections vs. the ceiling

SELECT count(*) FROM pg_stat_activity;
SHOW max_connections;

If count is sitting right up against max_connections, you've confirmed it.

② Aggregate by state — where's the leak?

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

• active: actually running a query (could be normal load)
• idle: holding a connection and doing nothing (a sign the pool is oversized)
• idle in transaction: the most dangerous. Transaction left open without commit/rollback → occupies both locks and a slot

③ Rank the oldest idle-in-transaction culprits

SELECT pid,
       now() - state_change AS duration,
       usename, application_name,
       left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change ASC;   -- oldest first

If you see a string of duration values from several minutes to tens of minutes, the application isn't closing its transactions. That's the real culprit in most cases.

2. Immediate recovery: bring the service back first

Root-cause analysis can wait. Right now you need to free slots.

Bulk-terminate old idle-in-transaction sessions (only those older than 5 minutes)

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();   -- ★ must exclude your own session

If you omit pid <> pg_backend_pid(), you can kill the session that's issuing the command. Always include it.

To kill a single PID:

SELECT pg_terminate_backend(12345);

When you can't even get a console — reserved connections PostgreSQL reserves superuser_reserved_connections (default 3) slots for superusers. Even if every app account is blocked, a superuser like postgres can still get in. Set this to around 5 in normal times so you aren't locked out during an incident.

Practical tip: I don't blindly kill everything during an incident. I first use query ③ to look at application_name and identify the offending service in about a second. Overwhelmingly often it's a batch server that opened a transaction and died. After killing those sessions, redeploy that server first so it doesn't happen again.

3. Root-cause fix: why you shouldn't raise max_connections

This is where 90% of people fall into the trap. "So just bump max_connections to 500, right?" — No.

PostgreSQL starts a separate backend process per connection. Actual memory is roughly:

peak memory ≈ max_connections × work_mem × (number of sort/hash nodes)
            + fixed overhead per backend (several to tens of MB)

The key point: work_mem is multiplied per sort/hash operation in a single query. Complex queries can use several times work_mem. With work_mem at 16MB and max_connections at 500, memory can explode in the worst case and the OOM Killer takes down the DB. On top of that, once you pass 200–300 backends, context-switching cost actually drops throughput.

Note that changing this requires a restart.

ALTER SYSTEM SET max_connections = 200;
-- PostgreSQL restart required afterward (reload will not apply this)

In managed environments like RDS / Aurora / Cloud SQL, the ceiling is fixed per instance class, so you can't raise it indefinitely. When Lambda or K8s pods scale out, connections explode, and containing that with a DB-side limit is nearly impossible. That's why a connection pooler (PgBouncer / RDS Proxy) has become effectively mandatory.

PgBouncer transaction-mode setup

Core idea: the app throws hundreds of connections at PgBouncer, and PgBouncer keeps only a small number of connections to the DB and reuses them.

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      ; reuse per transaction (most efficient)
max_client_conn = 1000       ; max clients the app can connect
default_pool_size = 20       ; actual connections out to the DB

userlist.txt (SCRAM/md5 hash):

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

For the app DSN, just change the port from 5432 to 6432 and you're done.

postgresql://app_user:****@db-host:6432/mydb

⚠️ Transaction-mode caveat: connections are reassigned to different clients after each transaction, so anything bound to the session breaks. Typical examples: server-side prepared statements, SET session variables, advisory locks, and LISTEN/NOTIFY. Disable prepared-statement caching in the driver (JDBC prepareThreshold=0), or put session-dependent features on a separate pool (session mode).

Application connection-leak checklist

Even with a pooler, if the app never returns connections, you'll blow up the same way.

• HikariCP: Is maximumPoolSize appropriate for the DB instance class? Turn on leak tracking with leakDetectionThreshold=5000
• SQLAlchemy: Does pool_size + max_overflow stay under the ceiling? Check for missing session close()
• Paths that open a transaction and finish without commit/rollback (← the idle-in-transaction culprit)
• ORM lazy loading holding a connection after the request ends
• Missing connection.close() on exception paths — force try/finally or a with context

4. Wrap-up: one-page cheat sheet

Three diagnostic SQL queries

SELECT count(*) FROM pg_stat_activity;            -- current connections
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;  -- by state
SELECT pid, now()-state_change, query FROM pg_stat_activity
 WHERE state='idle in transaction' ORDER BY state_change;     -- culprits

Emergency command

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();

Pool-size formula (starting point)

default_pool_size ≈ (CPU cores × 2) + number of disk spindles

For most OLTP workloads, 20–50 per database is enough. Surprisingly small.

Monitoring to prevent recurrence

• Collect pg_stat_activity count every 1 minute
• Alert threshold: warn when used connections / max_connections > 80%
• Separate alert on idle in transaction count (early detection of lock storms)

References: official docs

The primary source for the behavior, settings, and errors covered in this post is the official documentation below. Check there for version-specific options and exact behavior.

• PostgreSQL official documentation

FAQ

Q. Can I just raise max_connections to 1000? A. Not recommended. Each connection multiplies backend processes and work_mem, causing OOM and context-switching overhead. The standard approach is to keep it at 200–300 or below and absorb many clients with PgBouncer.

Q. Does pg_terminate_backend corrupt data? A. In-flight transactions are rolled back, so only uncommitted changes are lost. idle in transaction is relatively safe because work is already stalled, but when you kill an active session, check what query it is running first.

Q. Do I still need PgBouncer on RDS/Aurora? A. Run PgBouncer on a separate instance, or on AWS use managed RDS Proxy. Managed DBs have a fixed max_connections ceiling per instance class, so a pooler is effectively mandatory in environments like Lambda or K8s where connections explode.