PostgreSQL "FATAL: sorry, too many clients already": find who holds the connections

1. Get in

If you can’t connect at all, try as a superuser: PostgreSQL keeps superuser_reserved_connections slots for that, 3 by default.

sudo -u postgres psql

In a container:

docker exec -it db psql -U postgres

If those slots are gone too, usually because the application connects as a superuser, there’s no way in with SQL. Stop the application, or restart PostgreSQL as a last resort. And then stop using a superuser for the application.

2. See who holds them

The query in the TL;DR groups them. For the details of the oldest:

SELECT pid, usename, application_name, client_addr, state,
       now() - state_change AS in_state_for,
       left(query, 60) AS last_query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY state_change
LIMIT 20;

In state:

3. Free some now

End a specific connection, or everything idle for over 10 minutes from one application:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app'
  AND state = 'idle'
  AND state_change < now() - interval '10 minutes';

That’s safe for idle sessions, as a pool will reconnect. For idle in transaction, the open transaction rolls back. This buys you time, and only the next sections fix it.

4. Do the arithmetic on your pools

It’s the most common cause:

4 pods × 3 worker processes × 10 connections in each pool = 120 connections

That’s already over 100, before any traffic and before a deploy briefly doubles the pods. Add them up across every service using the database, including cron jobs and migrations.

Then set the pool sizes to fit. Most applications do fine with far fewer connections than they ask for. And check that connections go back: a request that exits early without closing leaks one each time.

5. Add limits so one client can’t take everything

ALTER ROLE app CONNECTION LIMIT 50;
ALTER ROLE reporting CONNECTION LIMIT 5;

And let the server clean up after buggy clients:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
ALTER SYSTEM SET idle_session_timeout = '30min';
SELECT pg_reload_conf();

idle_session_timeout is available since PostgreSQL 14. Set it carefully: a pool that doesn’t handle dropped connections will show errors, and you shouldn’t use it through PgBouncer.

6. Use a connection pooler

Each PostgreSQL connection is a separate process, so thousands of them cost memory and slow everything. PgBouncer sits in front, accepts thousands of clients and uses just a few real connections:

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

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

The application then connects to port 6432 instead of 5432. In transaction mode a client only holds a real connection during a transaction, which is what makes it so effective. The catch is that session features such as SET, advisory locks and LISTEN don’t carry across transactions, so check what your application uses.

Serverless functions and many short-lived clients are where this helps the most: they can exhaust the limit even at low traffic. Managed services often have their own pooler, such as RDS Proxy.

7. Raising the limit, if you need to

After all of that, you may still need more:

ALTER SYSTEM SET max_connections = 200;

It only applies after a restart, not a reload:

sudo systemctl restart postgresql

With Docker, it’s command: postgres -c max_connections=200.