FATAL: sorry, too many clients already - and why raising max_connections is wrong

Traffic doubles, the app starts throwing 500s, and the logs fill with one line:

FATAL:  sorry, too many clients already

The first search result says raise max_connections, restart Postgres, move on. It works. Load goes up again next month, you raise it again, and eventually the day arrives when the database server itself falls over, or the OOM killer takes the postmaster, and now you are not recovering in thirty seconds.

Here is the thing that advice never mentions: in PostgreSQL every connection is a separate operating system process, not a thread. Raising max_connections is not raising a counter, it is granting permission to fork hundreds more processes, each with its own memory. The limit is not the problem. It is the only thing standing between you and a much worse failure.

The exact error

FATAL:  sorry, too many clients already

Two spaces after the colon, which matters if you are grepping. What your application shows depends on the driver:

psycopg2.OperationalError: FATAL:  sorry, too many clients already
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
Error: sorry, too many clients already
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  sorry, too many clients already

A related one, which means the same wall from a different angle, is remaining connection slots are reserved for non-replication superuser connections. That is superuser_reserved_connections doing its job: it holds back a few slots, three by default, so you can still get in with psql to fix things.

Why this happens

Every connection is a process

When a client connects, the postmaster forks a backend process dedicated to that connection for its entire lifetime. That process has its own stack, its own catalogue caches, its own plan caches, and its own private memory allocations. Idle or busy, it exists. This is the design decision that everything else follows from, and it is why Postgres behaves differently from MySQL or SQL Server under high connection counts.

So max_connections = 500 means "be prepared to run 500 processes". Beyond the memory, the kernel has to schedule them, and Postgres has to scan shared data structures whose cost grows with the number of backends. Past a few hundred active connections you often see throughput go down as you add more, which is the opposite of what the person raising the limit expects.

work_mem is per operation, not per connection

The common phrasing is that each connection uses work_mem. It is worse than that. work_mem is the allowance for each sort, hash join or hash aggregate node in a plan. One complicated analytical query can allocate several multiples of it at once, and a parallel query multiplies it again per worker. The realistic worst case for total memory is not max_connections x work_mem, it is materially higher, and nobody computes it before raising the limit.

The arithmetic nobody does

This is the actual root cause in most incidents I have been called into. The application is configured, in several independent places, to open far more connections than the database permits, and each place looks reasonable in isolation.

Add a Celery deployment, a scheduled report, and a migration job, and the ceiling moves again. Autoscaling makes it non-deterministic: the configuration that works at three replicas fails at seven, at 11pm, when nobody is watching.

The fix

Step 1: See who is holding the connections

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

         state         | count
-----------------------+-------
 idle                  |    71
 idle in transaction   |    18
 active                |     8
                       |     3

Read this carefully, because the shape tells you which problem you have.

• Mostly idle: connection pools sized too large. Nothing is wrong with the database, the app is just hoarding. This is the common case and PgBouncer solves it.
• A pile of idle in transaction: a genuine leak. Code opened a transaction, did something slow that was not the database (an HTTP call to a payment gateway, say), and is holding a connection and its locks the whole time. This also blocks vacuum from cleaning up rows, so it degrades everything else.
• Mostly active and climbing: the database is genuinely saturated. Fix the queries, not the connection count.

To see the worst offenders by age:

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

Step 2: Buy yourself room right now

If you are in the incident, terminate the leaked sessions. This is a stopgap, not a fix:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '10 minutes'
  AND pid <> pg_backend_pid();

Expected output is one row per killed backend with t. Do not run this against state = 'active' without reading the queries first.

Step 3: Stop the leak from recurring, in the database

These are settable per role or in postgresql.conf, and they cost nothing:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET lock_timeout = '5s';

The first is the important one. A transaction left open by buggy application code is killed after 30 seconds instead of living until the next deploy. Set statement_timeout generously enough that legitimate queries survive, then lower it as you fix what it catches. If you run reports or migrations under a different role, give that role its own, longer values rather than raising everyone's.

Step 4: Put PgBouncer in front, in transaction mode

