Checksums, index corruption, and storage failure

The argument for data checksums is usually made in the abstract. Here it is as a measurement.

The experiment

Two clusters on the same host, same PostgreSQL 18.6. One built with initdb --data-checksums, one with --no-data-checksums. Identical table:

CREATE TABLE victim(id int primary key, payload text);
INSERT INTO victim SELECT g, 'row-'||g||'-'||repeat('z',60)
  FROM generate_series(1,20000) g;

Every payload matches ^row-[0-9]+-z+$, so damage is detectable by inspection.

Then, on each, with the cluster stopped, the identical corruption:

printf "CORRUPTX" | dd of=$FILE bs=1 seek=$((8192*5 + 4000)) conv=notrunc

Eight bytes. Same offset. Same block.

With checksums

$ SELECT count(*) FROM victim;

ERROR:  invalid page in block 5 of relation "base/5/16384"

Without checksums

$ SHOW data_checksums; SELECT count(*) FROM victim;

 data_checksums
----------------
off

count
-------
20000          <- NO ERROR. NO WARNING. NOTHING IN THE LOG.

$ SELECT id, payload, length(payload) FROM victim
WHERE payload !~ '^row-[0-9]+-z+$';

 id  |                               payload                                | len
-----+----------------------------------------------------------------------+-----
423 | row-423-zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzCORRUPTXzzzzzzzzzzzzzzzzz |  68

-- what it should have been:
row-423-zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz    68

Enabling checksums

initdb --data-checksums at cluster creation is free from then on, and PostgreSQL 18 enables it by default where earlier versions did not:

$ /usr/lib/postgresql/18/bin/initdb -D /tmp/defaultcs -U postgres
/usr/lib/postgresql/17/bin/initdb -D /tmp/dcs17   -U postgres
pg_controldata ... | grep -i checksum

--- 18.6 ---
Data page checksum version:           1        <- ENABLED

--- 17.11 ---
Data page checksum version:           0        <- DISABLED

On an existing cluster, pg_checksums enables them offline:

pg_ctl stop -m fast
pg_checksums --enable -D $PGDATA     # reads and rewrites every page
pg_ctl start

The cluster must be down for the whole operation, and the operation reads and rewrites every page — hours on a large cluster. That is the cost, and it is a one-off.

# check without changing anything; also works on a backup
pg_checksums --check -D $PGDATA

$ pg_checksums --check -D /var/lib/postgresql/cor

pg_checksums: error: checksum verification failed in file
".../base/5/16384", block 5:
calculated checksum 5A2C but block contains 48DC
Checksum operation completed
Files scanned:   955
Blocks scanned:  3212
Bad checksums:  1
Data checksum version: 1

Index corruption

Indexes are derived data, which makes them a completely different problem from heap corruption:

CREATE EXTENSION amcheck;

-- structural check, takes a SHARE lock
SELECT bt_index_check(index => c.oid, heapallindexed => true)
  FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid
 WHERE c.relam = (SELECT oid FROM pg_am WHERE amname = 'btree')
   AND c.relpersistence = 'p' AND i.indisready AND i.indisvalid;

-- heavier: also checks parent/child relationships. ACCESS EXCLUSIVE-ish
SELECT bt_index_parent_check('myindex', heapallindexed => true);

Both return void on success and raise an ERROR describing the problem on failure.

Storage failure

Checksums detect it. They do not fix it, and they say nothing about where it came from.

# the operating system's view, which usually knows first
dmesg -T | grep -iE 'i/o error|medium error|ata|nvme|scsi'
smartctl -a /dev/nvme0n1

If corruption is appearing in unrelated objects over time, the storage is failing and the sequence is: replace the hardware, then restore. Restoring onto failing storage lands the restored data on the thing that destroyed it.

What to take from this

• Measured, identical corruption on two clusters: with checksums, an error; without, CORRUPTX returned inside a row with no complaint.
• checksum_failures cannot increment on a cluster without checksums.
• Checksums are the default in 18. pg_checksums --enable converts an existing cluster offline, rewriting every page.
• pg_checksums --check names exact blocks and works on a backup.
• Indexes are derived: index corruption means REINDEX, heap corruption means restore.
• Run amcheck on a restored copy — a warm pool can make it pass on a corrupt table.
• Checksums do not cover memory corruption, logical corruption, or meaning. WAL and pg_control have their own CRCs.

Cross-course references

• Linux for Production Sysadmins — Part XIII (Disks and block devices) covers the device-level errors that precede this, and Part XLI (Storage Performance) covers reading SMART and the kernel log for the same event from below.
• Ceph & Distributed Storage — Part LXI (Scrubbing) and Part LXII (Inconsistent PGs) cover the storage layer’s own detection, which is a second independent check when you have it.

Quiz