Postgres amcheck: Detecting Data Corruption Before Backups Get Poisoned

Most database failure modes announce themselves. A disk fills and writes start failing. A connection limit hits and clients see errors. A slow query crosses a timeout and pages someone. The team notices, opens an incident, and recovers.

Silent data corruption does not announce itself. A bad sector flips a bit in a B-tree page that has not been read in three weeks. The replica gets the bad block from the primary because streaming replication is physical. Backups copy the bad block to S3 every night. The corruption sits in the database, in the standby, and in every backup for as long as the team takes to notice. Sometimes that is a query that suddenly returns wrong results. Sometimes it is a unique-constraint violation on data that should have been deduplicated. Sometimes it is an index scan that returns rows that no longer exist. By the time the team notices, the oldest clean backup is gone.

What amcheck does

The amcheck extension shipping with Postgres since 10 provides corruption-detection functions that walk database structures and report inconsistencies. The two primary functions are bt_index_check which checks B-tree index structural integrity against the index itself and bt_index_parent_check which additionally checks that index entries reference valid heap tuples requiring an ACCESS EXCLUSIVE lock. The 14+ addition verify_heapam walks heap relations and reports tuples with inconsistent xmin/xmax visibility or invalid TOAST pointers or unreadable lines.

Each function returns nothing on success and raises an error or returns problem rows on failure. The check is read-only against the data, but it walks the entire structure and competes for buffer cache with normal workload. A full check of a 500GB index can take 30 minutes and substantially affect cache hit ratios for that window.

The pg_amcheck wrapper

Postgres 14 added the pg_amcheck command-line tool that wraps the SQL functions with cluster-wide, parallel, filtered execution. The wrapper takes a database connection plus filters for which databases, schemas, tables, and indexes to check, runs multiple workers in parallel, and reports errors in a structured format suitable for monitoring pipelines.

pg_amcheck --all --parent-check --heapallindexed --jobs=4 \
  --skip=skip-corrupt --no-dependent-toast \
  --progress 2> amcheck.log

The flags worth knowing: --all covers every database in the cluster. --parent-check upgrades to the stronger check requiring ACCESS EXCLUSIVE on indexes. --heapallindexed verifies every heap tuple is referenced by every index that should reference it. --jobs=N runs N parallel workers. --skip=all-frozen and --skip=all-visible skip already-frozen or all-visible pages for a faster but less-thorough scan. The output is one line per error with a structured format that monitoring pipelines can parse.

What corruption looks like

The errors produced by amcheck cluster into a few categories. B-tree index errors include "could not find tuple in heap" when an index entry points at a missing heap row, "heap tuple version chain broken" when the xmin/xmax chain has a gap, and "out of order index tuples" when the sort order within a page is violated. Heap errors include "tuple xmin invalid" when the inserting transaction ID falls outside the allowed range, "TOAST chunk missing" when an external value is referenced but the chunk is not in the TOAST table, and "could not read page" when the page checksum fails. Each error type points at a different underlying mechanism — hardware bit flip, filesystem bug, partial-write tearing, Postgres bug, or operational mistake like deleting WAL during a basebackup.

The cadence that catches corruption early

The right cadence trades thoroughness against cost. The pattern that works well for most production databases: a fast nightly check that exercises a sample of recently-modified pages, a weekly full check that exercises every index and heap in the cluster, and a quarterly check that includes the parent-check upgrade requiring brief ACCESS EXCLUSIVE locks during a maintenance window. The fast nightly check catches new corruption within 24 hours. The weekly full check catches anything the sampling missed. The quarterly parent-check catches the deeper cross-index inconsistencies that the lighter checks cannot see.

The discipline that matters most: every check failure goes to the same alert pipeline as primary database failure. Silent corruption found and silently logged is corruption that does not get acted on. The 3am page is the only mechanism that reliably converts an amcheck error into a corruption response before backups expire.

What to do when corruption is found

The response pattern depends on what amcheck found. For a single corrupted index with an intact heap, the fix is REINDEX CONCURRENTLY which rebuilds the index from the heap and replaces the corrupt index atomically. For a corrupted heap with intact indexes, the fix is harder: identify the corrupt tuples via the verify_heapam output, decide whether to restore from backup or to manually patch via direct UPDATE with the corrupt rows excluded, and accept that some data may be permanently lost.

For corruption on a primary that has propagated to replicas, the fix has to include the replicas. Streaming replication is physical, so replicas have the same bad blocks at the same locations. The recovery is either to take a fresh basebackup from a known-good source and rebuild the replicas, or to fix the corruption on the primary via REINDEX or restore and let the replicas pull the fix via WAL.

What amcheck does not solve

The extension catches corruption it can see by walking structures. It does not catch corruption in TOAST tables outside what verify_heapam exercises. It does not catch logical-level corruption from application bugs that wrote semantically-wrong data through completely valid SQL. It does not catch corruption on standbys that have not been individually checked because amcheck operates per-connection and a primary check does not exercise standby data. It does not catch hardware-level corruption that affects WAL replay before the corruption ever lands in heap or index — that requires page-level checksums via the data_checksums initdb option and is one of the strongest reasons to run with checksums enabled despite the small CPU cost.

The combination that catches the most corruption is data_checksums enabled at initdb plus nightly amcheck plus quarterly basebackup restore drill to a separate environment with full query verification. Each layer catches what the others miss. The cost is real — a few percent CPU plus operational time for the drills plus disk for the verification environment — but the alternative is corruption that compounds through every backup until the team notices.

This is the operational pattern we use across DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay). All four currently run SQLite, where the equivalent discipline is the PRAGMA integrity_check and PRAGMA quick_check commands run nightly with the same alert-pipeline integration. When we migrate to Postgres, amcheck plus checksums plus restore drills will be part of the launch checklist, not a thing we add after the first corruption incident.

Database operational features divide into two categories: things you use every day and notice when they break, and things you almost never use but cannot recover without. The corruption-detection family is firmly in the second category. The pattern of teams who get it right is the same pattern across most operational concerns: build the habit when there is no incident, so the habit is in place when the incident happens.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.