Skip to main content
DevOps Insightsguides & answers

PostgreSQL · troubleshooting

FATAL: sorry, too many clients already

PostgreSQL has a fixed number of connection slots and every one is taken. Which of two messages you got matters: one means your application has run out while the database still keeps a few back for administrators, the other means the database is completely full. Raising the limit is the last fix to reach for, not the first.

Reproduced on PostgreSQL 16, . Where the text says “reproduced”, the behaviour was run on that version, not taken from documentation.

First published

New to this? Read what is actually happening first, then come back to the steps.

On this page · 9 sections

Before you start

  • A way to log in as a superuser, such as postgres. Once ordinary roles are refused, the last slots are kept for superusers, so that is how you get in to look around.
  • To see what other sessions are doing in pg_stat_activity: a superuser, or a role with privileges of pg_read_all_stats. Other roles see many columns as null for sessions that are not theirs.
  • To end sessions with pg_terminate_backend: a superuser, membership of the role that owns the session, or privileges of pg_signal_backend. Only a superuser can end a superuser's session.
  • The server's major version, from select version();. reserved_connections and the current wording of the reserved-slot message are new in PostgreSQL 16.
  • The application's pool size and how many copies of it run, from its configuration or deploy tooling, and the right to restart PostgreSQL if max_connections has to change, because it is only read at server start.

Run this first

step 1 of 4
psql -c "select state, count(*) from pg_stat_activity where backend_type = 'client backend' group by state order by 2 desc;"

Look for Which state dominates

The shape of the problem in one query. Mostly active means real load; mostly idle means pools or leaks; idle in transaction means stuck code.

Work out which cause you have

A few questions to narrow the list. Every answer ends in a command that confirms or rules the cause out — this cannot see your cluster, so nothing here is a certainty until you have checked.

Narrow it down

0 answered · nothing is sent anywhere

Which message are applications getting?

Cause space

4 of 4 still possible

  • Every application instance brings its own poolCommon
  • Sessions are stuck idle in a transactionCommon
  • The application is leaking connectionsOccasional
  • The workload really needs more slots than are configuredOccasional

Nothing ruled out yet. Answer the question above and the branches your answer eliminates will strike through here.

Or diagnose it manually

In this order. The first command usually contains the whole answer.

  1. Step 1

    psql -c "select state, count(*) from pg_stat_activity where backend_type = 'client backend' group by state order by 2 desc;"

    The shape of the problem in one query. Mostly active means real load; mostly idle means pools or leaks; idle in transaction means stuck code.

    Look for Which state dominates

  2. Step 2

    psql -c "select usename, application_name, client_addr, count(*) from pg_stat_activity where backend_type = 'client backend' group by 1, 2, 3 order by 4 desc limit 15;"

    Names who is holding the slots. One application or one address dominating usually ends the investigation.

    Look for A client or role holding far more connections than it should

  3. Step 3

    psql -c "select pid, now() - xact_start as open_for, left(query, 60) from pg_stat_activity where state like 'idle in transaction%' order by xact_start limit 10;"

    Stuck transactions are the cause that also hurts the database itself, through held locks and blocked vacuuming.

    Look for Anything open for longer than your longest legitimate transaction

  4. Step 4

    psql -c "select name, setting from pg_settings where name in ('max_connections', 'superuser_reserved_connections', 'reserved_connections');"

    The arithmetic every fix depends on. An ordinary role can use max_connections minus the reserved slots, not the whole number.

    Look for The real ceiling your applications are sharing

Every cause, and how to fix it

Ordered by how often each one turns out to be the answer.

Every application instance brings its own pool

Common

A connection pool is per process. Run more replicas, add a worker deployment, start a cron job that opens its own pool, and each one claims its full pool size at startup. The total is replicas multiplied by pool size, and it grows with every scale-out — so this tends to appear after a deploy or an autoscaling event rather than under load.

Confirm

psql -c "select usename, application_name, client_addr, count(*) from pg_stat_activity where backend_type = 'client backend' group by 1, 2, 3 order by 4 desc;"

Many addresses each holding a similar, round number of connections — the pool size, repeated once per instance

Fix

  • Size pools so that instances × pool size stays below max_connections minus superuser_reserved_connections and, on PostgreSQL 16, reserved_connections, with headroom for migrations, consoles and the next scale-out.
  • Put a connection pooler in front of the database so many application connections share a small number of server connections. This is the fix that survives scaling; resizing pools only moves the ceiling.
  • Give background workers and scheduled jobs their own small pools rather than the web tier's default.

Sessions are stuck idle in a transaction

Common

Application code begins a transaction, then waits on something else — an HTTP call, user input, an exception path that never commits or rolls back. The connection sits in idle in transaction, holding a slot and its locks indefinitely. Because the pool believes the connection is in use, it opens another, and the count climbs.

Confirm

psql -c "select pid, usename, now() - xact_start as open_for, left(query, 60) as last_query from pg_stat_activity where state like 'idle in transaction%' order by xact_start;"

