PostgreSQL FATAL: too many connections — real fix

This error isn't subtle. Postgres accepted all the connections it's willing to accept, and now it's telling everyone else to go away. The message shows up in your app logs as FATAL: sorry, too many clients already or FATAL: too many connections depending on version. Either way, the server is at the ceiling set by max_connections, which defaults to 100 on most builds.

What's actually happening here is simpler than people assume. Postgres forks a backend process per client connection. That's not a thread, it's a process, roughly 5-10 MB of RSS each under normal load. The max_connections limit exists because past a certain point the scheduler and shared memory buffers thrash. So the limit isn't arbitrary. Bumping it to 5000 doesn't fix anything, it just moves the failure to a worse place.

I've hit this in three recurring scenarios: a Rails app that grew its Puma workers without touching the pool size, a BI tool that opens a fresh connection per dashboard widget, and a batch job that leaks connections because a begin block never hits commit or rollback on the error path. All three look identical in the logs. You have to get inside the server to tell them apart.

Step 1 — Get back in (30 seconds)

You can't diagnose anything if you can't connect. Postgres reserves a small number of slots for superusers via superuser_reserved_connections (default 3). If you're a superuser, you can still get in even when regular users are locked out.

psql -U postgres -d yourdb -h 127.0.0.1

If that also fails, your superuser slots are exhausted. Restart the service to clear it. On systemd:

sudo systemctl restart postgresql

On a managed service like RDS or Cloud SQL, you don't have shell access. Use the console's reboot option. It's disruptive, so if you're in production and can survive 30 seconds of downtime, do it. If not, skip to step 3.

Once you're in, run this immediately:

SELECT pid, usename, application_name, client_addr, state,
       now() - state_change AS idle_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY state_change ASC
LIMIT 20;

That tells you who's actually working and who's just sitting there. A pile of idle rows belonging to one application_name means the pool is too big or leaking. A pile of idle in transaction means code opened a transaction and walked away without closing it.

Step 2 — Kill the offenders (5 minutes)

Don't kill everything. Kill the connections that are doing damage. Idle-in-transaction connections hold locks and block vacuum, so they're the priority.

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 pid <> pg_backend_pid() guard matters — without it you can kill yourself mid-query, which is annoying but harmless, and worse, you can kill the query that's about to commit something important. Filter by application_name or client_addr if you know which app is at fault. Killing a BI user's abandoned session is fine. Killing the checkout service's in-flight transaction is not.

If you're on Postgres 9.6 or newer, pg_terminate_backend is safe to call on any PID you own as superuser. On older versions you needed pg_cancel_backend first for some states. Everyone should be on 12+ at this point anyway — 9.6 went EOL in November 2021.

What you've done here is buy yourself time. The root cause is untouched. Which brings us to the actual fix.

Step 3 — Fix the real problem (15+ minutes)

You have two options and they're not equal. You can raise max_connections, or you can add a connection pooler. The right answer is almost always the pooler. Raising the limit is a band-aid that works until the next traffic spike, and it costs you RAM you'll want for shared buffers and the OS page cache.

Option A: Raise max_connections (only if you have headroom)

Check current usage against your RAM budget:

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;
SHOW shared_buffers;

If you're on a 16 GB box with shared_buffers = 4GB and 100 connections, going to 200 is survivable. On a 2 GB t3.small, it's not — you'll OOM the kernel and lose the whole server. The formula people quote is (RAM - shared_buffers) / 10MB, but that's optimistic. Use 15MB per connection if you have any query doing sorts or hash joins.

ALTER SYSTEM SET max_connections = 200;
-- requires restart, not just reload
sudo systemctl restart postgresql

ALTER SYSTEM writes to postgresql.auto.conf, which is read after postgresql.conf, so it wins. You can also edit postgresql.conf directly. Either way, a restart is required — this parameter can't be changed with pg_ctl reload.

Option B: Put PgBouncer in front (the real fix)

PgBouncer in transaction pooling mode multiplexes hundreds of client connections onto a handful of real Postgres backends. A typical web app with 200 Puma workers can run on 20 actual Postgres connections. This is what you actually want.

Install and configure. On Debian/Ubuntu:

sudo apt install pgbouncer

In /etc/pgbouncer/pgbouncer.ini:

[databases]
yourdb = host=127.0.0.1 port=5432 dbname=yourdb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
server_idle_timeout = 60

Point your app at port 6432 instead of 5432. The default_pool_size = 20 means Postgres sees at most 20 backends from this pool. Your app can open 1000 client connections and PgBouncer will queue them.

The reason transaction pooling works is that PgBouncer returns the server connection to the pool the moment the transaction ends, not when the client disconnects. That's why you can't use session-level features like LISTEN, advisory locks held across transactions, or SET outside a transaction in transaction mode. If your app relies on those, use pool_mode = session and accept a lower effective multiplexing ratio.

Kill the leaks at the source

Pooler or not, if your app leaks connections, add a statement timeout as a backstop:

ALTER DATABASE yourdb SET idle_in_transaction_session_timeout = '5min';
ALTER DATABASE yourdb SET statement_timeout = '30s';

Per-database settings beat per-role and avoid touching postgresql.conf. The idle_in_transaction_session_timeout setting is the single most useful one for this class of problem — it's been around since 9.6 and it kills the exact sessions that cause lock pileups.

Why this keeps happening

Almost every case I've debugged traces back to one of these:

Pool size × app instances > max_connections. Ten EC2 instances each with a 20-connection pool equals 200 connections, and nobody did the multiplication.
Connection leak on the error path. try { conn = pool.get(); ... } finally { conn.close(); } is correct. try { conn = pool.get(); ... } without the finally block leaks on every exception, and you notice three days later.
An ORM opening a connection per request without pooling. Older Django configs and some Node setups do this. The fix is a pooler, not a bigger max_connections.
Long-running reports on the same connection string as the OLTP app. Give analytics its own pooler with a small default_pool_size so a slow report can't starve checkout.

Monitor it before it bites again. The metric that matters is SELECT count(*) FROM pg_stat_activity sampled every 15 seconds, graphed against max_connections. When it crosses 80% for more than a minute, you want an alert — not a page at 3am when the app's already down.

One last thing: don't set max_connections from a script that runs on every deploy. I've seen that pattern in Helm charts and it silently resets the value to the chart's default, undoing the change you made during the incident. Put the real value in postgresql.conf or a managed parameter group and leave it alone.