2.1Access Statistics Views

When a report comes in that "the server is slow," the first move is the statistics views, not guesswork. PostgreSQL continuously aggregates and exposes a running server's internal state—connections, lock contention, table access patterns, physical I/O—under the pg_stat_* namespace. Accurately mapping which view reflects what is the starting point of performance monitoring.

2.1.1pg_stat_activity and pg_locks

• pg_stat_activity shows every currently connected session, one row per process: pid, usename, state (active/idle/idle in transaction/etc.), query (the running or most recent SQL). It also exposes wait_event_type (a category such as Lock/LWLock/IO/Client) and wait_event (the specific wait event name), letting you pin down exactly what a session is waiting on.
• pg_locks enumerates lock information held or requested inside the server, one row per lock: locktype, relation, pid, mode (AccessShareLock through AccessExclusiveLock), and granted (true = acquired, false = waiting). Joining it to pg_stat_activity on pid reveals which query is blocked waiting on which lock—the standard combination for deadlock investigation and first-pass lock-contention triage.

2.1.2pg_stat_database, table/I-O stats, archiver, and bgwriter

• pg_stat_database aggregates per database: xact_commit/xact_rollback (commit/rollback counts), blks_read/blks_hit (physical reads vs. buffer hits), deadlocks, and temp_files/temp_bytes (temp-file usage). It is the frequent source for computing the cache hit ratio (blks_hit / (blks_hit + blks_read)).
• pg_stat_all_tables captures per-table access patterns: seq_scan/seq_tup_read (sequential scan count and rows read), idx_scan/idx_tup_fetch (index usage), n_tup_ins/n_tup_upd/n_tup_del, n_dead_tup (dead tuple count—the key input for deciding whether VACUUM is needed), and last_vacuum/last_autovacuum/last_analyze/last_autoanalyze (timestamps of each maintenance operation's last run).
• pg_statio_all_tables is dedicated to physical I/O: heap_blks_read/heap_blks_hit (disk reads vs. cache hits on the table heap) and idx_blks_read/idx_blks_hit (the index side). When you need per-table cache efficiency, this—not pg_stat_all_tables—is the view to consult.
• pg_stat_archiver tracks WAL archiving success/failure (archived_count, last_archived_wal, failed_count, last_failed_wal)—used to detect archive_command failures. pg_stat_bgwriter gives checkpoint/background writer writeback statistics (checkpoints_timed/checkpoints_req = scheduled vs. requested checkpoint counts; buffers_checkpoint/buffers_clean/buffers_backend = breakdown by writer source), which helps judge whether checkpoints are firing too often (a high checkpoints_req hints at an undersized max_wal_size).

Walk through a real triage flow. Given a report that "a specific query never returns," first survey the wait state of every active session with SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state != 'idle';. If wait_event_type is Lock, that session is waiting on some lock, so next join pg_locks to pg_stat_activity on pid and look for rows with granted = false. The pid you find there belongs to the blocking session that is holding a lock while its transaction has not finished. That blocking session's state is often idle in transaction—the classic pattern of "issued BEGIN and forgot to COMMIT/ROLLBACK." Once the cause is identified, the emergency response is to cancel that backend's query with pg_cancel_backend() (Gold G1 scope). If instead wait_event_type is IO, disk I/O itself is likely the bottleneck, so check pg_statio_all_tables for an abnormally high heap_blks_read on the affected table. On the other hand, given a report that "writes to a specific table are slow," check n_dead_tup in pg_stat_all_tables; if dead tuples are piling up and last_autovacuum is stale (autovacuum is falling behind), you can conclude the table needs VACUUM tuning (G1/G3 scope). The key mindset is that a statistics view alone rarely explains the cause—you narrow a hypothesis by combining several views together.

2.1.3Section summary

• pg_stat_activity = per-session (wait_event_type/wait_event identify the wait reason). pg_locks = lock rows (granted=false is the waiting side; join on pid to pg_stat_activity)
• pg_stat_database = per-DB aggregates; pg_stat_all_tables = access counts + n_dead_tup; pg_statio_all_tables = physical I/O only; pg_stat_archiver = WAL archiving success/failure; pg_stat_bgwriter = checkpoint/writeback stats