📈

PostgreSQL Statistics Views & Monitoring Functions

Complete reference for PostgreSQL statistics views and monitoring functions including pg_stat_activity, pg_stat_statements, pg_locks, pg_stat_user_tables, and wait event analysis. Essential reference for query performance tuning and database health monitoring. Updated for PostgreSQL 16.

17 functions

Jump to function

pg_stat_activity pg_stat_statements pg_stat_user_tables pg_stat_user_indexes pg_stat_bgwriter pg_stat_database pg_locks pg_blocking_pids pg_stat_replication pg_stat_wal_receiver pg_stat_io pg_stat_wal pg_statio_user_tables pg_statio_user_indexes pg_stat_progress_vacuum pg_stat_progress_create_index pg_relation_size / pg_total_relation_size

What are PostgreSQL Statistics Views & Monitoring Functions?

PostgreSQL statistics views are system views populated by the cumulative statistics collector and the query executor, providing both real-time and historical data about query execution, table and index access patterns, and lock contention. pg_stat_activity shows currently executing queries and their states, pg_stat_statements (extension) aggregates query performance across executions, and pg_locks reveals blocking lock chains. These views are the primary tools for diagnosing slow queries and connection saturation in production PostgreSQL deployments.

pg_stat_activity

PG 8.0+ (enhanced in each major version: wait_event/wait_event_type added PG 9.6, backend_type PG 10, query_id PG 14)→ setof record

System view showing one row per server process, with information about the current activity: database, user, query text, wait events, state, and timing.

DBAReliabilitySecurity

Signature

SELECT * FROM pg_stat_activity

Examples

sql

SELECT pid, usename, state, left(query, 80) FROM pg_stat_activity WHERE state != 'idle';

→All active (non-idle) connections and their queries

sql

SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;

→Longest-running queries

sql

SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE wait_event IS NOT NULL GROUP BY 1, 2 ORDER BY 3 DESC;

→Current wait event distribution

⚠Anti-Pattern

Combine `pg_stat_activity` with `pg_blocking_pids(pid)` to find what is blocking a waiting query: `SELECT pid, pg_blocking_pids(pid) AS blocked_by, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0`.

example

SELECT a.pid, a.query, b.pid AS blocking_pid, b.query AS blocking_query FROM pg_stat_activity a JOIN pg_stat_activity b ON b.pid = ANY(pg_blocking_pids(a.pid));

→Blocked query paired with the query blocking it

pg_stat_statements

PG 9.0+ (requires shared_preload_libraries = 'pg_stat_statements' and CREATE EXTENSION pg_stat_statements; columns added in later versions: wal_* in PG 13, toplevel in PG 14)→ setof record

Extension view that tracks execution statistics for all SQL statements. Requires `pg_stat_statements` to be loaded as a shared preload library.

DBAReliability

Signature

SELECT * FROM pg_stat_statements

Examples

sql

SELECT query, calls, round(mean_exec_time::numeric, 2) AS avg_ms, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

→Top 10 slowest queries by average execution time

sql

SELECT query, calls, round(total_exec_time::numeric/1000, 1) AS total_sec FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

→Top 10 queries by total time consumed

sql

SELECT query, shared_blks_hit, shared_blks_read, round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct FROM pg_stat_statements ORDER BY shared_blks_read DESC LIMIT 10;

→Queries with most physical reads (cache-miss candidates)

⚠Anti-Pattern

Call `SELECT pg_stat_statements_reset()` before a load test to get clean statistics for just that window. Otherwise historical data dilutes the signal from recent changes.

example

SELECT pg_stat_statements_reset();
-- run your workload --
SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;

→Clean stats for just the recent workload

pg_stat_user_tables

PG 8.2+ (n_mod_since_analyze added PG 9.4; n_ins_since_vacuum added PG 13)→ setof record

View showing statistics for each user table: sequential scans, index scans, rows inserted/updated/deleted, dead tuples, last vacuum/analyze times.

DBAReliability

Signature

SELECT * FROM pg_stat_user_tables

Examples

sql

