DBAtlas · Database operations atlas

Measure connection usage against max_connections

PostgreSQLSessions

Command

-- Capacity: used slots and slots left for regular users
SELECT max_conn,
       reserved,
       used,
       max_conn - reserved - used AS free_for_normal_users,
       round(100.0 * used / max_conn, 1) AS used_pct
FROM (SELECT current_setting('max_connections')::int AS max_conn,
             current_setting('superuser_reserved_connections')::int
             + coalesce(current_setting('reserved_connections', true)::int, 0) AS reserved,
             (SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend') AS used) x;

-- Breakdown by database, user, application and state
SELECT datname, usename, application_name, state,
       count(*) AS sessions,
       max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename, application_name, state
ORDER BY sessions DESC;

-- Role and database connection limits
SELECT rolname, rolconnlimit FROM pg_roles WHERE rolconnlimit <> -1;
SELECT datname, datconnlimit FROM pg_database WHERE datconnlimit <> -1;

What it does

Shows used connection slots and those left for regular users relative to max_connections, the breakdown by database/user/application/state, and configured CONNECTION LIMITs.

When to use it

On 'too many clients already' / 'remaining connection slots are reserved' errors, during capacity planning, or when evaluating the need for a pooler.

Caution

max_connections limits client backends only; autovacuum workers, background workers and walsenders use separate slots. The last superuser_reserved_connections slots (plus reserved_connections on PG16+) are closed to regular users. Instead of raising max_connections into the hundreds or thousands, use a pooler such as PgBouncer: every connection is a separate process, and memory and snapshot costs grow with the connection count. Many 'idle' sessions indicate an oversized application pool. Prevent a runaway application from consuming all slots with ALTER ROLE ... CONNECTION LIMIT; changing max_connections requires a restart.

Open in DBAtlas →

Related