The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| List active sessions | SELECT pid,
usename,
application_name,
state,
query_start
FROM pg_stat_activity
WHERE state = 'active'; | View examples |
| Measure transaction age | clock_timestamp() - xact_start AS transaction_age | View examples |
| Find idle transactions | WHERE state = 'idle in transaction'
ORDER BY xact_start NULLS LAST | View examples |
| Inspect wait events | SELECT pid, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event IS NOT NULL; | View examples |
| Find direct blockers | SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0; | View examples |
| Bound lock waiting | SET LOCAL lock_timeout = '2s'; | View examples |
| Rank total execution time | SELECT queryid, calls, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20; | View examples |
| Rank mean execution time | SELECT queryid, calls, mean_exec_time
FROM pg_stat_statements
WHERE calls >= 100
ORDER BY mean_exec_time DESC; | View examples |
| Calculate cache hit ratio | blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) | View examples |
| Inspect backend I/O | SELECT backend_type,
object,
context,
reads,
read_time,
writes,
write_time
FROM pg_stat_io; | View examples |
| Monitor VACUUM progress | SELECT pid,
relid::regclass,
phase,
heap_blks_scanned,
heap_blks_total
FROM pg_stat_progress_vacuum; | View examples |
| Monitor index creation | SELECT pid,
relid::regclass,
phase,
blocks_done,
blocks_total
FROM pg_stat_progress_create_index; | View examples |
| Measure replay byte lag | pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_byte_lag | View examples |
| Inspect replication slots | SELECT slot_name,
slot_type,
active,
restart_lsn,
wal_status
FROM pg_replication_slots; | View examples |
| Refresh cached statistics | SELECT pg_stat_clear_snapshot(); | View examples |
PostgreSQL exposes live session state, cumulative counters, wait events, progress views, and optional normalized statement statistics. Useful monitoring combines them with timestamps and rate calculations rather than interpreting one snapshot in isolation. Collect the least-sensitive fields needed, establish workload baselines, and investigate before terminating sessions or resetting statistics.
Step by step
Detailed examples
Start incidents with session and transaction timelines
pg_stat_activity has one row per server process and exposes session, transaction, query, state-change, wait, and backend transaction timestamps. query_start is not transaction age, and a long-lived idle transaction can retain snapshots or locks even without an active query. Filter out your diagnostic backend and rank by the timestamp relevant to the suspected problem.
SELECT pid, datname, usename, application_name, client_addr, state,
clock_timestamp() - xact_start AS transaction_age,
clock_timestamp() - query_start AS query_age,
wait_event_type, wait_event, left(query, 200) AS query_sample
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND (state = 'active' OR state = 'idle in transaction')
ORDER BY xact_start NULLS LAST, query_start NULLS LAST;Interpret state and wait event together
An active backend can still be waiting, and a non-null wait_event does not by itself prove pathological delay. wait_event_type identifies broad causes such as Lock, IO, Client, or Activity; wait_event provides the specific event. Compare persistence across samples and correlate database waits with operating-system and storage telemetry.
SELECT backend_type, state, wait_event_type, wait_event, count(*) AS sessions
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY backend_type, state, wait_event_type, wait_event
ORDER BY sessions DESC, wait_event_type, wait_event;Resolve blocking relationships before considering intervention
pg_blocking_pids returns hard and soft blockers recognized by the lock manager and is safer than hand-joining pg_locks for a first view. A blocked backend can have multiple blockers and chains can form. Identify owners, applications, transaction ages, and business operations before using pg_cancel_backend or pg_terminate_backend; termination rolls back the target transaction and can amplify load.
SELECT blocked.pid AS blocked_pid,
blocked.application_name AS blocked_app,
blocker.pid AS blocker_pid,
blocker.application_name AS blocker_app,
blocker.state AS blocker_state,
clock_timestamp() - blocker.xact_start AS blocker_xact_age
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = b.pid
ORDER BY blocker_xact_age DESC NULLS LAST;Use normalized statement statistics for workload ranking
pg_stat_statements aggregates planning and execution metrics by query identity, database, user, and top-level status. It must be loaded through shared_preload_libraries and enabled with CREATE EXTENSION. Totals identify workload impact while means and percentiles answer different questions; counter resets, evictions, configuration, parallel workers, and optional I/O timing affect interpretation.
SELECT queryid, calls, rows,
round(total_exec_time::numeric, 1) AS total_exec_ms,
round(mean_exec_time::numeric, 2) AS mean_exec_ms,
shared_blks_read, shared_blks_hit, temp_blks_written,
left(query, 240) AS query_sample
FROM pg_stat_statements
WHERE calls >= 20
ORDER BY total_exec_time DESC
LIMIT 20;Convert cumulative counters into rates over known intervals
Views such as pg_stat_database and pg_stat_io accumulate counters since their reported reset time. Subtract two timestamped samples to obtain rates; a single lifetime total cannot explain a short incident. A buffer hit is not necessarily a physical storage read, and timing columns are meaningful only when the corresponding track_io_timing setting is enabled.
SELECT datname, xact_commit, xact_rollback, tup_returned, tup_fetched,
blks_read, blks_hit,
round(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0), 4) AS hit_fraction,
temp_files, temp_bytes, deadlocks, stats_reset
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY datname;
SELECT backend_type, object, context, reads, read_time, writes, write_time
FROM pg_stat_io
ORDER BY backend_type, object, context;Use operation-specific progress views without assuming linear completion
PostgreSQL reports progress for selected commands including VACUUM, ANALYZE, COPY, CREATE INDEX, CLUSTER, and base backup. Available counters and phases differ by command; some phases have no meaningful total, and work estimates can change. Join relation OIDs to regclass for readable output and alert on stalled change across samples rather than one percentage.
SELECT pid, datname, relid::regclass AS relation, phase,
heap_blks_scanned, heap_blks_total,
round(100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 1) AS heap_scan_pct,
index_vacuum_count, dead_tuple_bytes, num_dead_item_ids
FROM pg_stat_progress_vacuum
ORDER BY pid;Monitor replication by durability position and retained WAL
pg_stat_replication shows one row per WAL sender and its sent, written, flushed, and replayed positions. Byte lag measures retained work, not elapsed recovery time; low write volume can make time-based lag columns stale or null. Inspect replication slots because an inactive slot can retain WAL until storage fills, subject to max_slot_wal_keep_size and slot invalidation state.
SELECT application_name, client_addr, state, sync_state,
pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_byte_lag,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_byte_lag,
write_lag, flush_lag, replay_lag
FROM pg_stat_replication
ORDER BY application_name;
SELECT slot_name, slot_type, active, restart_lsn, wal_status, safe_wal_size
FROM pg_replication_slots
ORDER BY slot_name;Preserve evidence, privacy, and snapshot semantics
Cumulative statistics may be cached within a transaction according to stats_fetch_consistency; pg_stat_clear_snapshot forces later accesses to refetch. Keep collectors out of long transactions. Query text, client addresses, and application names can contain sensitive data, while visibility is restricted for ordinary roles; grant predefined monitoring roles narrowly and avoid publishing raw SQL to broad dashboards.
SELECT pg_stat_clear_snapshot();
SELECT clock_timestamp() AS sampled_at, datname, backend_type, state,
wait_event_type, wait_event, count(*) AS sessions
FROM pg_stat_activity
GROUP BY datname, backend_type, state, wait_event_type, wait_event
ORDER BY datname, backend_type, state;Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development GroupPostgreSQL: Monitoring Database Activitypostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: The Cumulative Statistics Systempostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Progress Reportingpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: pg_stat_statementspostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: System Administration Functionspostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.