SELECT relname, seq_scan, idx_scan, CASE WHEN (seq_scan + idx_scan) > 0 THEN round(100.0 * idx_scan / (seq_scan + idx_scan), 1) ELSE 0 END AS idx_pct FROM pg_stat_user_tables ORDER BY seq_scan DESC;

→Tables with the most sequential scans — index candidates

sql

SELECT relname, n_dead_tup, n_live_tup, round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY dead_pct DESC;

→Tables bloated with dead tuples — vacuum candidates

sql

SELECT relname, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_user_tables ORDER BY last_autovacuum NULLS FIRST;

→Tables that haven't been vacuumed recently

⚠Anti-Pattern

A high ratio of dead to live tuples causes table bloat and slows queries. If `n_dead_tup / (n_live_tup + n_dead_tup) > 10%` and `last_autovacuum` is stale, autovacuum may not be keeping up — consider manual `VACUUM ANALYZE`.

example

SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 100000 ORDER BY n_dead_tup DESC;

→Tables urgently needing vacuum

pg_stat_user_indexes

PG 8.2+→ setof record

View showing index usage statistics: number of scans, tuples read, and tuples fetched. Used to identify unused or underused indexes.

DBAReliability

Signature

SELECT * FROM pg_stat_user_indexes

Examples

sql

SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;

→Indexes that have never been used — candidates for removal

sql

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 20;

→Unused indexes with their sizes — prioritize dropping large ones

⚠Anti-Pattern

An `idx_scan = 0` index might still be valuable if the database was recently restarted (stats reset on restart) or if it supports a constraint. Cross-check with `pg_indexes` and verify the index isn't a primary key or unique constraint before dropping.

example

SELECT i.relname AS table, i.indexrelname, s.idx_scan, pg_size_pretty(pg_relation_size(s.indexrelid)) FROM pg_stat_user_indexes s JOIN pg_indexes i ON i.indexname = s.indexrelname WHERE s.idx_scan < 10 ORDER BY pg_relation_size(s.indexrelid) DESC;

→Low-usage indexes sorted by wasted space

pg_stat_bgwriter

PG 8.3+ (buffers_backend_fsync added PG 9.1; split into pg_stat_bgwriter + pg_stat_checkpointer in PG 17)→ record (single row)

Single-row view with cumulative statistics for the background writer and checkpointer: checkpoint counts, buffers written, buffers allocated.

DBAReliability

Signature

SELECT * FROM pg_stat_bgwriter

Examples

sql

SELECT checkpoints_timed, checkpoints_req, round(100.0 * checkpoints_req / nullif(checkpoints_timed + checkpoints_req, 0), 1) AS forced_pct FROM pg_stat_bgwriter;

→Fraction of checkpoints that were forced (high = tune checkpoint settings)

sql

SELECT buffers_checkpoint, buffers_clean, buffers_backend, buffers_alloc FROM pg_stat_bgwriter;

→Buffer write breakdown by source

⚠Anti-Pattern

If `checkpoints_req / (checkpoints_timed + checkpoints_req)` is above 10-20%, checkpoints are happening too frequently. Increase `max_wal_size` and `checkpoint_completion_target` to spread I/O more evenly.

example

SELECT checkpoints_req, checkpoints_timed, buffers_backend / nullif(buffers_checkpoint, 0) AS backend_write_ratio FROM pg_stat_bgwriter;

→High backend_write_ratio means the bgwriter isn't cleaning pages fast enough

pg_stat_database

PG 7.4+ (temp_files/temp_bytes added PG 9.2; checksum_failures added PG 12; session_time/active_time added PG 14)→ setof record

View with per-database statistics: connections, commits, rollbacks, cache hits, rows read/fetched, deadlocks, temp file usage.

DBAReliability

Signature

SELECT * FROM pg_stat_database

Examples

sql

SELECT datname, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 1) AS cache_hit_pct FROM pg_stat_database WHERE datname = current_database();

→Buffer cache hit ratio — should be above 95%

sql

SELECT datname, deadlocks, conflicts, temp_files, pg_size_pretty(temp_bytes) FROM pg_stat_database ORDER BY deadlocks DESC;

