PostgreSQL Statistics Views & Monitoring Functions

Calling pg_blocking_pids on every row in pg_stat_activity in a tight monitoring loop — the function inspects lock structures for each call; querying it for hundreds of connections repeatedly can itself impose overhead; sample at a reasonable interval. Also: Assuming the returned PIDs are the root cause — pg_blocking_pids returns the immediate blocker; that blocker may itself be blocked by another process. Follow the chain recursively or use a CTE to find the root blocker. Also: Using pg_cancel_backend on a pid from pg_blocking_pids without checking if it is idle-in-transaction — cancelling an actively-executing query is safe; cancelling an idle-in-transaction session leaves the transaction open and may not release locks immediately. Also: Treating an empty array return as proof that no blocking exists — advisory locks and certain lock types (e.g., predicate locks) are not covered by pg_blocking_pids; use pg_locks for a complete picture.

✓ Instead: See description and related functions for the correct approach.

Using pg_locks without joining to pg_stat_activity to identify the blocking query — lock rows contain pid but no query text; always join ON pid to pg_stat_activity to see what SQL holds the lock. Also: Filtering only WHERE NOT granted to find problems — granted = true rows show who holds the lock that is causing the wait; you need both sides of the join to diagnose blocking. Also: Casting relation to regclass without guarding against NULL — pg_locks rows for non-relation lock types (e.g., transactionid, virtualxid) have a NULL relation; unguarded ::regclass casts error out on NULL. Also: Reading pg_locks in isolation without pg_blocking_pids() — for multi-level lock chains (A blocks B blocks C), manually tracing through pg_locks is error-prone; pg_blocking_pids handles the full chain. Also: Alarming on any AccessExclusiveLock without checking duration — short-lived AELocks during routine DDL are normal; correlate with now() - state_change from pg_stat_activity to detect only long-held exclusive locks.

✓ Instead: See description and related functions for the correct approach.

Using pg_relation_size when you actually need the full storage cost — pg_relation_size returns only the main heap fork; for total storage including indexes and TOAST, use pg_total_relation_size to avoid dramatically understating table size. Also: Running pg_total_relation_size on every table in a tight loop without caching — each call acquires a brief lock to read the file system; looping over thousands of tables rapidly can cause lock contention; batch with a single SELECT over pg_stat_user_tables instead. Also: Comparing pg_relation_size output directly to OS-reported file sizes — the relation size includes only allocated pages, not necessarily the same bytes the OS reports due to filesystem block alignment and pre-allocated extents. Also: Not using pg_size_pretty for human output — pg_relation_size returns raw bytes; always wrap with pg_size_pretty() in reports to avoid misreading large numbers (e.g., confusing megabytes with gigabytes). Also: Forgetting that pg_relation_size does not account for table bloat — a table that has been heavily updated may show a large size even though most blocks contain dead tuples; cross-reference with pg_stat_user_tables.n_dead_tup before concluding the data itself is large.

✓ Instead: See description and related functions for the correct approach.

Reading pg_stat_activity without filtering out idle connections — idle rows dominate the result set and obscure active workload; always add WHERE state != 'idle'. Also: Reading wait_event without understanding wait_event_type context — a wait_event of 'Lock' means nothing without knowing wait_event_type is 'Lock'; always select both columns together. Also: Using pg_stat_activity to check for blocking without pg_blocking_pids() — manually comparing pids and relation locks misses soft dependencies; use pg_blocking_pids(pid) instead. Also: Granting broad SELECT on pg_stat_activity to application roles — the view exposes query text, usernames, and client addresses for all sessions; restrict access and use pg_monitor role for monitoring accounts. Also: Trusting query_start for duration without checking state — query_start is only meaningful when state = 'active'; for idle-in-transaction rows, use state_change instead.

✓ Instead: See description and related functions for the correct approach.

