🚨 PostgreSQL Error Guide: 'too many clients already' Connection Limit Exhausted

Overview

PostgreSQL refuses a new connection when the number of established backends has reached max_connections (minus the slots held back for superusers). Every client connection consumes a backend process and a slot; once they are all taken, the server rejects further logins immediately, before authentication even completes for non-reserved roles.

You will see this in the client output and the PostgreSQL log:

FATAL:  sorry, too many clients already

It occurs the moment a connection attempt arrives and no free slot exists. This is almost always a symptom of connections piling up faster than they are released — idle sessions, sessions stuck idle in transaction, an application that leaks connections, or simply more concurrent clients than max_connections allows without a pooler in front.

Symptoms

• New connections fail instantly with FATAL: sorry, too many clients already.
• Existing connections keep working; only new logins are rejected.
• The count of rows in pg_stat_activity sits at or just below max_connections.
• Application logs show connection-pool acquisition timeouts cascading.

SELECT count(*) AS total,
       current_setting('max_connections')::int AS max_conn
FROM pg_stat_activity;

 total | max_conn
-------+----------
   100 |      100
(1 row)

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

        state        | count
---------------------+-------
 idle                |    61
 idle in transaction |    27
 active              |    11
                     |     1
(4 rows)

Common Root Causes

1. max_connections is simply too low for the workload

If the application legitimately needs more concurrent sessions than max_connections allows, the limit is reached under normal load.

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend';

 max_connections
-----------------
 100

 count
-------
    99

Sitting at 99 of 100 with a reserved slot means the very next client is rejected. Raising max_connections costs roughly 5-10 MB of shared memory per slot, so a pooler is usually the better answer.

2. Idle connections piling up (no pooling, long-lived clients)

Application instances that open a connection and never close it accumulate idle backends that hold slots without doing work.

SELECT datname, usename, client_addr, count(*)
FROM pg_stat_activity
WHERE state = 'idle'
GROUP BY datname, usename, client_addr
ORDER BY count(*) DESC;

 datname  | usename | client_addr  | count
----------+---------+--------------+-------
 appdb    | appuser | 10.0.4.21    |    34
 appdb    | appuser | 10.0.4.22    |    27
(2 rows)

Dozens of idle sessions from a handful of app hosts means a client-side pool that opens too many connections and keeps them parked.

3. Sessions stuck “idle in transaction”

A transaction left open (a BEGIN with no matching COMMIT/ROLLBACK) holds its slot and its locks indefinitely. These are worse than plain idle connections because they also block vacuum and other writers.

SELECT pid, usename, state,
       now() - state_change AS idle_for,
       left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change;

  pid  | usename |        state        |    idle_for     |               last_query
-------+---------+---------------------+-----------------+----------------------------------------
 24417 | appuser | idle in transaction | 02:14:09.331    | SELECT id FROM orders WHERE user_id = $1
 24502 | appuser | idle in transaction | 01:58:44.117    | UPDATE accounts SET balance = balance -
(2 rows)

Multi-hour idle in transaction sessions are leaked transactions, usually an application that forgot to commit on an error path.

4. No connection pooler (PgBouncer) in front of PostgreSQL

When many short-lived clients each open their own backend, the slot count spikes far above what a small pool would need. PgBouncer in transaction mode multiplexes hundreds of clients onto a handful of server connections.

ss -tnp 'sport = :5432' | grep -c ESTAB
ps -ef | grep -c '[p]gbouncer'

98
0

98 direct connections on 5432 and zero PgBouncer processes confirms clients are talking to Postgres directly with no multiplexing.

5. Per-user or per-database connection limits

A role or database can have its own CONNECTION LIMIT lower than max_connections. The same too many clients error appears once that smaller limit is hit, even though the cluster has free slots.

SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolconnlimit >= 0;

SELECT datname, datconnlimit
FROM pg_database
WHERE datconnlimit >= 0;

 rolname  | rolconnlimit
----------+--------------
 appuser  |           20

 datname  | datconnlimit
----------+--------------
 appdb    |           40

appuser is capped at 20 connections; the 21st login from that role fails regardless of cluster headroom.

6. superuser_reserved_connections masking the real ceiling

PostgreSQL holds back superuser_reserved_connections slots so an admin can always log in. Non-superusers effectively cap out at max_connections - superuser_reserved_connections, which surprises people who assume all slots are usable.

SHOW max_connections;
SHOW superuser_reserved_connections;

 max_connections
-----------------
 100

 superuser_reserved_connections
--------------------------------
 3

Regular roles are rejected at 97 active connections, not 100 — the last 3 are reserved for superusers only.

Diagnostic Workflow

Step 1: Confirm you are actually at the limit

SELECT count(*) AS total,
       current_setting('max_connections')::int AS max_conn,
       current_setting('superuser_reserved_connections')::int AS reserved
FROM pg_stat_activity;

If total is at max_conn - reserved for non-superusers, the ceiling is the cause. Connect as a superuser (which can use a reserved slot) to run the rest of the diagnosis.

Step 2: Break the connections down by state

SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count(*) DESC;

A large idle or idle in transaction bucket points at leaks/pooling; a large active bucket points at genuine load.

