Alerting that is actionable · PostgreSQL

Every view in this part can be graphed. Very few should page anyone.

The test

An alert is worth having if a human must do something and there is something they can do. Everything else is a dashboard.

	Alert	Dashboard
Purpose	Someone must act now	Understanding, and investigation
Trigger	A symptom users feel, or a hard limit approaching	Anything
Failure	Nobody acts	Nobody looks — which is fine

The alerts worth having

Grouped by what they mean.

The cluster is unavailable or about to be

-- can we connect and run a query at all?
SELECT 1;

Connection failure. The most important alert, and it must not depend on the database’s own monitoring.
pg_wal filesystem above 75%. Lesson XII-03’s outage. Alert on pg_wal separately from the data directory.
Data filesystem above 85%.
Connections above 80% of max_connections. Lesson IV-02.

Replication is not protecting you

SELECT count(*) AS connected FROM pg_stat_replication;

Fewer standbys connected than expected. Zero rows produces no alert from a naive aggregate — lesson XIV-04.
replay_lag above the RPO. Measured baseline: 2.5 ms healthy, 30 s when replay was blocked.
Any slot with wal_status not reserved, or safe_wal_size falling. Lesson XIV-06 measured 56 MB → 28 MB → NULL.
Any inactive replication slot. Lesson XII-03’s classic outage.

Maintenance is failing

age(datfrozenxid) above 1 billion. Wraparound, lesson VIII-05. The autovacuum failsafe is at 1.6 billion by default, and the point of alerting at 1 billion is to act long before it.
Autovacuum not completing on a table for far longer than its churn implies.
pg_stat_archiver.failed_count increasing, or last_archived_time falling behind. Lesson XIII-05.

Something is wrong with the data

checksum_failures non-zero. Ever. Part XVIII.
deadlocks increasing. Part IX.

Users are suffering

Query latency at the application, which is the only measure that reflects what users experience.
temp_files increasing. Lesson XI-05.
sessions_fatal or sessions_killed climbing. Lesson XVI-03.

Alerts that reliably produce noise

Cache hit ratio below 99%. Lesson XVI-03: a page-cache hit counts as a miss, and a cluster doing large scans is healthy with a low ratio.

Any single slow query. One slow query is normal. A change in the rate of slow queries is a signal.

CPU above 80%. A database using the CPU it was bought for is working. Alert on what users experience instead.

Connection count above a fixed number, without reference to max_connections or to what is normal.

Replication lag on an idle cluster. The lag columns are NULL, which is health — lesson XIV-04’s monitoring bug.

Disk usage above a fixed percentage on a volume that is meant to be full, such as a dedicated archive volume with retention.

Where the alert should come from, and the trap of monitoring from inside

A subtlety that decides whether alerting works at all: the check must not share a failure domain with the thing it checks.

A monitoring agent running on the database host cannot report that the host is down. An alerting pipeline whose state lives in the database it monitors goes quiet exactly when the database has a problem. A check that connects through the same proxy the application uses cannot distinguish a database failure from a proxy failure.

Three layers, and each catches what the others cannot.

From outside, as a client. Connect and run SELECT 1 from where the application connects from. This is the only check that tests the whole path — network, proxy, authentication, connection availability — and it is the closest thing to what users experience. It is also the check most likely to be missing, because it requires infrastructure outside the database.

From the host. Filesystem usage, process liveness, memory pressure. Catches conditions the database cannot report about itself, especially a full pg_wal before it becomes an outage.

From inside the database. Everything in this part. Rich, precise, and unavailable exactly when things are worst.

The layering has a corollary worth stating: an absence of alerts is not evidence of health unless something proves the alerting works. A dead-man’s-switch — an alert that fires when the monitoring stops reporting — is the only construction that distinguishes “everything is fine” from “the monitoring died on Tuesday”.

Without one, the most dangerous state a system can be in is silent, and you cannot tell it apart from the safest.

What to take from this

Alert when a human must act and can. Everything else is a dashboard.
An alert that fires and needs no action is a bug in the alert.
The list is short: availability, filesystem, connections, replication and slots, wraparound age, archiving, checksums, deadlocks, latency, temp files.
Derive thresholds from your own baseline; use rates for cumulative counters.
Known noise: cache hit ratio, single slow queries, CPU percentage, NULL lag on an idle cluster.
Check from outside, from the host, and from inside — the outside check is the one usually missing.
Add a dead-man’s-switch, or silence is indistinguishable from health.

Cross-course references

Observability for Production Sysadmins — Part XVIII (Alerting rules), Part XX (Alert quality) and Part XXII (SLO-based alerting) cover the same discipline in depth: an alert that fires and needs no action is a defect in the alert.
Linux for Production Sysadmins — Part LXVIII (Cluster incident response) covers what happens to a team that has learned to ignore a page.