Fix PostgreSQL 'sorry, too many clients already' with Limits and Pooling

FATAL: sorry, too many clients already means PostgreSQL has handed out every backend slot allowed by max_connections and is refusing new sessions. To fix it, find which roles and applications hold the slots in pg_stat_activity, terminate idle or abandoned sessions, set idle timeouts and per-role limits, and then put a connection pooler such as PgBouncer (built into Azure Database for PostgreSQL flexible server on port 6432) in front of the database. Raise max_connections only after that, because it needs a restart and costs memory for every slot.

Who this is for and what you will have at the end

This guide is for DBAs, platform engineers and developers who run PostgreSQL themselves (on VMs, Kubernetes or bare metal) or on Azure Database for PostgreSQL flexible server, and who are seeing connection refusals during load spikes, deployments or failovers.

At the end you will have:

• A clear reading of which connection error you hit and what it means.
• Queries that show who is holding connections and in which state.
• A safe way to free slots during an incident.
• Timeouts and per-role or per-database limits that stop one application from exhausting the server.
• PgBouncer in transaction pooling mode, self-managed or built-in on Azure, sized against max_connections.
• A verification routine so you can see the problem is gone.

Understand the error messages

PostgreSQL raises several related errors, all with SQLSTATE 53300 (too_many_connections). The wording tells you which limit you hit.

The relevant server settings:

• max_connections: the maximum number of concurrent connections. The default is typically 100, can only be set at server start, and PostgreSQL sizes shared memory from it. On a streaming replication standby it must be the same or higher than on the primary, or the standby won't allow queries.
• superuser_reserved_connections: slots kept for superusers as a final emergency reserve. Default 3, set at server start.
• reserved_connections (PostgreSQL 16 and later): slots kept for roles with the privileges of pg_use_reserved_connections. Default 0, set at server start.

On Azure Database for PostgreSQL flexible server, Microsoft reserves 15 connections for replication and monitoring, so the maximum user connections are max_connections minus the sum of reserved_connections and superuser_reserved_connections. The default max_connections depends on the compute size chosen at provisioning: for example 50 on B1ms, 859 on D2ds_v5 and 1,718 on D4ds_v5, capped at 5,000 on larger sizes. The default is not recalculated when you change compute later, so adjust it yourself after a scale operation.

Prerequisites

• A role that can read all of pg_stat_activity (a superuser, or a role with privileges of pg_read_all_stats). Ordinary roles see full details only for their own sessions.
• A role that can terminate other sessions: a superuser, a member of the role that owns the session, or a role with privileges of pg_signal_backend. Only superusers can terminate superuser backends.
• For self-managed servers, access to postgresql.conf (or ALTER SYSTEM) and permission to restart PostgreSQL.
• For Azure, the Azure CLI or portal access to the server's parameters page.

Step 1: Get a session when the server is full

If an application role can't connect, connect as a superuser: the reserved slots exist for exactly this. If you have set reserved_connections, grant pg_use_reserved_connections to your monitoring or DBA role in advance so it can still get in during an incident:

GRANT pg_use_reserved_connections TO dba_oncall;

On a managed service where you don't hold a superuser account and no slot is free, reduce load from the client side instead: scale down or stop the application instances that are holding connections, or restart the server as a last resort.

Step 2: Find out who is holding the connections

Start with a count against the limit:

SELECT count(*) AS client_backends,
       current_setting('max_connections')::int AS max_connections
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Then break the sessions down by role, application, client address and state:

SELECT usename,
       application_name,
       client_addr,
       state,
       count(*) AS sessions,
       max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, client_addr, state
ORDER BY sessions DESC;

The state column tells you what each backend is doing:

Hundreds of idle sessions from the same application_name usually mean every application instance opens its own large pool. Many idle in transaction sessions are more serious: they hold locks and stop vacuum from removing dead rows, which causes table bloat. Find the oldest ones:

SELECT pid, usename, application_name, client_addr,
       now() - xact_start AS xact_age,
       now() - state_change AS idle_for,
       left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start;

Step 3: Free slots during the incident

pg_cancel_backend(pid) cancels the current query of a session; pg_terminate_backend(pid, timeout) ends the session. With a timeout in milliseconds, the function waits until the process is actually gone and returns false with a warning if the time runs out.

Terminate sessions that have been idle in a transaction for more than ten minutes:

SELECT pid, usename, application_name,
       pg_terminate_backend(pid, 5000) AS terminated
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
  AND state_change < now() - interval '10 minutes'
  AND backend_type = 'client backend';

Be deliberate here. Terminating a session rolls back its open transaction, and the application sees a broken connection. Target a specific usename or application_name when you can, and tell the owning team.

Step 4: Add timeouts so idle sessions clean themselves up

Three settings stop abandoned sessions from accumulating. All default to 0 (disabled) and take milliseconds when no unit is given.

Apply them per role rather than server-wide. The PostgreSQL documentation warns against enforcing idle_session_timeout on connections that come through a pooler or middleware, because those layers may not react well to unexpected closure, and it recommends against setting transaction_timeout in postgresql.conf because it would affect all sessions.

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE analyst SET idle_session_timeout = '30min';

Role settings apply to new sessions, so existing connections keep the old behavior until they reconnect.

Step 5: Cap roles and databases

A per-role or per-database limit turns a server-wide outage into an error for the one application that over-consumes:

ALTER ROLE app_user CONNECTION LIMIT 80;
ALTER DATABASE reporting CONNECTION LIMIT 40;

Things to know about these limits:

• -1 (the default) means no limit.
• The role limit is enforced approximately: if two sessions start at the same moment for the last slot, both can fail.
• The role limit is never enforced for superusers, and prepared transactions and background workers don't count toward it.
• The sum of your role limits plus the reserved slots should stay below max_connections, otherwise the limits don't protect the server.

