PostgreSQL problems

Sorry, Too Many Clients Already: Who Holds the Connections, and the Fix

22 November 2026

PostgreSQL has a fixed number of connections, and when the last one is taken every new login fails. The limit itself is rarely the problem. Something holds connections it does not use, or sessions pile up behind a lock. Two queries tell which, and the answer decides the fix.

What PostgreSQL says

How close the limit is

SELECT count(*)                                AS used,
       current_setting('max_connections')::int AS max_conn,
       current_setting('max_connections')::int
       - current_setting(
           'superuser_reserved_connections')::int
       - current_setting('reserved_connections')::int
                                               AS usable
FROM   pg_stat_activity
WHERE  backend_type = 'client backend';

usable is what ordinary roles get: max_connections, 100 by default, less the slots kept for superusers, 3 by default, and those kept for members of pg_use_reserved_connections. Limits of single roles and databases:

SELECT 'role' AS kind, rolname AS name,
       rolconnlimit AS conn_limit
FROM   pg_roles    WHERE rolconnlimit <> -1
UNION  ALL
SELECT 'database', datname, datconnlimit
FROM   pg_database WHERE datconnlimit <> -1;

Who holds the connections

SELECT usename, application_name, client_addr, state,
       count(*)                  AS connections,
       max(now() - state_change) AS longest_in_state
FROM   pg_stat_activity
WHERE  backend_type = 'client backend'
GROUP  BY usename, application_name, client_addr, state
ORDER  BY connections DESC
LIMIT  15;
What the top rows showWhat it means
Many idle from one applicationA connection pool larger than the server can give, or several application servers that each bring a full pool. Idle for hours: connections that were opened and never closed.
Many idle in transactionCode that opens a transaction and does not end it. These also hold locks: see idle in transaction.
Many active, most of them waiting on a lockA pile-up. Every request waits behind one session, the application opens more connections to get through, and the limit is the last thing to fail, not the first.
Many active and workingReal load, or one statement that became slow and now overlaps with itself.

The fix, in order

  1. Get in. The reserved slots exist for this moment: connect as a superuser, or as a member of pg_use_reserved_connections.
  2. End the root of a pile-up, not the waiters. When the sessions wait on a lock, find the one they wait behind and end that one. The queue empties by itself.
  3. Free connections that do nothing.
    SELECT pg_terminate_backend(pid)
    FROM   pg_stat_activity
    WHERE  backend_type = 'client backend'
    AND    state = 'idle'
    AND    state_change < now() - interval '1 hour'
    AND    pid <> pg_backend_pid();
    A pool opens them again when it needs them.
  4. Size the pools. The pools of all application servers together must fit under usable, with room for the DBA and for monitoring.
  5. Put a pooler in front. PgBouncer in transaction mode lets thousands of clients share a few tens of server connections, because a client holds one only while its transaction runs.
  6. Bound idle sessions. ALTER ROLE app SET idle_session_timeout = '30min'; for roles that connect without a pooler, and idle_in_transaction_session_timeout for every role.
  7. Raise max_connections last. It needs a restart, every standby needs at least the same value, and more connections than the cores can serve make every query slower. A limit that is reached because connections leak is reached again at any value.

See it coming

The limit is reached at the end of a climb that takes minutes or hours. Radar Engine, the detection layer of Scops, follows the connections of every instance against the limit that ordinary roles can really use, with no agent on the server:

DetectorWhen it fires
Connection limitAgainst the usable limit: max_connections less the reserved slots. Watch from 80%. Warning from 90%, or from 80% and climbing with the limit 30 minutes away or less. Critical from 97%: new connections are refused.
Blocking pile-upWarning: at least 10 lock waiters behind one head, the longest waiting 3 minutes or more. Warning too: a head that is idle in a transaction, or a prepared transaction, holding 3 or more waiters for 5 minutes. Critical: at least 20 lock waiters that are at least half of the active sessions.

The finding says who holds the connections. An application behind a pool that always sits at 85% is that instance's usual level and stays quiet until it leaves it.

Questions

What does "sorry, too many clients already" mean in PostgreSQL?
Every connection allowed by max_connections is in use, so the server refuses the new one. Existing sessions keep working. It ends when connections are closed or terminated.

How do I see how many connections PostgreSQL uses?
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend', compared with SHOW max_connections. Grouping the same view by usename, application_name and state shows who holds them.

Should I raise max_connections?
Only when the connections are really used. Idle and leaked connections fill any limit, and many active sessions on few cores slow each other. A pooler such as PgBouncer serves more clients with fewer connections.

What is superuser_reserved_connections?
The number of connection slots, 3 by default, that only superusers may use. They let a DBA connect and repair the server when ordinary roles are refused.

Related problems

Sources