Count PostgreSQL Sessions by State with SQL

Count PostgreSQL Sessions by State

This PostgreSQL query returns a count of all server sessions grouped by their current state. It gives a fast at-a-glance view of how connections are distributed across active, idle, and problematic states.

Purpose and Overview

PostgreSQL tracks the state of every backend connection in the pg_stat_activity system view. Knowing how many connections are in each state at any given moment is one of the first checks a DBA performs when diagnosing connection pressure, performance slowdowns, or application misbehavior.

A single row showing twenty connections in idle in transaction is an immediate red flag that warrants further investigation.

SQL Script

1SELECT
2    count(1),
3    state
4FROM
5    pg_stat_activity
6GROUP BY
7    state
8ORDER BY
9    1 DESC;

Code Breakdown

count(1) and GROUP BY state

The query counts all rows in pg_stat_activity and groups them by the state column. Each row in pg_stat_activity represents one backend process — either a client connection or an internal server process. The result is one row per distinct state value, sorted by count descending so the most common state appears first.

ORDER BY 1 DESC

Sorts results by the count column, putting the highest-volume state at the top. This makes it easy to spot the dominant connection state without scanning the full result set.

pg_stat_activity

The source view exposes real-time information about every backend process. The state column reflects what the backend is currently doing.

Connection State Values

active — the backend is currently executing a query. A high active count is expected under load; a sustained very high count may indicate a bottleneck.

idle — the connection is open and authenticated but not executing any query. A large number of idle connections can exhaust max_connections even though no work is being done. Connection pooling addresses this.

idle in transaction — the backend has an open transaction but is not currently running a query. This state holds row locks and can block other sessions. Connections staying here for extended periods are a common cause of lock contention.

idle in transaction (aborted) — the transaction encountered an error and is waiting for a ROLLBACK. These connections block vacuum and hold resources until cleaned up.

fastpath function call — the backend is executing a fast-path function, a low-level protocol shortcut used by some clients.

disabled — statistics collection is disabled for this backend (track_activities = off). The state cannot be determined.

NULL — the row represents a background worker or autovacuum process rather than a client connection. These do not have a client state.

Sample Output

In this example, 42 idle connections are consuming connection slots without doing any work, and 3 connections are sitting in open transactions — both worth investigating.

Practical Applications

Quick Health Check

Run this query as the first step when investigating slow queries, connection failures, or application errors. The state distribution often points directly to the problem category.

Monitoring and Alerting

Integrate this query into a monitoring system to alert when idle in transaction counts exceed a threshold, or when total connections approach max_connections.

Capacity Planning

Tracking state counts over time reveals connection growth trends and helps determine when connection pooling or a max_connections increase is needed.

Before Running Maintenance

Check the state counts before running VACUUM, ANALYZE, or schema changes. A high count of idle in transaction sessions may hold locks that would block maintenance operations.

Extending the Query

Break Down by Database

 1SELECT
 2    count(1),
 3    datname,
 4    state
 5FROM
 6    pg_stat_activity
 7GROUP BY
 8    datname,
 9    state
10ORDER BY
11    1 DESC;

Include Wait Events

 1SELECT
 2    count(1),
 3    state,
 4    wait_event_type,
 5    wait_event
 6FROM
 7    pg_stat_activity
 8GROUP BY
 9    state,
10    wait_event_type,
11    wait_event
12ORDER BY
13    1 DESC;

Check Against max_connections

1SELECT
2    count(1) AS total_connections,
3    current_setting('max_connections')::int AS max_connections,
4    round(count(1)::numeric / current_setting('max_connections')::int * 100, 1) AS pct_used
5FROM
6    pg_stat_activity;

Version Compatibility

Requires PostgreSQL 9.2 or later. The state column was added to pg_stat_activity in PostgreSQL 9.2. Tested on PostgreSQL 9.2+, 10.x, 11.x, 12.x, and 13.0.

Best Practices

Poll regularly: Run this query every few minutes during incident investigation to see how the state distribution changes over time.

Set alerts on idle in transaction: Any sustained count above a small threshold (e.g., 5+) warrants investigation into which application is leaving transactions open.

Pair with connection limits: Compare total connections against max_connections and superuser_reserved_connections to understand how much headroom remains.

Use a connection pooler: If idle counts are consistently high, tools like PgBouncer reduce idle connection accumulation by multiplexing application connections onto a smaller pool of real database connections.

References

• PostgreSQL Documentation - pg_stat_activity - Full column reference for the pg_stat_activity view including all state values
• PostgreSQL Documentation - max_connections - Reference for connection limit configuration parameters
• Source SQL Script - postgres_sessions_state_count.sql - Original script by Hari Sekhon