→Databases with deadlocks, conflicts, and temp file usage

⚠Anti-Pattern

A cache hit ratio below 95% suggests your working set doesn't fit in `shared_buffers`. Increasing `shared_buffers` (typically to 25% of RAM) can dramatically reduce disk I/O.

example

SELECT datname, blks_hit, blks_read, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_ratio FROM pg_stat_database WHERE datname NOT LIKE 'template%';

→Cache hit ratio per database — below 95% is a concern

pg_locks

PG 7.4+→ setof record

View showing all currently held and awaited locks in the database cluster. Each row represents one lock or lock request.

DBAReliabilitySecurity

Signature

SELECT * FROM pg_locks

Examples

sql

SELECT pid, locktype, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted;

→Locks that are currently being waited for

sql

SELECT l.pid, l.mode, l.relation::regclass, a.query FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE NOT l.granted;

→Blocked pids with their waiting queries

⚠Anti-Pattern

Lock rows don't include the query text. Join on `pid` to see which query holds an AccessExclusiveLock: `SELECT a.query FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE l.mode = 'AccessExclusiveLock'`.

example

SELECT blocking.pid, blocking.query, blocked.pid AS blocked_pid, blocked.query AS blocked_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

→Full blocker-to-blocked chain with query text

pg_blocking_pids

PG 9.6+→ integer[]

Returns an array of PIDs that are blocking the given server process from acquiring a lock. Returns an empty array if the process is not blocked.

DBAReliability

Signature

pg_blocking_pids ( pid integer ) → integer[]

Parameters

Parameter	Type	Description
pid	integer	PID of the process to check for blockers

Examples

sql

SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

→All currently blocked processes and their blockers

⚠Anti-Pattern

`pg_blocking_pids(pid)` always returns an array (possibly empty). Filter with `cardinality(pg_blocking_pids(pid)) > 0` to find only processes that are genuinely blocked, then optionally call `pg_cancel_backend` on the blocking pid.

example

SELECT pid, usename, query, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

→Blocked sessions with which PIDs are blocking them

pg_stat_replication

PG 9.1+ (write_lag/flush_lag/replay_lag interval columns added PG 10; spill_* and send_buffer_size added PG 14)→ setof record

View showing one row per WAL sender process (standby connections). Shows replication state, flush/write/replay LSN positions, and replication lag.

DBAReliability

Signature

SELECT * FROM pg_stat_replication

Examples

sql

SELECT client_addr, state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;

→Replication lag per standby

sql

SELECT client_addr, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS bytes_behind FROM pg_stat_replication;

→Bytes of WAL each standby is behind

⚠Anti-Pattern

`replay_lag` (an interval) is the time-based lag. For bytes-behind, use `pg_wal_lsn_diff(sent_lsn, replay_lsn)`. During low-activity periods, time-based lag can be misleadingly high even though the standby is fully current in bytes.

example

SELECT client_addr, replay_lag, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS bytes_behind FROM pg_stat_replication;

→Combined lag picture: time and data volume

pg_stat_wal_receiver

PG 9.6+→ record (single row)

Single-row view on a standby server showing the WAL receiver's status: connected primary, last received LSN, receive timing, and status.

DBAReliability

Signature

SELECT * FROM pg_stat_wal_receiver

Examples

sql

SELECT status, sender_host, received_lsn, last_msg_receipt_time FROM pg_stat_wal_receiver;

→WAL receiver connection status and last received position

⚠Anti-Pattern

On a standby, `pg_stat_wal_receiver.status` should be 'streaming'. A 'catchup' status means the standby is replaying old WAL. If `last_msg_receipt_time` is stale, the WAL stream may be broken.

example

SELECT status, now() - last_msg_receipt_time AS silence_duration FROM pg_stat_wal_receiver;

→Long silence indicates a broken replication connection

pg_stat_io

PG 16+→ setof record

View (added in PG 16) tracking I/O operations per backend type and I/O object: reads, writes, hits, evictions, reuses, and timing.

DBAReliability

Signature

SELECT * FROM pg_stat_io

Examples

sql