Step 6: Put a connection pooler in front of PostgreSQL

PostgreSQL uses a process per connection, so many idle connections are expensive. A pooler accepts thousands of client connections and multiplexes them over a small number of server connections.

Choose the pooling mode

PgBouncer has three modes. Session pooling (the PgBouncer default) releases the server connection only when the client disconnects, so it doesn't reduce server connections for applications that hold connections open. Transaction pooling releases the server connection at the end of each transaction and is what Microsoft recommends for most workloads. Statement pooling doesn't allow multi-statement transactions.

Transaction pooling breaks session-level features by design. Check the application against this list from the PgBouncer feature map:

Self-managed PgBouncer

A minimal transaction-mode configuration. default_pool_size is the number of server connections per user and database pair (default 20), max_client_conn is the number of client connections PgBouncer accepts (default 100), and max_db_connections caps server connections per database regardless of user (default 0, unlimited).

[databases]
appdb = host=10.0.1.10 port=5432 dbname=appdb
 
[pgbouncer]
listen_addr = *
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
max_db_connections = 60
max_prepared_statements = 200
stats_users = pgb_stats

Size it against the server: the total of all pools (default_pool_size multiplied by the number of user and database pairs, bounded by max_db_connections) plus admin, replication and monitoring connections must fit under max_connections. Then reduce each application's own pool so the clients don't simply move the problem to PgBouncer's max_client_conn. If you run PgBouncer in front of a Patroni cluster, the same sizing applies to the promoted primary after failover, as covered in PostgreSQL high availability with Patroni, etcd and HAProxy.

Built-in PgBouncer on Azure Database for PostgreSQL

Azure Database for PostgreSQL flexible server includes PgBouncer on the same VM as the database, listening on port 6432 with the server's normal host name. It is supported on the General Purpose and Memory Optimized tiers, not on Burstable; moving a server to Burstable removes it.

Turn it on by setting pgbouncer.enabled to true on the server's parameters page in the Azure portal (no restart needed), or with the CLI:

az postgres flexible-server parameter set \
  --resource-group rg-data \
  --server-name contoso-pg \
  --name pgbouncer.enabled \
  --value true

The other PgBouncer parameters appear only after pgbouncer.enabled is true. Their defaults on Azure differ from standalone PgBouncer:

Microsoft suggests starting with conservative pool sizes, multiplying the vCore count by a value between 2 and 5, then watching resource use and application performance. Switch applications by changing the port from 5432 to 6432, after testing in a non-production environment. After a zone-redundant HA failover, PgBouncer restarts on the new primary and the connection string stays the same; any server restart also restarts PgBouncer, so clients must reconnect.

Step 7: Raise max_connections only when you have to

If pooling is in place and the server still needs more backends, raise max_connections and restart. On self-managed PostgreSQL:

ALTER SYSTEM SET max_connections = 300;

Then restart the service. Increase the setting on every standby first (or at the same time), because a standby with a lower value than the primary won't allow queries.

On Azure, set the parameter and restart the server:

az postgres flexible-server parameter set \
  --resource-group rg-data --server-name contoso-pg \
  --name max_connections --value 1000
 
az postgres flexible-server restart \
  --resource-group rg-data --name contoso-pg

Microsoft advises against going above the default for the compute size: memory use grows with connections, and a higher value that looks fine while connections are idle can cause high latency or crashes once they become active. Many short-lived connections (under 60 seconds) also drive up CPU on connection setup and teardown, which pooling solves and a higher limit doesn't.

Verification

1. Compare client backends with the limit using the count query from Step 2 during peak load. You want clear headroom above the reserved slots.
2. On a self-managed PgBouncer, connect to the admin console (psql -p 6432 -U pgb_stats pgbouncer) and run SHOW POOLS;. cl_waiting counts clients that have sent queries but have no server connection yet, and maxwait is how long the oldest one has waited. A rising maxwait means the pool is too small or the server is overloaded.
3. On Azure, set pgbouncer.stats_users to an existing user and connect to the pgbouncer database on port 6432 to run the same SHOW commands. Enable metrics.pgbouncer_diagnostics (dynamic, disabled by default) to get the Waiting client connections and Active server connections metrics in Azure Monitor, and alert on them.
4. Confirm the role settings with SELECT rolname, rolconnlimit FROM pg_roles WHERE rolname = 'app_user';.

Troubleshooting

Checklist

• Identify which message you got and which limit it points to.
• Query pg_stat_activity by role, application and state before changing anything.
• Terminate only targeted, stale idle in transaction sessions during the incident.
• Set idle_in_transaction_session_timeout per application role.
• Add role and database CONNECTION LIMIT values that fit inside max_connections.
• Put PgBouncer in transaction mode in front of the database and shrink application pools.
• On Azure, use the built-in PgBouncer on port 6432 (not on Burstable) and enable its metrics.
• Raise max_connections last, restart, and keep standbys equal or higher.

References

• PostgreSQL: Connections and Authentication settings
• PostgreSQL: Client Connection Defaults
• PostgreSQL: The Cumulative Statistics System (pg_stat_activity)
• PostgreSQL: System Administration Functions
• PostgreSQL: CREATE ROLE
• PostgreSQL: ALTER ROLE
• PostgreSQL: ALTER DATABASE
• PostgreSQL source: connection limit checks in postinit.c
• PgBouncer configuration
• PgBouncer features and SQL feature map
• PgBouncer usage and admin console
• Limits in Azure Database for PostgreSQL flexible server
• PgBouncer in Azure Database for PostgreSQL flexible server
• az postgres flexible-server parameter
• az postgres flexible-server