Reading pg_stat_bgwriter once and drawing conclusions — the view is cumulative since last reset; compute rates by sampling twice and dividing the delta by elapsed time, not by reading a snapshot. Also: Ignoring buffers_backend as a signal — when backends write buffers themselves (buffers_backend is high relative to buffers_checkpoint), the bgwriter is not cleaning pages fast enough; increase bgwriter_lru_maxpages or bgwriter_delay. Also: Treating checkpoints_req = 0 as healthy without checking checkpoint frequency — in a low-write workload checkpoints_req can be zero while checkpoints_timed fires too frequently; ensure checkpoint_timeout matches your recovery-time objective. Also: Not resetting stats with pg_stat_reset() before a benchmark — historical checkpoint data from normal operations masks the checkpoint behavior under load; reset before load tests for a clean picture. Also: Confusing buffers_clean (bgwriter) with buffers_checkpoint (checkpointer) — they represent different write paths; a high buffers_checkpoint/buffers_alloc ratio means I/O is checkpoint-dominated, not bgwriter-dominated.

✓ Instead: See description and related functions for the correct approach.

Computing cache hit ratio with blks_hit / (blks_hit + blks_read) across all databases including template0/template1 — these have near-zero reads and inflate the apparent hit ratio; always filter WHERE datname NOT LIKE 'template%'. Also: Treating a cache hit ratio above 99% as proof that shared_buffers is large enough — a ratio near 100% can also indicate a tiny working set that fits easily; cross-reference with blks_read growth rate over time. Also: Ignoring xact_rollback relative to xact_commit — a high rollback ratio indicates application-level errors or frequent serialization failures; it warrants investigation beyond just I/O stats. Also: Reading temp_files without correlating with work_mem — a large temp_files count means sort/hash operations are spilling to disk; the fix is usually increasing work_mem per session rather than shared_buffers. Also: Not monitoring deadlocks column over time — pg_stat_database.deadlocks is cumulative; set up a time-series metric and alert on rate increases rather than polling the absolute value.

✓ Instead: See description and related functions for the correct approach.

Querying pg_stat_io on PostgreSQL versions before 16 — the view does not exist; check server_version_num >= 160000 before including it in monitoring scripts. Also: Summing reads across all backend_type and context combinations without grouping — autovacuum, bgwriter, and client backends have very different I/O profiles; aggregate within each backend_type to avoid misleading totals. Also: Ignoring evictions alongside reads — evictions show pages ejected from shared_buffers to make room for new pages; high evictions alongside high reads signals a shared_buffers working set that is too small. Also: Reading read_time and write_time without verifying track_io_timing is enabled — timing columns are populated only when track_io_timing = on; without it, both columns are always zero, making I/O timing invisible. Also: Treating pg_stat_io hits as redundant with pg_stat_database cache hit ratio — pg_stat_io splits hits by backend type and object (heap vs index vs temp); this granularity reveals, for example, whether autovacuum is consuming cache that client queries need.

✓ Instead: See description and related functions for the correct approach.

Treating blocks_done / blocks_total as a reliable ETA for a concurrent index build — REINDEX CONCURRENTLY performs multiple heap scans; blocks_done resets between phases, so a progress percentage near 100% may restart. Also: Cancelling an index build stuck in 'waiting for old snapshots' — this phase waits for long-running transactions to complete; cancelling the index build does not fix the root cause. Identify and terminate the offending long-running transaction instead. Also: Not monitoring pg_stat_progress_create_index during production CREATE INDEX CONCURRENTLY — a stalled concurrent build holds a ShareUpdateExclusiveLock on the table, which blocks DDL but not DML; it is easy to forget a background index build is running. Also: Reading index_relid::regclass during the initializing phase — the index OID may not yet be visible in the catalog at the very start of the build, causing a regclass cast error; guard with a phase filter or use index_relid directly. Also: Ignoring tuples_done relative to pg_stat_user_tables.n_live_tup — if tuples_done stalls far below the expected live tuple count, the index build may be stuck waiting for visibility or encountering errors; cross-check with pg_stat_activity for error messages.

✓ Instead: See description and related functions for the correct approach.

Polling pg_stat_progress_vacuum and seeing no rows and concluding vacuum is not running — autovacuum workers also appear in this view only while actively vacuuming; a just-finished or about-to-start worker produces no row. Also: Using heap_blks_scanned / heap_blks_total as the only completion estimate — a vacuum with many dead tuples may linger in the 'vacuuming indexes' phase long after finishing the heap scan; track phase transitions, not just block progress. Also: Cancelling a VACUUM because it appears stuck in 'vacuuming indexes' on a large table — index vacuuming on a GIN or multi-column index can legitimately take many minutes; killing it restarts from the beginning on the next run. Also: Not checking pid against pg_stat_activity — pg_stat_progress_vacuum shows the backend pid; joining to pg_stat_activity confirms whether it is an autovacuum worker or a manual VACUUM and shows how long it has been running. Also: Ignoring num_dead_item_ids accumulating without heap_blks_vacuumed advancing — this indicates the vacuum is collecting dead item IDs but the maintenance_work_mem is full before it can do an index pass; consider increasing maintenance_work_mem for large tables.