Sessions open for minutes or hours. last_query is the statement that ran last, which usually points straight at the code path that never finished

Fix

  • Fix the code path: commit or roll back in a finally block, and never hold a transaction open across a network call.
  • Set idle_in_transaction_session_timeout so PostgreSQL ends these sessions itself. It defaults to 0, which disables it.
  • To recover now, end the worst offenders with pg_terminate_backend — their transactions roll back, so confirm what they were doing first.
psql -c "select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle in transaction' and now() - xact_start > interval '10 minutes';"

Rolls back each session's open transaction. Adjust the interval to something longer than any legitimate transaction in your application.

The application is leaking connections

Occasional

Connections are taken from the pool and never returned — an early return that skips the release, an error path without cleanup, a client created per request instead of shared. They show as plain idle, which is cheap for the database, but they still occupy slots, so the count only rises until a restart resets it.

Confirm

psql -c "select state, count(*), max(now() - state_change) as longest from pg_stat_activity where backend_type = 'client backend' group by state;"

A large idle count that grows between restarts and has connections idle for a long time, while active stays small

Fix

  • Find the path that does not release. A client created inside a request handler, rather than once per process, is the most frequent one.
  • Set a maximum lifetime and an idle timeout on the application's pool, so a leaked connection is eventually reclaimed by the pool even before the bug is fixed.
  • Do not reach for idle_session_timeout as the fix if connections go through a pooler — the documentation warns that such a layer may not react well to unexpected connection closure.

The workload really needs more slots than are configured

Occasional

Sometimes the arithmetic is honest: many services, each needing a modest pool, and a max_connections set low for a small instance. This is the only cause where raising the limit is the right answer — and it still costs shared memory and needs a restart.

Confirm

psql -c "select name, setting from pg_settings where name in ('max_connections', 'superuser_reserved_connections', 'reserved_connections');"

The configured ceiling, compared with the sum of pool sizes the applications are actually configured for

Fix

  • Rule out the other three first. A limit raised to hide a leak or a stuck transaction just takes longer to hit.
  • If the workload really needs it, raise max_connections and restart, and check the instance has the memory for it.
  • Usually a pooler is the better spend: it lets the same max_connections serve far more application connections.

Understanding it properly

Skip this if you are mid-incident — the working part of the page is above. Worth reading afterwards, because understanding the mechanism is what stops the next one.

In plain terms

PostgreSQL accepts only a set number of connections at the same time, and this error means every one of them is in use. The exact wording tells you whether the database is completely full or is keeping its last few connections back for administrators. Either way, find out who is holding the connections before you change any settings.

Both refusals, reproduced

Reproduced with PostgreSQL 16.15 on a throwaway local cluster that listened only on 127.0.0.1, port 5499, started with max_connections=5 and superuser_reserved_connections=1. reserved_connections was left at its default, 0. PGHOST and PGPORT pointed at the cluster and psql ran with -X, so no local startup file applied. Four sessions of an ordinary role, app, were already open, each running select pg_sleep(60). The superuser session stayed open while the last command ran. The server address in the two error lines is replaced with <host>; nothing else was changed.

Command

psql -U app -d postgres -c "select 1"
psql -U postgres -d postgres    # stays open; the next three lines run inside it
SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend';
psql -U postgres -d postgres -c "select 1"    # while that session is still open

Output

psql: error: connection to server at "<host>", port 5499 failed: FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute
 max_connections 
-----------------
 5
(1 row)

 count 
-------
    10
(1 row)

 count 
-------
     5
(1 row)

psql: error: connection to server at "<host>", port 5499 failed: FATAL:  sorry, too many clients already

How to read it

  • The first refusal went to app, an ordinary role, with four of the five slots in use. The last slot was reserved for superusers, so the message names the SUPERUSER attribute: the server still had room for an administrator.
  • SELECT count(*) FROM pg_stat_activity returned 10 on a server whose limit is 5. The view has one row per server process, and five of them were background processes such as the checkpointer. Counting only client backend rows gave 5, the number that matches the limit.
  • The second refusal went to the superuser, after its own session had taken the reserved slot. sorry, too many clients already means no slot is left for anyone.
  • With log_error_verbosity = verbose, the server log recorded SQLSTATE 53300 for both refusals, so code that checks the error code catches both wordings.
  • PostgreSQL 15 and earlier word the first refusal as remaining connection slots are reserved for non-replication superuser connections. On 16 with reserved_connections above 0, roles without pg_use_reserved_connections get a third wording before either of these: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role.

Words used on this page

