The SQL diagnostic

Run this read-only query against the PostgreSQL environment you are investigating. Interpret the results in the context of your workload and privileges.

WITH session_totals AS (
    SELECT count(*) FILTER (
        WHERE backend_type = 'client backend'
    ) AS client_sessions
    FROM pg_stat_activity
)
SELECT
    current_setting('max_connections')::int
        AS configured_max_connections,
    client_sessions,
    round(100.0 * client_sessions
          / current_setting('max_connections')::numeric, 1)
        AS pct_of_configured_max
FROM session_totals;

-- Which application and state own the connections?
SELECT
    usename,
    application_name,
    state,
    count(*) AS sessions
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, state
ORDER BY sessions DESC;

Based on DBA-020. Counts client backends and presents the percentage as a comparison with the configured maximum, not a precise count of slots still available to every role.

How to read the results

If one application has hundreds of idle sessions, inspect its pool configuration and instance count. Raising max_connections may only postpone the symptom while increasing server overhead.

A session that is idle in transaction deserves separate investigation: it has not merely returned its connection to a pool; it may also hold locks and keep a transaction open.

What to check next

1. Check whether an administrative connection is still possible. At full saturation, you may need a reserved administrative path to run any diagnostic.
2. Group by application, not just username. Several services can share one database role. Application name helps narrow the source.
3. Inspect pool multiplication. Per-instance pool size multiplied by the number of instances can exceed expected connection demand.
4. Plan capacity and pooling together. Review pool settings, transaction duration and an appropriate pooler before increasing the server ceiling.

Related DBA tasks

Continue the investigation with these recipes from SQL Recipes for DBAs: DBA-015 (live sessions), DBA-016 (long-running and idle transactions), and DBA-017 (controlled intervention).