✓ Instead: See description and related functions for the correct approach.

Relying solely on replay_lag (an interval) without also checking byte lag — during low-write periods, interval lag can grow large even though the standby is fully caught up in bytes; always check pg_wal_lsn_diff(sent_lsn, replay_lsn) alongside the interval. Also: Not alerting when pg_stat_replication has zero rows — if all standbys disconnect, the view becomes empty; monitoring that polls it must treat an empty result as a critical alert, not just 'no lag'. Also: Confusing sent_lsn with flush_lsn for durability guarantees — data is durable on the standby only after flush_lsn; synchronous_standby_names must reference flush position for true synchronous replication guarantees. Also: Ignoring the state column — a standby in 'catchup' state is replaying archived WAL, not streaming; it may be significantly behind even if replay_lag looks small because it is processing older segments. Also: Using pg_stat_replication on the standby — the view is populated only on the primary; on the standby, use pg_stat_wal_receiver instead.

✓ Instead: See description and related functions for the correct approach.

Querying pg_stat_statements without the extension enabled — the view simply won't exist; verify with 'SELECT * FROM pg_extension WHERE extname = ''pg_stat_statements'';' before relying on it. Also: Not resetting pg_stat_statements periodically — cumulative stats since server start dilute signal from recent changes; call SELECT pg_stat_statements_reset() before load tests or after major schema changes to get a clean window. Also: Sorting only by mean_exec_time and ignoring total_exec_time — a query called millions of times with a 1 ms average costs more overall than a rare 500 ms query; always consider both dimensions. Also: Treating pg_stat_statements query text as exact SQL — parameters are normalized into $1, $2 placeholders; use queryid to correlate, not substring matching on the query column. Also: Ignoring shared_blks_read in favor of timing alone — a fast query that hammers disk reads will degrade under load; cross-check mean_exec_time with shared_blks_read / calls to find cache-cold queries.

✓ Instead: See description and related functions for the correct approach.

Dropping an index solely because idx_scan = 0 without checking when the server was last restarted — pg_stat_user_indexes resets on restart; an index that was actively used before a recent restart will show zero scans. Also: Ignoring indexes that back unique constraints or primary keys when hunting unused indexes — these are enforced at write time, not via idx_scan; dropping them destroys the constraint. Also: Not distinguishing idx_tup_read from idx_tup_fetch — idx_tup_read counts index entries visited; idx_tup_fetch counts heap rows returned. A large gap suggests many dead tuples in the index or a visibility-map issue. Also: Dropping multiple indexes simultaneously on a production table — each DROP INDEX CONCURRENTLY is safe alone, but dropping several at once removes write-amplification protection before you can measure the impact; remove one at a time and monitor. Also: Evaluating index usage without cross-referencing pg_statio_user_indexes — an index may scan infrequently but serve critical queries; low idx_blks_read alongside low idx_scan often means the index fits in cache and is working well.

✓ Instead: See description and related functions for the correct approach.

Relying on n_live_tup instead of COUNT(*) for exact row counts — n_live_tup is a planner estimate updated by vacuum/analyze and can be significantly off on heavily-modified tables; use COUNT(*) when an exact figure is needed. Also: Treating last_autovacuum as confirmation that autovacuum is healthy — autovacuum may have run but been killed by lock contention or cost-delay throttling before completing; check n_dead_tup after the timestamp to confirm progress. Also: Ignoring seq_scan on large tables — even occasional full scans on multi-million-row tables are serious; an idx_scan / (seq_scan + idx_scan) ratio below 95% for large tables warrants index investigation. Also: Not differentiating between last_vacuum (manual) and last_autovacuum (daemon) — a table may have last_vacuum set from a one-off run while autovacuum is silently excluded via autovacuum_enabled = false. Also: Comparing n_dead_tup across tables without considering table size — 10,000 dead tuples on a 100-row table is catastrophic; always compute dead_pct = n_dead_tup / nullif(n_live_tup + n_dead_tup, 0).