max_connections
The most connections the server allows at once. The documentation gives the default as typically 100, and it can only be set at server start.
superuser_reserved_connections
Slots kept back for superusers out of max_connections, 3 by default. When only these are free, ordinary roles are refused.
reserved_connections
New in PostgreSQL 16: further slots kept for roles with privileges of pg_use_reserved_connections. The default is 0, so it changes nothing unless someone sets it.
pg_stat_activity
A view with one row per server process. That includes background processes such as the checkpointer, so filter on backend_type = 'client backend' to count connections.
client backend
The backend_type of a process serving a client's connection. In the reproduction below, the server stopped accepting connections when five of these were open with max_connections at 5.
idle in transaction
A session that began a transaction and is waiting for the client's next command. It keeps its slot and its locks, and stops vacuum removing recently-dead rows.
SQLSTATE 53300
The error code for both refusals, too_many_connections, in class 53, Insufficient Resources. Match on this code in application code rather than on the message text, which changed in PostgreSQL 16.
Connection pool
A set of connections an application keeps open and reuses. Each running copy of the application has its own, so the totals add up across copies.
What is actually happening

Every client connection to PostgreSQL occupies a slot, and the number of slots is max_connections. The documentation gives its default as typically 100, notes it can only be set at server start, and is explicit that it is not free: PostgreSQL sizes certain resources, including shared memory, directly from it. When the slots run out, new connections are refused with SQLSTATE 53300, too_many_connections, in the class the documentation calls Insufficient Resources.

With the default settings there are two different refusals, and telling them apart is most of the diagnosis. On PostgreSQL 16, setting reserved_connections above 0 adds a third, which turns away roles without pg_use_reserved_connections first: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role. A few slots are held back for administrators — superuser_reserved_connections, default three. Once only those reserved slots remain, an ordinary role is turned away with FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute, while a superuser can still get in. Only when every slot is gone does anyone, superuser included, get FATAL: sorry, too many clients already. Both wordings were reproduced on PostgreSQL 16 for this page; versions before 16 phrase the first one differently, so match on the meaning rather than the exact string.

That makes the first message good news of a kind. It means the database is protecting a way in for you to investigate, and your application is the thing that is full. Connect as a superuser and look before changing anything.

The usual cause is not traffic. It is how many connections the application holds open, which is decided by its connection pool and by how many copies of the application are running. Ten replicas with a pool of twenty is two hundred connections whether or not anybody is using the site, and scaling the application out multiplies the number without any change to the database at all.

The other common cause is connections that are open but useless. A session that began a transaction and then stopped sending queries shows as idle in transaction. It holds its slot, keeps any locks it took, and — the documentation notes — an open transaction also prevents vacuuming away recently-dead rows, so it degrades the database while it sits there. A plain idle connection outside a transaction is far cheaper: the documentation says it imposes no large costs on the server.

How to tell this is your problem
WhereWhat you see
Application logsFATAL: sorry, too many clients already, or FATAL: remaining connection slots are reserved for ...
Driver error codeSQLSTATE 53300 (too_many_connections)
select count(*) from pg_stat_activity where backend_type = 'client backend'A total at or near max_connections
pg_stat_activity grouped by stateLarge numbers in idle or idle in transaction, few in active

How to know it is actually fixed

  • The client backend count settles well below max_connections minus the reserved slots, under normal load and after a scale-out.
  • No session stays idle in transaction for longer than your longest legitimate transaction.
  • Applications reconnect cleanly and the FATAL messages stop appearing in their logs.
  • Scaling the application up by one step does not push the total near the ceiling.

Stopping it happening again

  • Do the multiplication before scaling: instances × pool size, plus workers and jobs, must fit inside max_connections minus superuser_reserved_connections and, on PostgreSQL 16, reserved_connections, with room to spare.
  • Put a connection pooler between applications and PostgreSQL once more than a handful of services share a database.
  • Set idle_in_transaction_session_timeout. It is off by default, and stuck transactions cost slots, locks and vacuum progress all at once.
  • Alert on connection count as a fraction of the ceiling. The FATAL message is the end of the story; the count creeping upward is the start of it.
  • Keep the reserved superuser slots. They are what let you connect and diagnose while the application cannot.

Security considerations

  • Do not let applications log in as a superuser. The reserved slots only keep a way in for you if the application cannot use them: in the reproduction, a superuser session took the last slot and the next superuser was refused like everyone else.
  • On PostgreSQL 16, grant pg_use_reserved_connections only to administration and monitoring roles. Any role with it can use the reserved_connections slots, so granting it to an application role gives that headroom away.
  • To let on-call engineers end stuck sessions without full superuser rights, grant pg_signal_backend. It allows pg_terminate_backend on other roles' sessions, but only a superuser can end a superuser's session.
  • The query column in pg_stat_activity holds each session's latest SQL text, which can include literal values such as personal data or a password set with ALTER ROLE. Redact it before you paste diagnostic output into a ticket, and give monitoring roles pg_read_all_stats rather than superuser.
  • Ending a session with pg_terminate_backend rolls back its open transaction. In the reproduction, a row inserted by a terminated idle-in-transaction session was gone afterwards, so check with the owner before you discard that work.

Sources

Behaviour described here is drawn from official documentation. Where a figure could not be confirmed on an official page it is attributed in the text rather than stated as canonical.

Did this get you to an answer?

No text box on purpose — please do not paste production logs anywhere

Something wrong or out of date? Report it

Where to go next