PostgreSQL 'too many clients already': fixing connection exhaustion

#The error

FATAL: sorry, too many clients already
FATAL: remaining connection slots are reserved for non-replication superuser connections

You may need a reserved superuser slot to log in at all (superuser_reserved_connections).

#1. Who holds the connections?

SELECT usename, application_name, client_addr, state, COUNT(*)
FROM pg_stat_activity
GROUP BY 1,2,3,4
ORDER BY 5 DESC;

SHOW max_connections;

#2. Read the states

#3. Immediate relief

-- Sessions idle in transaction for more than 10 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND state_change < now() - interval '10 minutes';

Then set timeouts so the database cleans up by itself:

ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '5min';
ALTER DATABASE appdb SET idle_session_timeout = '30min';        -- PG 14+, careful with pools

#4. The real fix: pooling

Each PostgreSQL connection is a process with its own memory. Hundreds of mostly idle connections waste RAM, and raising max_connections to 2,000 makes things worse.

• PgBouncer in transaction pool mode lets thousands of client connections share a few dozen server connections.
• Managed options: RDS Proxy, the built-in PgBouncer on Azure Flexible Server.

; pgbouncer.ini (excerpt)
[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 40

Note

Transaction pooling breaks session features: session-level SET, advisory locks held across transactions, LISTEN/NOTIFY, and (before PgBouncer 1.21) named prepared statements. Check the app before switching.

#5. Size application pools

Total connections = app instances × pool size per instance. 30 pods × 20 connections = 600, which is often more than the database allows. Set pool sizes from the database limit downwards, not from the app upwards.

A good starting point for active connections is about 2–4 × the number of CPU cores on the database server.

Tip

Alert when connections reach 80% of max_connections, and graph them by application_name. Ask each team to set application_name in their connection strings; it makes this investigation take seconds.