Monitoring

Postgres exposes almost everything it's doing through catalog views — plain tables you SELECT from, no separate tooling required to get started.

pg_stat_activity — what's running right now

SELECT pid, usename, state, wait_event_type, wait_event,
     now() - query_start AS running_for, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY running_for DESC;

This is the first table to check when something feels stuck: state (active, idle, idle in transaction), how long a query has been running, and — if it's waiting — what it's waiting on (see Locks for wait_event_type = 'Lock'). Found a runaway query?

SELECT pg_cancel_backend(1234);   -- politely ask a query to stop
SELECT pg_terminate_backend(1234); -- forcibly kill the backend

Common mistake

idle in transaction is a special kind of dangerous: a connection sitting in an open transaction, doing nothing, still holds whatever locks and MVCC snapshot it acquired. It's a common cause of both blocked queries and — because Postgres can't clean up rows newer than the oldest open snapshot — table bloat.

pg_stat_database and pg_locks

pg_stat_database gives per-database counters — cache hit ratio, transactions committed/rolled back, deadlocks — good for a single "is this database healthy" glance:

SELECT datname,
     round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 1) AS cache_hit_pct,
     xact_commit, xact_rollback, deadlocks
FROM pg_stat_database
WHERE datname = 'learning';

pg_locks is the raw lock table underneath pg_stat_activity's wait_event column — join it to itself to see exactly which backend is blocking which (see Locks & Deadlocks for the full query).

pg_stat_user_tables — the missing-index signal

Per-table access patterns are one of the most actionable views in the whole catalog:

SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 10;

A table with a high seq_scan count and a large n_live_tup (row count) but a low idx_scan count is a table the planner keeps deciding to scan sequentially — either it's genuinely small enough that a scan is cheaper, or it's missing an index that would make the planner choose differently. n_dead_tup climbing steadily is the same bloat signal covered in VACUUM & Autovacuum.

pg_stat_io

Postgres 17 unified what used to be scattered across several views into one: I/O broken down by backend type, I/O object (relation, temp file, WAL), and operation (read/write/extend), including hits vs actual reads.

SELECT backend_type, object, reads, writes, hits
FROM pg_stat_io
WHERE object = 'relation'
ORDER BY reads DESC;

Logs

Catalog views reset on restart and only show recent activity; logs are the durable record. The setting that matters most day-to-day:

log_min_duration_statement = 500   # log any statement over 500ms, with its text

Production note

Set this too low (or to 0, logging everything) on a busy database and the log itself becomes a performance and disk problem. Start high (a few hundred ms) and lower it only while actively hunting a specific slow query.

Prometheus & Grafana

For dashboards and alerting rather than ad-hoc queries, postgres_exporter runs many of the queries above on a schedule and exposes them as Prometheus metrics.

Metrics pipeline

Postgrespg_stat_* views

postgres_exporterruns queries on a timer

Prometheusscrapes + stores

Grafanadashboards + alerts

The same three views above — pg_stat_activity, pg_stat_user_tables, and pg_stat_database — cover the majority of exporter dashboards you'll find; learning to read them directly in psql first makes the dashboards make sense rather than just being colored numbers. Next: turning what you can now see into faster queries — Performance Tuning.