FATAL: sorry, too many clients already

The short answer

FATAL: sorry, too many clients already
psql: error: connection to server failed

Postgres hit max_connections. Every slot is taken.

Do not immediately raise max_connections. Each Postgres connection is a separate OS process using several megabytes plus work_mem per sort. Going from 100 to 500 can turn a connection problem into a memory problem and an OOM kill.

Find out who is holding them first:

SELECT state, count(*), max(now() - state_change) AS oldest
FROM pg_stat_activity
GROUP BY state ORDER BY count DESC;

If most connections are idle in transaction, you have an application bug and no amount of tuning will fix it. If they are idle, your pool is oversized. If they are active, you genuinely need more capacity or a pooler.

Tested on PostgreSQL 16.3 and PgBouncer 1.23.

Why Postgres connections are expensive

Unlike MySQL's thread per connection model, Postgres forks a full OS process for every connection. That process has its own memory context, its own catalog cache, and its own plan cache.

Rough cost: 5 to 10MB of baseline memory per connection, plus up to work_mem for each sort or hash operation in a query, and a single query can have several. With work_mem at 64MB and a query doing three sorts, one connection can transiently use nearly 200MB.

Multiply that by 500 connections and the arithmetic stops working long before you run out of connection slots.

There is also a context switching cost. Hundreds of active processes on a machine with 16 cores means the scheduler spends real time moving between them, and throughput drops even though the connections are technically available.

The practical guidance most people converge on is that useful concurrency is roughly cores * 2 plus some allowance for I/O wait. For an 8 core database server, something like 20 to 40 genuinely active connections. Anything beyond that is queueing, and queueing in the database is worse than queueing in a pooler.

Finding the holder

SELECT pid, usename, application_name, client_addr, state,
       now() - state_change AS duration,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY state_change;

Read the state column carefully, because the three values mean completely different things.

active means a query is running right now. Many active connections with long durations means slow queries, and the fix is reading the query plans rather than adding connections.

idle means the connection is open with no transaction. This is normal for a pool. Hundreds of them means your pool is too large, often because each of twenty application instances opens a pool of twenty.

idle in transaction is the dangerous one. A transaction was opened and never committed or rolled back. The connection is held, and worse, the transaction holds locks and blocks vacuum from cleaning up rows newer than its snapshot. A long lived idle transaction causes table bloat that outlasts the incident.

SELECT pid, now() - xact_start AS txn_age, state, left(query,100)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Anything over a few minutes is a bug. The usual cause is an application that opens a transaction, makes an HTTP call or waits on something external, and holds the transaction open across it.

The arithmetic that catches people

The most common cause of this error is not a leak. It is multiplication.

20 application pods
× pool size 20 per pod
= 400 connections

Against max_connections = 100, that fails as soon as the fourth pod warms up. It works fine in staging with two pods and fails in production, which is why it tends to be found the hard way.

Serverless makes this worse. Each Lambda or edge function instance may open its own connection, and concurrency scales with traffic rather than with a fixed instance count. A traffic spike becomes a connection spike, at exactly the moment you can least afford one.

Autoscaling has the same shape: the response to load is more pods, each opening more connections, which makes the database slower, which increases latency, which triggers more scaling. That feedback loop is a genuinely nasty outage pattern.

Fixes, in order

1. Fix idle in transaction

If you have long lived idle transactions, that is the bug and everything else is a workaround.

Set a backstop so the database defends itself:

ALTER DATABASE mydb SET idle_in_transaction_session_timeout = '30s';
ALTER DATABASE mydb SET statement_timeout = '30s';

The first kills transactions left open. The second kills runaway queries. Both should be set on any production database, and neither is on by default.

In application code, the rule that prevents this: no external I/O inside a transaction. Fetch what you need first, then open the transaction, write, commit. Same rule that prevents deadlocks widening.

Check your ORM's transaction handling. Several will leave a transaction open if an exception escapes a with block in an unusual way, and connection pool wrappers occasionally return a connection to the pool without rolling back.

2. Right size your pool

Total connections should be instances × pool_size, and that should sit comfortably under max_connections with headroom for maintenance and for you to connect with psql during an incident.

For most web applications a pool of 5 to 10 per instance is plenty. The instinct to set it to 50 comes from thinking of the pool as capacity, when it is actually a queue depth. A larger pool does not make the database faster, it just lets more requests wait inside the database instead of in your application.

// node-postgres
const pool = new Pool({
  max: 8,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,   // fail fast rather than hang
});

Set connectionTimeoutMillis. Without it, a request waits indefinitely for a connection and your latency graph looks like a wall.

3. Put a pooler in front

For serverless, high instance counts, or anything where you cannot control connection growth, PgBouncer is the answer.

It maintains a small number of real Postgres connections and multiplexes many client connections onto them. Three modes:

Transaction mode is what you almost always want, and it is the reason PgBouncer is so effective. A thousand client connections can share twenty server connections, because most connections are idle between transactions.

The catch with transaction mode is that anything relying on session state breaks: prepared statements, SET at session level, advisory locks held across statements, LISTEN and NOTIFY, and temporary tables. Most ORMs have a flag for this. In node-postgres it is avoiding named prepared statements; in SQLAlchemy it is NullPool plus disabling prepared statement caching.

If you use a managed Postgres, check whether it already offers a pooler endpoint. Most do, and it is usually a different port on the same host.

4. Only then raise max_connections

If connections are genuinely active and your pooler is sized correctly, you may need more.

ALTER SYSTEM SET max_connections = 200;
-- requires a restart

Recalculate memory before you do. Worst case is roughly max_connections × (base + work_mem × sorts_per_query). If that exceeds available RAM, you have moved the failure rather than fixed it, and the new failure is an OOM kill with no diagnostic output.

Also raise superuser_reserved_connections so you can still get in when the pool is exhausted. The default of 3 is thin, and being locked out of your own database during an incident is a bad place to be.

Emergency response

When you are locked out right now:

-- connect as superuser, using a reserved slot
-- kill idle transactions older than 5 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes';

-- kill long running queries if you must
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '10 minutes'
  AND query NOT ILIKE '%pg_stat_activity%';

pg_cancel_backend is gentler and cancels the query while keeping the connection. pg_terminate_backend drops the connection entirely. Try cancel first.

Excluding your own query from the predicate matters, and forgetting it is a small embarrassment I have committed.

Monitoring

Three things worth graphing:

-- connection count against the limit
SELECT count(*)::float / current_setting('max_connections')::int AS pct_used
FROM pg_stat_activity;

-- oldest idle transaction
SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state = 'idle in transaction';

-- waiting on locks
SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Alert on the first at 80 percent, and on the second at anything over a minute. The second is the leading indicator: idle transactions accumulate before they exhaust the pool, so it gives you warning that the connection count does not.