测量连接使用情况相对于 max_connections 的比例

命令

-- 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;

作用

显示相对于 max_connections 已使用的连接槽和留给普通用户的剩余槽，按数据库/用户/应用/状态的细分，以及已配置的 CONNECTION LIMIT。

适用场景

出现 'too many clients already' / 'remaining connection slots are reserved' 错误时、容量规划期间，或评估是否需要连接池时。

注意

max_connections 只限制客户端后端进程；autovacuum worker、后台 worker 和 walsender 使用单独的槽。最后的 superuser_reserved_connections 个槽（PG16+ 还有 reserved_connections）不对普通用户开放。与其把 max_connections 调到数百甚至数千，不如使用 PgBouncer 等连接池：每个连接都是一个独立进程，内存和快照成本随连接数增长。大量 'idle' 会话说明应用连接池过大。用 ALTER ROLE ... CONNECTION LIMIT 防止失控的应用占用所有槽；修改 max_connections 需要重启。