This is the real fix. In transaction pooling mode a client holds a server connection only for the duration of a transaction, then hands it back. Two hundred mostly-idle application connections map onto twenty busy database connections.

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

[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 = 2000
default_pool_size = 20
reserve_pool_size = 5
server_idle_timeout = 60

Then point the application at port 6432 instead of 5432 and, importantly, shrink the application pool. Leaving SQLAlchemy at pool_size=5, max_overflow=10 while adding PgBouncer just moves the queue; the point is that the app can now be generous with client connections because they are cheap, while the database sees few.

Transaction mode has real constraints you must check before adopting it. Session-scoped state does not survive between transactions, so SET at session level, LISTEN/NOTIFY, session advisory locks and WITH HOLD cursors will misbehave. Server-side prepared statements need explicit support in PgBouncer configuration, or disabling in your driver. If your application relies on any of those, use pool_mode = session and accept smaller gains, or fix the dependency.

Step 5: Only now consider raising max_connections

If after all that you still need more, raise it deliberately. It requires a restart because it sizes shared memory structures at startup:

SHOW max_connections;
ALTER SYSTEM SET max_connections = 200;

sudo systemctl restart postgresql

Before you do, know your per-backend cost. Measure it rather than trusting a number from a blog post: watch resident memory on a real workload with ps or pg_stat_activity joined against your monitoring, then multiply by the new ceiling and check it against free RAM with room left for the page cache. A database that has lost its page cache to backend processes is slow in a way that looks like a completely different problem.

Verification

SELECT count(*) AS total,
       (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn
FROM pg_stat_activity;

Under normal load, total should sit comfortably below the limit and stay flat rather than creeping. Then check PgBouncer is actually pooling rather than passing through, from its admin console:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c 'SHOW POOLS;'

You want cl_active much larger than sv_active. If they are equal, you are in session mode or your app opens a transaction and holds it, and you have gained nothing. Finally, confirm the timeouts took effect for the application role:

SELECT rolname, rolconfig FROM pg_roles WHERE rolname = 'app_user';

What people get wrong

Raising max_connections as the first move. It converts a clear, recoverable error into an unclear, unrecoverable one. The database stops refusing connections and starts swapping, or gets OOM-killed. "Too many clients already" is at least a diagnosis.

Setting max_connections to 1000 "to be safe". That is not safety, it is removing the safety. Postgres will accept all 1000 and then contend on shared structures and CPU, and your p99 latency triples while the dashboard shows plenty of headroom.

Adding PgBouncer without shrinking the app pool. Very common. The bottleneck moves from Postgres to PgBouncer's default_pool_size, the error changes to a client-side pool timeout, and you conclude PgBouncer did not help.

Blaming the pooler for a slow query. If the queries are slow, pooling makes it worse: transactions hold their server connection longer, so the pool drains. Find the slow query first. The same discipline I use for tracking down slow queries with the slow query log applies here, with pg_stat_statements as the Postgres equivalent.

Restarting the app every time it happens. It works, which is why it persists as a practice, and it means you never see the pattern that would tell you which code path leaks.

When it is still broken

• Connections climb even at idle. Something is opening and never closing. Look at application_name in pg_stat_activity to identify the service, then set application_name explicitly in every service's connection string so this is one query rather than an investigation.
• PgBouncer itself refuses connections. Raise max_client_conn, which governs how many clients PgBouncer accepts and is unrelated to Postgres's own limit. Also check the file descriptor limit for the pgbouncer process, since each client connection consumes one.
• Serverless or autoscaled workers. Every cold start opens a fresh connection and functions may exit without closing cleanly. A pooler is not optional in this architecture, it is a requirement, and it needs to live somewhere long-lived rather than inside the function.
• The count is correct but everything is still slow. Then connections were a symptom. Look for lock contention with pg_locks, for a long-running transaction blocking vacuum, or for a cache layer that stopped absorbing reads. A cache that has quietly stopped serving what it should pushes its entire read volume onto the database, and this error is often the first place it shows up.

The general shape of the fix is the same every time: the database is telling you the truth about a limit, and the right response is to need fewer connections rather than to permit more. Pool at the edge, keep transactions short, and let Postgres run a modest number of backends that are actually doing work.