Step 3: Attribute connections to a source

SELECT datname, usename, client_addr, application_name, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename, client_addr, application_name
ORDER BY count(*) DESC
LIMIT 10;

This tells you which host, user, and app is hoarding slots so you can fix the right pool.

Step 4: Find and clear the worst offenders

-- Cancel queries idle in transaction for over 10 minutes
SELECT pid,
       pg_terminate_backend(pid),
       now() - state_change AS idle_for
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes';

Terminating leaked idle in transaction backends releases slots immediately. Prefer pg_cancel_backend(pid) first if a query is merely active; use pg_terminate_backend(pid) to drop the whole session.

Step 5: Check role/database limits and reserved slots

SELECT rolname, rolconnlimit FROM pg_roles WHERE rolconnlimit >= 0;
SELECT datname, datconnlimit FROM pg_database WHERE datconnlimit >= 0;
SHOW superuser_reserved_connections;

If the cluster has free slots but a specific role/db is full, the per-object CONNECTION LIMIT is the real cap, not max_connections.

Example Root Cause Analysis

A checkout service starts throwing FATAL: sorry, too many clients already during a traffic spike, and the on-call engineer cannot even psql in as appuser.

Connecting as the postgres superuser (which can use a reserved slot), the state breakdown is telling:

SELECT state, count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend' GROUP BY state;

        state        | count
---------------------+-------
 idle in transaction |    58
 idle                |    31
 active              |     8

Fifty-eight sessions stuck idle in transaction is the smoking gun — that is not load, it is leaked transactions. Drilling into the source:

SELECT client_addr, left(query, 50) AS last_query, count(*)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
GROUP BY client_addr, last_query
ORDER BY count(*) DESC;

 client_addr |              last_query              | count
-------------+--------------------------------------+-------
 10.0.4.30   | UPDATE inventory SET reserved = rese | 58

All 58 are the same inventory-reservation UPDATE from one app host. A recent deploy added an early return after the UPDATE that skipped COMMIT on a validation failure, leaving the transaction open until the connection was reused or timed out.

Fix: clear the stuck sessions to restore service, then set a guardrail so a leaked transaction self-terminates:

-- Immediate relief
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes';

-- Guardrail: kill transactions idle longer than 60s
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

After the deploy is rolled back to commit on the error path, the idle in transaction count stays near zero and connections free up normally.

Prevention Best Practices

• Put a connection pooler (PgBouncer in transaction mode) in front of PostgreSQL so hundreds of clients share a small, bounded server pool instead of each opening a backend.
• Set idle_in_transaction_session_timeout (and a sane statement_timeout) so leaked transactions and runaway queries cannot park slots forever.
• Size application connection pools deliberately: total app pool size across all instances must stay below max_connections - superuser_reserved_connections, with headroom for migrations and admin sessions.
• Use per-role CONNECTION LIMIT to stop a single noisy service from starving the rest of the cluster.
• Always leave superuser_reserved_connections headroom so an admin can log in to fix a saturated server.
• Alert on pg_stat_activity count approaching max_connections and on any session idle in transaction for more than a few minutes — the free incident assistant can turn a saturated pg_stat_activity snapshot into the likely leak source.

Quick Command Reference

-- Are we at the limit?
SELECT count(*) AS total,
       current_setting('max_connections')::int AS max_conn,
       current_setting('superuser_reserved_connections')::int AS reserved
FROM pg_stat_activity;

-- Connections by state
SELECT state, count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend' GROUP BY state ORDER BY count(*) DESC;

-- Who is holding the slots?
SELECT datname, usename, client_addr, application_name, count(*)
FROM pg_stat_activity WHERE backend_type = 'client backend'
GROUP BY 1,2,3,4 ORDER BY count(*) DESC LIMIT 10;

-- Find leaked transactions
SELECT pid, usename, now() - state_change AS idle_for, left(query, 60)
FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY state_change;

-- Reclaim slots from stuck sessions
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - state_change > interval '10 minutes';

-- Per-role / per-db limits and reserved slots
SELECT rolname, rolconnlimit FROM pg_roles WHERE rolconnlimit >= 0;
SELECT datname, datconnlimit FROM pg_database WHERE datconnlimit >= 0;
SHOW superuser_reserved_connections;

-- Guardrail
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

Conclusion

FATAL: sorry, too many clients already means every usable connection slot is taken and PostgreSQL is rejecting new logins. The usual root causes:

1. max_connections is genuinely too low for the concurrent workload.
2. Idle connections pile up because clients open backends and never close them.
3. Sessions stuck idle in transaction hold slots (and locks) indefinitely.
4. No PgBouncer pooler, so every short-lived client opens its own backend.
5. A per-role or per-database CONNECTION LIMIT caps below the cluster total.
6. superuser_reserved_connections lowers the effective ceiling for regular roles.

Start by breaking pg_stat_activity down by state and source: if it is leaks, add a pooler and an idle_in_transaction_session_timeout; if it is real load, raise the limit behind a pooler rather than piling backends onto the server. More PostgreSQL troubleshooting lives in the Postgres guides.