SELECT backend_type, object, reads, writes, hits FROM pg_stat_io WHERE reads > 0 ORDER BY reads DESC;

→I/O breakdown by backend type and object type

sql

SELECT object, reads, hits, round(100.0 * hits / nullif(reads + hits, 0), 1) AS hit_pct FROM pg_stat_io WHERE context = 'normal' ORDER BY reads DESC;

→Cache hit ratio per object type

⚠Anti-Pattern

Before PG 16, I/O analysis required external tools. Now `pg_stat_io` shows exactly which backend types (client backend, autovacuum, bgwriter) are doing reads and writes, making it easy to attribute I/O load.

example

SELECT backend_type, sum(read_time) AS total_read_ms, sum(write_time) AS total_write_ms FROM pg_stat_io GROUP BY backend_type ORDER BY total_read_ms DESC;

→Time spent on I/O per backend type

pg_stat_wal

PG 14+→ record (single row)

Single-row view showing WAL generation statistics: WAL records written, full-page writes, bytes written, buffers written, and sync statistics.

DBAReliability

Signature

SELECT * FROM pg_stat_wal

Examples

sql

SELECT wal_records, pg_size_pretty(wal_bytes) AS wal_size, wal_buffers_full FROM pg_stat_wal;

→Total WAL generated since last reset

⚠Anti-Pattern

Divide `wal_bytes` by time since `stats_reset` to get the WAL generation rate. Compare this to your WAL retention settings (`wal_keep_size`) to ensure replicas can keep up and WAL archives don't overflow.

example

SELECT pg_size_pretty(wal_bytes) AS total_wal, pg_size_pretty(wal_bytes / extract(epoch FROM now() - stats_reset)::bigint) AS bytes_per_sec FROM pg_stat_wal;

→Approximate WAL generation rate

pg_statio_user_tables

PG 8.2+→ setof record

View showing I/O statistics per user table: heap blocks read from disk vs hits from cache, index blocks read vs hits.

DBAReliability

Signature

SELECT * FROM pg_statio_user_tables

Examples

sql

SELECT relname, heap_blks_hit, heap_blks_read, round(100.0 * heap_blks_hit / nullif(heap_blks_hit + heap_blks_read, 0), 1) AS heap_hit_pct FROM pg_statio_user_tables ORDER BY heap_blks_read DESC;

→Per-table cache hit ratio — identify hot tables reading from disk

⚠Anti-Pattern

If one table has a consistently low heap cache hit rate, it may be evicting other tables from shared_buffers. Consider partitioning it, using tablespaces on faster storage, or increasing `shared_buffers`.

example

SELECT relname, pg_size_pretty(pg_relation_size(relid)) AS size, round(100.0 * heap_blks_hit / nullif(heap_blks_hit + heap_blks_read, 0), 1) AS hit_pct FROM pg_statio_user_tables ORDER BY heap_blks_read DESC LIMIT 10;

→Large tables with low cache hit rates

pg_statio_user_indexes

PG 8.2+→ setof record

View showing per-index I/O statistics: index blocks read from disk vs hits from buffer cache.

DBAReliability

Signature

SELECT * FROM pg_statio_user_indexes

Examples

sql

SELECT relname, indexrelname, idx_blks_read, idx_blks_hit, round(100.0 * idx_blks_hit / nullif(idx_blks_hit + idx_blks_read, 0), 1) AS hit_pct FROM pg_statio_user_indexes ORDER BY idx_blks_read DESC LIMIT 10;

→Indexes with most disk reads — low cache hit candidates

⚠Anti-Pattern

Active indexes are kept warm in shared_buffers. An index with low `hit_pct` is either rarely used (verify with `pg_stat_user_indexes.idx_scan`) or too large to fit in cache — consider a partial index to reduce its size.

example

SELECT s.indexrelname, s.idx_scan, io.idx_blks_read, round(100.0 * io.idx_blks_hit / nullif(io.idx_blks_hit + io.idx_blks_read, 0), 1) AS hit_pct FROM pg_stat_user_indexes s JOIN pg_statio_user_indexes io USING (indexrelid) WHERE s.idx_scan > 100 ORDER BY io.idx_blks_read DESC;