✓ Instead: See description and related functions for the correct approach.

Reading wal_bytes as a snapshot without capturing a delta — all values are cumulative since last reset; to compute a rate, record two readings separated by a known time interval and divide the difference. Also: Ignoring wal_fpw (full-page writes) relative to wal_records — a very high wal_fpw / wal_records ratio indicates checkpoint frequency is too high; full-page writes inflate WAL volume significantly and increasing checkpoint_completion_target can reduce them. Also: Not monitoring wal_buffers_full — when wal_buffers_full increments, WAL buffers are flushing prematurely because wal_buffers is undersized; this causes synchronous I/O that hurts write latency. Also: Querying pg_stat_wal on PostgreSQL versions before 14 — the view does not exist; check server_version_num >= 140000 before including it in monitoring queries. Also: Using pg_stat_wal without correlating with pg_stat_bgwriter checkpoint data — WAL volume must be interpreted alongside checkpoint frequency; high WAL bytes with few forced checkpoints suggests large transactions, while high bytes plus many forced checkpoints points to insufficient max_wal_size.

✓ Instead: See description and related functions for the correct approach.

Querying pg_stat_wal_receiver on the primary — the view is populated only on standbys; it returns zero rows on a primary server, which can be mistaken for a replication problem. Also: Treating a stale last_msg_receipt_time as the only replication health signal — the WAL receiver sends periodic keepalives; a briefly stale timestamp during high-load periods is normal, but silence beyond wal_receiver_timeout indicates a broken connection. Also: Ignoring the received_lsn vs pg_last_wal_replay_lsn gap — received_lsn shows how much WAL has arrived; pg_last_wal_replay_lsn shows how much has been applied. A large gap means the replay process is falling behind. Also: Not checking pg_stat_wal_receiver when replication appears to lag per pg_stat_replication — the primary view shows sent LSN, but the standby may have received it without yet replaying; always cross-check both views. Also: Assuming status = 'streaming' means no lag — the standby can be streaming current WAL but still have a replay backlog; combine with pg_is_wal_replay_paused() and replay LSN comparisons.

✓ Instead: See description and related functions for the correct approach.

Evaluating index cache health using only idx_blks_hit / (idx_blks_hit + idx_blks_read) without checking idx_scan from pg_stat_user_indexes — a rarely-used index will show 100% cache hit simply because it is almost never accessed; cross-reference scan counts. Also: Treating idx_blks_read = 0 as proof that an index fits in cache — if the index is never scanned, it generates zero reads regardless of size; confirm with pg_stat_user_indexes.idx_scan > 0. Also: Ignoring TOAST index I/O — pg_statio_user_indexes covers the main table indexes but TOAST table indexes appear under pg_statio_all_indexes; for tables with large text/JSON columns, check TOAST I/O separately. Also: Not pairing pg_statio_user_indexes with pg_statio_user_tables — an index may appear efficient while the heap it points to has a poor cache hit rate, making the overall access pattern still disk-bound. Also: Resetting pg_stat_reset() without documenting the reset time — both pg_statio views reset with pg_stat_reset(); data collected after a reset is incomparable to pre-reset data, and failure to track reset events makes trend analysis unreliable.

✓ Instead: See description and related functions for the correct approach.

Computing cache hit ratio with heap_blks_hit / (heap_blks_hit + heap_blks_read) without handling zero denominators — tables with zero I/O return NULL; always wrap the denominator with NULLIF to avoid division-by-zero errors. Also: Looking only at heap_blks_read and ignoring idx_blks_read — a table can have a high heap cache hit rate while its indexes are causing significant disk reads; always inspect both columns together. Also: Drawing conclusions from a single snapshot — blks_read accumulates over the server lifetime; a table that was cold-started long ago may show high reads that are no longer occurring. Sample twice and compare the delta. Also: Not cross-referencing with pg_stat_user_tables.seq_scan — a table with many heap_blks_read alongside a high seq_scan count is doing repeated full scans; the fix is an index, not more cache. Also: Assuming low heap_blks_read means the table is small — the OS page cache and the kernel's own buffering can satisfy reads before PostgreSQL counts them; heap_blks_read reflects only hits and misses against PostgreSQL's shared_buffers.

✓ Instead: See description and related functions for the correct approach.