How Many Connections Can Your PostgreSQL Database Handle?

How Many Connections Can Your PostgreSQL Database Handle?

PostgreSQL 16 split the connection limit into more than one pool. Besides the slots held back for superusers, a second block of slots can now be reserved for ordinary roles that have been granted pg_use_reserved_connections. The number of sessions an application can open is therefore smaller than max_connections, and the gap depends on three other settings. SHOW max_connections gives the ceiling; pg_settings and pg_stat_activity show how much of it is left.

Purpose and Overview

max_connections caps the number of concurrent connections to the database server. The default is typically 100, but initdb may pick a lower value when the kernel settings cannot support it, so a freshly installed server on a small VM can report less than 100. The value can only be set at server start, which makes it one of the settings that is expensive to get wrong: a change means a restart.

The setting is not free to raise. PostgreSQL sizes certain resources directly from max_connections, so a larger value allocates more of them, shared memory included. On a connection problem, check who is using the slots before raising the number. The queries below show what the limit is, how it is applied, how many sessions are open, and which sessions they are.

Sample Code

The limit itself:

1SHOW max_connections;

The limit with its companions and the restart state:

1SELECT name, setting, context, pending_restart
2FROM pg_settings
3WHERE name IN ('max_connections',
4               'superuser_reserved_connections',
5               'reserved_connections');

Current client sessions against the limit:

1SELECT
2    count(*) FILTER (WHERE backend_type = 'client backend')  AS client_sessions,
3    current_setting('max_connections')::int                  AS max_connections,
4    round(100.0 * count(*) FILTER (WHERE backend_type = 'client backend')
5          / current_setting('max_connections')::int, 1)      AS pct_used
6FROM pg_stat_activity;

Who is holding the connections:

1SELECT datname, usename, state, count(*) AS sessions
2FROM pg_stat_activity
3WHERE backend_type = 'client backend'
4GROUP BY datname, usename, state
5ORDER BY sessions DESC;

Limits that apply below the server-wide one:

1SELECT datname, datconnlimit FROM pg_database WHERE datconnlimit <> -1;
2SELECT rolname, rolconnlimit FROM pg_roles    WHERE rolconnlimit  <> -1;

Notes: SHOW works on every supported version. reserved_connections and the pg_use_reserved_connections role exist from PostgreSQL 16; on earlier versions that name is absent from the pg_settings result. The backend_type column of pg_stat_activity exists from PostgreSQL 10. Run the session queries as a superuser or a member of pg_read_all_stats; other roles see many columns of other users' sessions as null.

Code Breakdown

Each query answers a different part of the question.

SHOW

SHOW displays the current value of a run-time parameter. The same value is available through the current_setting() function, and the pg_settings view carries the same information with extra columns. SHOW max_connections is the quickest check; the view is better for scripts.

pg_settings and pending_restart

The context column says how a setting can be changed. For max_connections it reads postmaster: the change takes effect only when the server starts. The pending_restart column is true when the value has been changed in the configuration file but the running server still uses the old one, which is the state a half-finished change leaves behind.

Counting sessions

pg_stat_activity has one row per server process, and not every process is a client. Autovacuum workers, the checkpointer, the WAL writer and WAL senders all appear. Filtering on backend_type = 'client backend' counts the sessions that applications opened. Treat the percentage as an approximation of how close the server is to its limit, not an exact count of free slots.

Grouping by state

The state column separates active from idle, idle in transaction and idle in transaction (aborted). Many sessions in an idle state are the signature of an application pool that holds connections it is not using.

Key Connection Limits

Four layers decide whether a particular connection is accepted.

max_connections

At most max_connections connections can be active at the same time. Nothing lifts this ceiling.

superuser_reserved_connections

When the number of active connections is at least max_connections minus superuser_reserved_connections, new connections are accepted only for superusers. The default is three. These slots are the way back in when an application has used up everything else.

reserved_connections

From PostgreSQL 16, reserved_connections holds back a further block of slots for superusers and for roles with privileges of pg_use_reserved_connections. The default is zero, so nothing changes until it is set. A monitoring or break-glass role that is not a superuser can be given access to this pool.

Per-database and per-role limits

datconnlimit in pg_database and rolconnlimit in pg_roles cap one database or one role, and -1 means no limit. The database limit is enforced only approximately: two sessions that start at the same moment when one slot remains can both fail. It is not enforced against superusers or background worker processes.

Practical Applications

The checks above fit a few recurring jobs.

Diagnosing a connection error

When applications report that new connections are refused, run the usage query first. A figure near 100 percent confirms the limit as the cause. The grouped query then shows whether one application, one role or one database holds most slots, and whether they are active or idle.

Sizing a change

Before raising max_connections, read the state breakdown. If most sessions are idle, adding slots only adds more idle sessions. Putting an external connection pooler in front of the server is often the better fix.

Changing the value

1ALTER SYSTEM SET max_connections = 200;

ALTER SYSTEM writes the setting to postgresql.auto.conf, and because the parameter can only be set at server start, a restart is needed before it applies. The pending_restart column shows the change waiting.

Limiting a noisy tenant

1ALTER DATABASE reporting CONNECTION LIMIT 40;
2ALTER ROLE app_batch CONNECTION LIMIT 20;

A cap on the database or the role keeps one workload from taking every slot while the server-wide value stays unchanged.

Standby servers

A standby must run max_connections at the same value as the primary or higher. If it is lower, queries are not allowed on the standby. When raising the value, raise it on the standbys first, then on the primary.

Version Compatibility

max_connections and superuser_reserved_connections are long-standing parameters and behave the same across supported releases. PostgreSQL 16 added reserved_connections and the pg_use_reserved_connections predefined role. The backend_type column in pg_stat_activity arrived in PostgreSQL 10, so the session queries above do not run on older versions.

The default of three for superuser_reserved_connections and zero for reserved_connections are the values on current releases. Check pg_settings rather than assuming, because a managed service may set its own.

Best Practices

• Read the whole limit — compare max_connections with the reserved settings to know how many slots applications really have.
• Alert on usage, not on errors — track the percentage from the usage query and act well before it nears 100.
• Count idle sessions — a high share of idle or idle in transaction points to the pool, not to the server limit.
• Use a pooler for many clients — a small server-side pool serves many application connections with fewer backends.
• Give monitoring its own slot — grant pg_use_reserved_connections to a monitoring role on PostgreSQL 16 and later so it can connect during an incident.
• Plan the restart — schedule changes to max_connections for a window, and set standbys first.

References

• PostgreSQL: Connection Settings — max_connections, reserved_connections and superuser_reserved_connections, with defaults and restart requirements.
• PostgreSQL: SHOW — The command that displays the current value of a run-time parameter.
• PostgreSQL: The Cumulative Statistics System — pg_stat_activity columns, backend_type, state and who can see other sessions.
• PostgreSQL: pg_settings — The context and pending_restart columns.
• PostgreSQL: Hot Standby — Parameters a standby must match or exceed relative to the primary.
• Crunchy Data: Postgres Tuning and Performance for Analytics Data — Practical guidance on max_connections and connection pooling.