Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

PostgreSQL "sorry, too many clients already": count connections before raising max_connections

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

FATAL: sorry, too many clients already means PostgreSQL has no free connection slot left for the new client. A related message, remaining connection slots are reserved for roles with the SUPERUSER attribute, appears as slots run short, when only the slots set aside for administrators are left. One cause is a client that holds many connections, many of them idle. Count who holds them first. Raising max_connections is a restart and a memory cost, and it does not fix a leak.

What the error means

In the PostgreSQL source (REL_17_STABLE and REL_19_STABLE), sorry, too many clients already is raised as a fatal error with the "too many connections" error code when a new backend cannot get a process slot. A related fatal error in the same source reads remaining connection slots are reserved for roles with the %s attribute, with SUPERUSER filled in, and a second form that names the pg_use_reserved_connections role.

The PostgreSQL documentation describes the limits:

  • max_connections "Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it". It can only be set at server start.
  • superuser_reserved_connections has a default of three. The documentation says: "Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections, new connections will be accepted only for superusers."
  • reserved_connections has a default of zero and reserves further slots for roles with privileges of pg_use_reserved_connections.

With the defaults, 100 − 3 = 97, so ordinary roles are refused once about 97 connections are active, and only the last three slots remain for superusers. Which sessions count toward the total is not spelled out in the PostgreSQL documentation, so compare the number you see in pg_stat_activity with the limit rather than assuming exactly which processes use a slot.

Raising the limit has a cost. The documentation says: "PostgreSQL sizes certain resources based directly on the value of max_connections. Increasing its value leads to higher allocation of those resources, including shared memory." A standby must have the same or a higher value than the primary.

Likely causes

Cause What you see How to confirm
Application pool larger than the server allows, or several applications each with their own pool Count near the limit, spread across several application_name values Group pg_stat_activity by application_name and user
Sessions left idle in transaction Many rows in that state, with an old state_change Filter on the state column
Connection leak in the application Count climbs until it hits the limit, then falls only after a restart of the application Watch the count over several minutes
Too many short-lived connections with no pooler Count spikes at busy times, many idle rows State and backend_start times in pg_stat_activity
max_connections set low for the workload Steady count at the limit with active queries Compare active sessions with the setting

Prove the cause before changing anything

All of these are read-only. Connect as a superuser; the reserved slots exist so that you can.

1. The limits.

SHOW max_connections;
SHOW superuser_reserved_connections;
SHOW reserved_connections;

2. The count of client sessions. The pg_stat_activity documentation lists client backend among the possible backend_type values.

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

3. Who holds them, and in what state.

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

The state column includes idle in transaction, which the documentation describes as "The backend is in a transaction, but is not currently executing a query." Many rows in that state point at an application that opens a transaction and does not finish it.

4. How long they have been idle.

SELECT pid, usename, application_name, state, state_change
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state = 'idle in transaction'
ORDER BY state_change;

The state_change column is the time the state last changed.

Fix

Fix the client first

Ask which application owns the sessions (application_name, user and database tell you). Reduce its pool size so that the sum of all pools stays below max_connections minus the reserved slots, and make it close transactions. For stuck sessions, the server can end them. The documentation for idle_in_transaction_session_timeout says it will "Terminate any session that has been idle (that is, waiting for a client query) within an open transaction for longer than the specified amount of time", and that zero (the default) disables it. Setting it ends sessions that the application may still expect to use, so treat it as a change that needs the application owner's agreement.

End specific sessions (only when needed)

Select the exact sessions with step 4 and note their pid. Disruptive step: pg_terminate_backend ends a session; the documentation says it "Terminates the session whose backend process has the specified process ID", and that only superusers can terminate superuser backends. The client loses its connection, and its work in that session is interrupted. It cannot be undone. Run it for one pid you have just selected:

SELECT pg_terminate_backend(12345);

Replace 12345 with the pid of the session you selected. Never run it over a whole query result that you have not read.

Use a pooler

A connection pooler sits between the clients and PostgreSQL. The PgBouncer configuration page documents pool_mode (session, transaction or statement), default_pool_size (default 20, "The maximum number of server connections to allow per user/database pair") and max_client_conn (default 100). In transaction mode the page says "clients must not use any session-based features", so test the application first.

Raise max_connections

Only do this after the checks above show that the load is legitimate and the host has memory for it. Save first. Print the current value and find the file:

SHOW max_connections;
SHOW config_file;

Copy the file named by config_file to a dated copy, for example cp /srv/pgdata/postgresql.conf /srv/pgdata/postgresql.conf.saved-20261120 (the path is an example). Edit max_connections, then read what the server makes of the file without applying it:

SELECT sourcefile, sourceline, name, setting, applied, error FROM pg_file_settings WHERE name = 'max_connections';

The documentation says this view reports on the current contents of the files and helps check planned changes. It also says that if an individual setting cannot be applied, for example because the setting cannot be changed after server start, the row "will have an appropriate message in the error field". The page does not give the wording of that message. max_connections can only be set at server start, so on a running server the error column may show a message for the changed line. Check that the setting column shows the value you typed and that sourcefile and sourceline point at the line you edited. A wrong value in setting means the edit is wrong. The restart is what applies it.

Disruptive step: because max_connections can only be set at server start, the change needs a restart, which the pg_ctl page says is a stop followed by a start. In its default fast mode "All active transactions are rolled back and clients are forcibly disconnected", so schedule a window. Restart with your service manager, or pg_ctl restart -D datadir. Risk: the Connection Settings page says that increasing max_connections "leads to higher allocation of those resources, including shared memory". To back out, run cp /srv/pgdata/postgresql.conf.saved-20261120 /srv/pgdata/postgresql.conf, check with the pg_file_settings query above that setting shows the old value and sourceline points at the restored line, and restart again.

Example (illustrative): db01 has max_connections at its default of 100. During a busy hour the application shop-web reports sorry, too many clients already. Step 3 shows 80 sessions for shop-web in idle in transaction, and step 4 shows they have been idle for over an hour. The developers find a code path that opens a transaction and returns without committing. They fix it and reduce the pool size. No change to max_connections is needed.

Confirm the fix

  1. The count from step 2 stays well below max_connections minus superuser_reserved_connections over a busy period.
  2. No rows remain in idle in transaction for longer than the longest transaction you expect.
  3. The application connects without the error at its busiest time.
  4. If you restarted, SHOW max_connections; shows the new value.

What to watch afterwards

  • Chart the client session count against the limit and alert at a share of it, such as 80 per cent.
  • Alert on sessions in idle in transaction for longer than a few minutes.
  • Add up the pool sizes of all applications whenever one is added.
  • Keep the reserved slots for administrators; do not hand pg_use_reserved_connections to applications.

Risks and limits

  • These commands have not been run or lab-tested.
  • The PostgreSQL documentation does not say exactly which server processes use a connection slot, so the count in step 2 is a guide, not an exact measure of what is left.
  • Message text is from REL_17_STABLE and REL_19_STABLE.
  • A per-role connection limit is a separate limit and is not covered.
  • PgBouncer behaviour is from its configuration page; check the version you run.

Sources