PostgreSQLintermediate

PostgreSQL Monitoring

The system views, extensions and metrics that reveal a PostgreSQL problem before users do.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

PostgreSQL exposes its internal state through system views. Collect from them; host metrics alone cannot tell you why the database is slow.

Enable pg_stat_statements first

shared_preload_libraries = 'pg_stat_statements'   # requires a restart
pg_stat_statements.max = 10000
pg_stat_statements.track = top

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Where the time actually goes: total time, not slowest single execution.
SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Ranking by total time finds the query executed ten thousand times a minute at 8 ms, which costs far more than the nightly report that runs for two minutes.

What to collect

pg_stat_activity

Source: System view

Session count by state and wait event. Alert on idle in transaction sessions older than a minute — they block vacuum and hold locks.

pg_stat_replication

Source: System view (primary)

Per-standby lag. Track both bytes (pg_wal_lsn_diff) and the replay_lag interval; a stalled standby can report a small time lag while bytes grow.

pg_stat_user_tables

Source: System view

n_dead_tup, last_autovacuum, sequential versus index scans per table. The basis for bloat and missing-index alerts.

pg_stat_database

Source: System view

Commits, rollbacks, deadlocks, temp files and blocks read versus hit. Rising temp_bytes means queries are spilling because work_mem is too small for them.

pg_stat_archiver

Source: System view

failed_count and last_archived_time. A stalled archiver silently breaks point-in-time recovery while everything else looks healthy.

age(relfrozenxid)

Source: pg_class

Transaction ID age per table. Alert well before autovacuum_freeze_max_age; wraparound protection stops writes entirely.

Locks and blocking

-- Who is blocking whom, right now.
SELECT blocked.pid          AS blocked_pid,
       blocked.query        AS blocked_query,
       blocking.pid         AS blocking_pid,
       blocking.query       AS blocking_query,
       now() - blocking.xact_start AS blocking_xact_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Logging configuration

log_min_duration_statement = 500ms   # tune to your latency objective
log_checkpoints = on
log_lock_waits = on                  # logs waits beyond deadlock_timeout
log_temp_files = 0                   # every spill to disk
log_autovacuum_min_duration = 0
log_line_prefix = '%m [%p] %q%u@%d app=%a '

log_lock_waits and log_temp_files are the two most frequently omitted, and both point directly at causes that are otherwise invisible.

Exporters

postgres_exporter publishes these views as Prometheus metrics and supports custom queries for anything it does not cover. Grant it a role with pg_monitor — that is enough to read the statistics views without granting data access. See Prometheus Monitoring.