→Used indexes with poor cache hit — likely too large

pg_stat_progress_vacuum

PG 9.6+→ setof record

Shows progress of in-progress VACUUM operations: current phase, heap blocks scanned/vacuumed, dead tuples collected, and index vacuum passes.

DBAReliability

Signature

SELECT * FROM pg_stat_progress_vacuum

Examples

sql

SELECT relid::regclass AS table, phase, heap_blks_scanned, heap_blks_total, round(100.0 * heap_blks_scanned / nullif(heap_blks_total, 0), 1) AS pct_done FROM pg_stat_progress_vacuum;

→Current VACUUM progress and completion percentage

⚠Anti-Pattern

Track `heap_blks_scanned / heap_blks_total` over time to estimate when a long-running VACUUM will finish. You can also check `num_dead_item_ids` — a very high number might indicate index vacuuming will take a while.

example

SELECT relid::regclass, phase, heap_blks_scanned || '/' || heap_blks_total AS progress, num_dead_item_ids FROM pg_stat_progress_vacuum;

→Real-time VACUUM progress monitoring

pg_stat_progress_create_index

PG 12+→ setof record

Shows progress of in-progress CREATE INDEX and REINDEX operations: current phase, blocks scanned, tuples processed.

DBAReliability

Signature

SELECT * FROM pg_stat_progress_create_index

Examples

sql

SELECT relid::regclass AS table, phase, blocks_done, blocks_total, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct FROM pg_stat_progress_create_index;

→Percentage complete for active index builds

⚠Anti-Pattern

A concurrent index build goes through multiple phases (initializing, scanning heap, writing). The 'waiting for old snapshots' phase can stall on long-running transactions. Monitor phase transitions to identify bottlenecks.

example

SELECT relid::regclass, phase, blocks_done || '/' || blocks_total AS block_progress, tuples_done FROM pg_stat_progress_create_index;

→Live index build progress per phase

pg_relation_size / pg_total_relation_size

PG 8.1+ (pg_relation_size fork parameter added PG 9.0; pg_total_relation_size PG 8.1+)→ bigint

Returns the on-disk size in bytes of a relation. `pg_relation_size` returns the main fork only; `pg_total_relation_size` includes indexes, TOAST, and free space maps.

DBAReliability

Signatures

pg_relation_size ( relation regclass [, fork text ] ) → bigint

pg_total_relation_size ( relation regclass ) → bigint

Parameters

Parameter	Type	Description
relation	regclass	Table, index, or other relation name or OID

Examples

sql

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

→Top 10 largest tables including indexes

sql

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;

→Largest indexes

⚠Anti-Pattern

The difference between `pg_total_relation_size` and `pg_relation_size` is the combined size of all indexes plus TOAST. A very large gap indicates heavy indexing overhead — reconsider whether all indexes are needed.

example

SELECT relname, pg_size_pretty(pg_relation_size(relid)) AS table_only, pg_size_pretty(pg_total_relation_size(relid)) AS with_indexes FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

→Table size vs total size (diff = indexes + TOAST)

PostgreSQL Statistics Views & Monitoring Functions

Jump to function

What are PostgreSQL Statistics Views & Monitoring Functions?

pg_stat_activity

Signature

Examples

pg_stat_statements

Signature

Examples

pg_stat_user_tables

Signature

Examples

pg_stat_user_indexes

Signature

Examples

pg_stat_bgwriter

Signature

Examples

pg_stat_database

Signature

Examples

pg_locks

Signature

Examples

pg_blocking_pids

Signature

Parameters

Examples

pg_stat_replication

Signature

Examples

pg_stat_wal_receiver

Signature

Examples

pg_stat_io

Signature

Examples

pg_stat_wal

Signature

Examples

pg_statio_user_tables

Signature

Examples

pg_statio_user_indexes

Signature

Examples

pg_stat_progress_vacuum

Signature

Examples

pg_stat_progress_create_index

Signature

Examples

pg_relation_size / pg_total_relation_size

Signatures

Parameters

Examples

Related PostgreSQL Categories