Using amcheck to detect index corruption

Postgres indexes corrupt silently — queries return wrong answers, not errors. amcheck finds it. How to run it and what the errors mean.

Why corruption hides

A corrupt btree doesn't crash; it quietly returns wrong or missing rows for index scans while sequential scans still see everything. Classic causes: glibc collation changes after OS upgrades, fsync lies from storage, and plain bit rot. You will not notice until a customer does.

Running it

CREATE EXTENSION IF NOT EXISTS amcheck;
-- fast, read-only, safe in production:
SELECT bt_index_check(c.oid)
FROM pg_class c JOIN pg_am am ON am.oid = c.relam
WHERE am.amname = 'btree' AND c.relkind = 'i';
-- deeper (takes locks): bt_index_parent_check(oid, true)

Any error output means that index is damaged — REINDEX it, then hunt the cause (check dmesg, recent glibc/ICU upgrades).

Backups inherit corruption

pg_dump reads tables, not indexes, so a dump of a corrupt-index database restores 'fine' — but if the heap itself is damaged, the dump breaks or carries the damage. Running amcheck on the RESTORED copy (as Firedrill does per backup) validates both the backup and, transitively, your source's health, with zero production load.