PostgreSQL 'Sorry, Too Many Clients Already': Fix the Connection Math

Last Monday a service started throwing FATAL: sorry, too many clients already about forty times in a row, then went quiet. CPU on the Postgres box sat at 9%. Nothing had deployed that morning. The database was healthy and refusing to talk to us, which is my least favorite combination.

I wrote about MySQL's version of this problem a couple of weeks ago. Postgres fails the same way in production, but the details are different, and the details are what bite you.

Every connection is a process

MySQL keeps connections as threads inside one process. Postgres forks a full operating system process for every connection. Each one holds a few megabytes at rest, and more the moment it runs a sort or a hash join with its own work_mem. Past a few hundred active backends, the CPUs spend their time context-switching and fighting over locks instead of running your queries.

So a 16-core server can genuinely run about 16 queries at once. Giving it 800 connections does not give you 800 concurrent queries. It gives you 800 processes standing in line inside the kernel.

That's why the first instinct, raising max_connections, usually makes things worse. The default of 100 looks small until you understand what each slot costs. And if you do change it, note that it needs a restart, not just a reload. I learned that during the incident instead of before it.

Find out who is holding the slots

SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3, 4
ORDER BY count(*) DESC;

In our case one application_name owned most of the 100 slots, nearly all in state idle. The app had opened them and never let go. That's the whole diagnosis in one query: you don't need more connections, you need fewer idle ones.

The multiplication

Most frameworks keep a pool per process, and the totals multiply quietly. Twelve pods, four worker processes each, a pool of 20 per worker. That's 960 connections against a max_connections of 100, and nobody ever wrote those three numbers side by side. Every autoscaling event and every cron worker makes it worse. Serverless is the worst of all, since each concurrent invocation can want its own connection.

Do the multiplication on paper before you touch a single setting. It takes two minutes and it ends the argument.

Put PgBouncer in front

The real fix is a pooler. PgBouncer takes thousands of cheap client connections and funnels them through a small set of real Postgres backends:

; pgbouncer.ini
[databases]
app = host=10.0.0.5 port=5432 dbname=app

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 30

With pool_mode = transaction, a server connection is lent to a client for the length of one transaction, then handed back. Five thousand app connections can share thirty real backends. Point your app at port 6432 and the pressure disappears.

There is one honest catch. In transaction mode, consecutive transactions from the same client may run on different backends, so anything that depends on session state breaks. That means SET without LOCAL, session-level advisory locks, LISTEN/NOTIFY, and temp tables that outlive a transaction. Check whether your app actually uses any of those before you flip the mode. Prepared statements used to be on that list too, but PgBouncer 1.21 and newer support them through max_prepared_statements, and most modern drivers work fine now.

If you're on a managed platform, check what it ships before installing anything. Supabase has Supavisor, Neon has its own pooler, RDS Proxy covers AWS. Half the incidents I read about online are people running direct connections on a platform that already gave them a pooler URL.

Size it, then set the timeouts

For the server-side pool, a decent starting point is (cores x 2) + effective disk spindles. On a 16-core box with SSDs, that's roughly 30 to 40 active connections. Benchmark from there instead of guessing higher. Keep max_connections a bit above the pooler's total and leave superuser_reserved_connections alone so you can still log in to fix things when it all goes wrong. Nothing is funnier, in a grim way, than an exhausted server you can't even psql into.

Then close the leak at the source with timeouts:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
ALTER SYSTEM SET idle_session_timeout = '15min';  -- PostgreSQL 14+
SELECT pg_reload_conf();

The first one has existed since 9.6 and kills exactly the sessions that cause lock pileups. Be careful with idle_session_timeout if clients hold long-lived pools directly; their drivers need to handle the reconnect.

The MySQL post ended with the same lesson and I'll keep repeating it until it sticks: count before you scale. I keep the pg_stat_activity query and my pgbouncer.ini template saved in Snippet Ark now, so the next incident starts with a paste instead of a frantic search.