Monitoring: activity, waits and key metrics

conceptThe concept

pg_stat_activity has one row per process: state (active, idle, idle in transaction), query, query_start, xact_start, backend_xmin and wait_event_type / wait_event. A session that is active with no wait event is on CPU. Lock waits mean blocking; IO waits mean reading or writing data or WAL; Client waits mean the application is slow to read results or send the next command; LWLock waits point to internal contention.

Stopping work: pg_cancel_backend(pid) cancels the current query; pg_terminate_backend(pid) closes the connection. Guardrails: statement_timeout, idle_in_transaction_session_timeout, lock_timeout and, since 17, transaction_timeout.

Metrics to graph and alert on: active and idle-in-transaction connections, the longest transaction age, replication lag and slot retained WAL, age(datfrozenxid), dead tuples and last autovacuum per big table, cache hit ratio, temp file bytes, checkpoints, and deadlocks. Tools: postgres_exporter with Prometheus and Grafana, pganalyze, PMM, or the DBHelm desktop app.

whyWhy it matters

When the pager goes off, you need to answer "what is the database doing right now and who is blocking whom" within a minute. These queries do that.

Use caseReal-world scenario

Latency spikes at 10:00 every day. pg_stat_activity shows dozens of sessions waiting on Lock: relation behind a scheduled REINDEX that takes ACCESS EXCLUSIVE. Switching to REINDEX CONCURRENTLY ends the spikes.

Try itExamples

Run and edit these in the interactive lesson.

SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS running_for,
       now() - xact_start AS xact_age,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       xact_commit, xact_rollback, deadlocks, temp_files, pg_size_pretty(temp_bytes) AS temp_bytes,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_stat_database
WHERE datname = current_database();

SELECT relname,
       seq_scan, idx_scan,
       n_live_tup, n_dead_tup,
       coalesce(last_autovacuum, last_vacuum) AS last_vacuumed,
       pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables
ORDER BY seq_scan * n_live_tup DESC
LIMIT 8;

SELECT backend_type, object, context, reads, writes, extends, hits
FROM pg_stat_io
WHERE reads > 0 OR writes > 0 OR hits > 0
ORDER BY hits + reads DESC
LIMIT 8;

internalsUnder the hood

Statistics views are fed by the cumulative statistics system (shared memory since 15). track_io_timing = on adds I/O timings to EXPLAIN and pg_stat_statements; pg_stat_io (16) breaks I/O down by backend type and context. Since 18, pg_stat_io also reports bytes, and per-backend I/O and WAL statistics are available.

Avoid / preferDo this, not that

-- alert when cache hit ratio drops below 99%

-- alert on symptoms (latency, errors, saturation), use cache hit ratio to explain them

-- the monitoring agent connects as postgres

CREATE ROLE monitor LOGIN;
GRANT pg_monitor TO monitor;

PitfallsCommon mistakes

• Killing sessions with pg_terminate_backend before understanding them. Cancel first; terminating a big write triggers a long rollback.
• Not setting idle_in_transaction_session_timeout, so a crashed client's open transaction blocks VACUUM for days.
• Alerting on cache hit ratio alone. A 99% ratio can still hide one query that reads gigabytes from disk.

LimitsWhen not to use it

Do not poll heavy catalog queries every second across hundreds of databases; size and bloat estimates can run every few minutes or hourly.

SecuritySecurity notes

Query texts in pg_stat_activity and pg_stat_statements can contain personal data in literals. Restrict who can read them and scrub them before shipping to external monitoring.

ChallengeTry it yourself

SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - xact_start > interval '1 minute';

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Cumulative statistics system
• Wait events