Monitor PostgreSQL Wait Events Using pg_stat_activity

Understanding PostgreSQL Wait Events for Performance Analysis

Every active PostgreSQL backend is either executing work or waiting. When a session waits, PostgreSQL records the reason in pg_stat_activity using two columns: wait_event_type and wait_event. Aggregating these across all sessions gives a clear picture of where your database is spending idle time — and what is holding it back.

Common wait event types include Lock (heavyweight locks between transactions), LWLock (internal lightweight locks), IO (disk reads and writes), IPC (inter-process communication), and Client (waiting on application input). A cluster dominated by IO waits may need larger shared_buffers; a cluster dominated by Lock waits has contention between transactions that needs investigation at the query level.

Sample Code

 1SELECT
 2    wait_event_type,
 3    wait_event,
 4    COUNT(*)                              AS session_count,
 5    STRING_AGG(pid::text, ', ')           AS pids,
 6    STRING_AGG(DISTINCT datname, ', ')    AS databases
 7FROM pg_stat_activity
 8WHERE wait_event IS NOT NULL
 9GROUP BY wait_event_type, wait_event
10ORDER BY session_count DESC;

Notes: Available from PostgreSQL 9.6 when wait_event_type and wait_event were introduced. Run as a superuser or as a role with pg_monitor (PostgreSQL 10+) to see all sessions; non-superusers see only their own sessions.

Code Breakdown

• pg_stat_activity — one row per server process; columns include pid, datname, state, wait_event_type, wait_event
• WHERE wait_event IS NOT NULL — filters to sessions actively waiting; excludes sessions that are running or idle with no wait
• wait_event_type — broad category: Lock, LWLock, IO, IPC, Client, Timeout, BufferPin, Extension, Activity
• wait_event — specific wait within the category (e.g. relation under Lock, DataFileRead under IO)
• COUNT(*) — number of sessions in each wait state
• STRING_AGG(pid::text, ', ') — lists process IDs for follow-up with pg_cancel_backend or pg_terminate_backend
• STRING_AGG(DISTINCT datname, ', ') — shows which databases are affected
• ORDER BY session_count DESC — highest-frequency waits first

Key Points

• A healthy cluster shows most sessions in no-wait state; a long wait summary list signals contention
• IO / DataFileRead in volume means data is not in shared_buffers and PostgreSQL is hitting disk
• Lock / relation or Lock / tuple waits indicate row-level or table-level lock contention between transactions
• LWLock waits are internal; high counts of BufferMapping or WALWrite point to buffer or WAL throughput limits
• Client / ClientRead means sessions are blocked waiting for the application to send the next query — often a connection pooler or application-side issue
• Results reflect a point-in-time snapshot; run repeatedly or wrap in a loop to observe trends

Insights and Explanations

To drill into the specific queries driving a wait state, join back to pg_stat_activity using the pids output or filter by wait_event_type directly:

 1SELECT
 2    pid,
 3    datname,
 4    usename,
 5    state,
 6    wait_event_type,
 7    wait_event,
 8    NOW() - query_start      AS query_age,
 9    LEFT(query, 120)         AS query_snippet
10FROM pg_stat_activity
11WHERE wait_event_type = 'Lock'
12ORDER BY query_age DESC NULLS LAST;

For blocking lock chains, combine with pg_blocking_pids():

1SELECT
2    pid,
3    wait_event_type,
4    wait_event,
5    pg_blocking_pids(pid)    AS blocked_by,
6    LEFT(query, 80)          AS query_snippet
7FROM pg_stat_activity
8WHERE pg_blocking_pids(pid) <> '{}'
9ORDER BY pid;

Persistent IO waits that don't resolve during off-peak hours suggest shared_buffers is undersized. The PostgreSQL rule of thumb is 25% of total RAM for a dedicated database server, up to a practical ceiling of around 8 GB beyond which returns diminish. Adjust effective_cache_size in tandem.

Additional Considerations

• Permissions: superuser or pg_monitor role required to see all sessions; pg_stat_activity returns only own sessions for unprivileged users (PostgreSQL 10+)
• Version compatibility: wait_event_type and wait_event added in PostgreSQL 9.6; the query works on 9.6 and later
• Sampling cadence: a single snapshot can miss short-lived waits; tools like pg_activity or pgBadger sample continuously for a fuller picture
• Activity wait type: background workers (autovacuum launcher, WAL writer, checkpointer) appear here when idle — these are normal and not a concern

References

• PostgreSQL: pg_stat_activity
• PostgreSQL: Wait Event Types
• PostgreSQL: pg_blocking_pids()