The Postgres metrics worth watching

The default database dashboard is CPU, memory, disk and connection count. All four are real, and all four are lagging indicators — by the time CPU is pegged, the thing that caused it has been happening for a while.

These are the metrics that tell you something is going wrong before it becomes an incident, roughly in order of how badly it goes if you ignore them.

Transaction ID age — the one that takes the database offline

Postgres transaction IDs are 32-bit and wrap around. Vacuum freezes old rows to keep the usable range from being exhausted. If vacuum can't keep up — usually because something is blocking it — Postgres shuts down writes entirely to protect your data. Not slow. Refusing writes until you complete an offline vacuum.

SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483648, 1) AS pct_to_wraparound
FROM pg_database ORDER BY xid_age DESC;

-- Alert at 50% (about 1 billion). Page someone at 80%.
-- Under 200 million is normal for a healthy database.

It's rare, and it's catastrophic when it happens, and it always has warning: the number climbs steadily for weeks. Almost always the cause is one of three things holding back the vacuum horizon — a long-running transaction, an abandoned replication slot, or an orphaned prepared transaction.

-- The three usual culprits
SELECT pid, now() - xact_start AS duration, query FROM pg_stat_activity
  WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 5;

SELECT slot_name, active, pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

SELECT gid, prepared FROM pg_prepared_xacts;   -- usually should be empty

Longest idle-in-transaction

A connection that opened a transaction and stopped doing database work holds locks, pins its backend process, and prevents vacuum from cleaning up rows its snapshot can still see. One of these lasting hours is the root cause of a surprising share of database incidents.

SELECT max(extract(epoch from now() - state_change)) AS longest_idle_in_txn
FROM pg_stat_activity WHERE state = 'idle in transaction';

-- Alert above 60 seconds. Nothing legitimate holds a transaction open
-- that long while doing nothing.

The cause is almost always application code doing something slow inside a transaction — calling an external API, processing a file, waiting on a lock. Set `idle_in_transaction_session_timeout` so the database terminates them, then fix the code path that produced them.

Cache hit ratio

SELECT round(100.0 * sum(heap_blks_hit) /
             nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS hit_pct
FROM pg_statio_user_tables;

Above 99% is comfortable, below 95% deserves a look, below 90% means something is wrong. But watch the trend rather than the absolute number — a ratio drifting from 99.5% to 97% over a month is telling you the working set is outgrowing memory, and that's actionable while it's still a graph rather than an outage.

Query time by total, not by average

`pg_stat_statements` is the single most valuable extension for this, and the ordering you choose changes what you find.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       round(100.0 * shared_blks_hit /
             nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 15;

Order by `total_exec_time`, not `mean_exec_time`. A query averaging 900ms and running twice a day is a curiosity. A query averaging 12ms called four hundred times a second is 5 seconds of database time per second, and it is your actual problem. Sorting by mean surfaces the first and hides the second, which is why so many optimisation efforts start in the wrong place.

Dead tuples and bloat

Postgres's MVCC leaves dead row versions behind on update and delete. Vacuum reclaims them. When vacuum falls behind, tables grow, scans read dead rows, and cache holds data nobody wants.

SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC LIMIT 15;

Above 20% dead on a large table means autovacuum isn't keeping up. Look at `last_autovacuum` — if it's null or very old on a busy table, autovacuum is either being blocked or its thresholds are wrong for that table's write rate. High-churn tables often need per-table settings rather than the global default.

Lock waits and deadlocks

-- Who is blocked, and by whom
SELECT w.pid AS waiting, w.query AS waiting_query,
       b.pid AS blocking, b.query AS blocking_query,
       now() - w.query_start AS waited
FROM pg_stat_activity w
JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid) ON true
JOIN pg_stat_activity b ON b.pid = bp.pid
WHERE w.wait_event_type = 'Lock';

-- Deadlocks are counted per database and should be near zero
SELECT datname, deadlocks, temp_files, temp_bytes FROM pg_stat_database;

Any deadlock at all is worth investigating — it means two code paths acquire the same locks in different orders, and that's a bug that will recur under load. `temp_files` in the same view is a useful side signal: a rising count means queries are spilling sorts to disk, which usually means `work_mem` is too low for the workload.

What to actually alert on

Dashboards are for investigation; alerts are for waking people. Keep the second list short or it gets ignored.

• Page: transaction ID age above 80% of the limit. Page: disk above 85%, since a full disk stops writes entirely. Page: the database is unreachable.
• Warn: idle-in-transaction over 60 seconds. Warn: a replication slot inactive for more than a few minutes. Warn: connection count above 80% of the limit.
• Ticket: cache hit ratio trending down week over week. Ticket: any table above 20% dead tuples. Ticket: a new query appearing in the top five by total time.
• Do not alert on: CPU spikes without a latency impact, individual slow queries, or absolute connection counts that are within the pool's expected range.

One caveat about suspended databases. If you run instances that pause when idle — increasingly common for per-customer and preview databases — a suspended instance emits no metrics at all. Any alert configured to fire on missing data will page someone about an entirely expected state. Teach your monitoring the difference between 'not reporting' and 'asleep' before you enable auto-suspend, not after the third false page.

The pattern across all of these: the metrics that matter are about work the database is failing to finish — vacuum falling behind, transactions left open, WAL not being consumed. Resource metrics tell you the consequence. These tell you the cause, and they tell you earlier.