PostgreSQL TOAST: which checks reach the value?

Same damaged value, different answers

Each fixture contains one bytea value: 24,000 ASCII A bytes and a 45-byte marker. The EXTERNAL strategy stores it without compression in 13 TOAST chunks. After a clean shutdown, we change one marker byte from H to I in the last of four 8,192-byte relation pages. No checksum is recomputed.

The table is generated from recorded outcomes. All clean online controls succeeded and returned the expected complete-value digest. All three executions, each using separate checksum-on and checksum-off clusters, agreed.

With checksums on, the full-value hash query raises a read error: there is no digest to compare. With checksums off, the query succeeds and its digest differs from the expected input. The returned digest also matches an independent Python calculation of the deliberately changed payload.

The offline utility's refusal when checksums are disabled is unavailable verification, not a clean result. It is run only after a clean server shutdown, as required by its documented contract. [4]

The row can describe bytes it does not fetch

TOAST can move large fields to a separate relation and leave an external-value pointer in the main row. That pointer includes size information and the identifiers needed to retrieve the chunks. The main row and complete field are therefore different physical coverage units. [1]

For this uncompressed bytea, octet_length can return the size without reconstructing the value. In the pinned source, byteaoctetlen calls toast_raw_datum_size, whose on-disk external case reads the pointer's stored size. [5] [6]

EXTERNAL substring reads can fetch only the part required. Our 32-byte prefix therefore remains a useful partial-read control, not evidence about every later byte. These optimizations are desirable for ordinary queries; treating them as full-content verification changes the question they answer. [1]

The checksum-off mutation preserves a structurally acceptable chunk and a valid bytea value. amcheck has no copy of the original application payload. Its successful structural checks and the failed expected-digest comparison are compatible results. [2]

Two routes to the damaged page

The two pg_amcheck exclusions are easy to confuse. In our commands, --exclude-toast-pointers removes checks reached through main-table references. --no-dependent-toast removes automatic selection of the dependent TOAST relation. Each route alone encountered the checksum error; excluding both produced exit 0. [3]

All four variants select public.payloads and use --no-dependent-indexes. The labels in the table describe the remaining TOAST routes, not all pg_amcheck defaults. Raw results preserve the exact flags and error output. This observation does not establish that every corruption would be found by either route.

Make the restore check's promise explicit

Record the required object set and version, then separate three outcomes: readable metadata, structurally acceptable storage, and equality to trusted expected content. A fresh digest calculated from already changed data only identifies those changed bytes. It cannot supply the missing original.

Use full-value comparisons where content equality matters, retain the checker's selection and exclusion options, and keep failures distinct from missing checks. Neither a complete hash nor a structural check alone demonstrates freshness, the presence of every required object, or successful application recovery. Continue with the backup verification analysis and checksum identity boundaries.

There is a useful historical parallel, not a PostgreSQL result, in Bairavasundaram and colleagues' FAST 2008 field study. The storage stack used different checks for checksum mismatches, block identity and parity. A RAID scrub did not perform the filesystem's identity check. Their logs also could not confirm every disk's scrub exposure. We use that observation-scope lesson, not the study's fleet rates, to interpret this small experiment. [7]

Reproduce the checks, keep the limits

The dataset and reproduction package contain 120 online executions and 12 offline invocations across 6 fresh clusters. The first two runs are the original experiment; the third is a review rerun of the same harness on the same host, not independent peer review. Sixteen Python tests cover the mutation, expected-content oracle, recorded controls and publication generator.

Runtime: Ubuntu PostgreSQL 16.15. The recorded mutation is at relation byte offset 32,716, page 3 with zero-based numbering. Raw files retain executable and harness hashes, settings, outputs and generation timestamps. The script accepts no existing database and mutates only its own stopped, disposable fixtures. Never adapt it to damage a live or valuable database.

We tested one uncompressed bytea and one chosen payload bit. Compression, encoding, concurrency, damaged pointers, index corruption, checksum collisions, restoration, power loss and hardware faults are outside scope. Probes run in a fixed order; no cold-cache I/O count or performance claim follows. Repetition checks reproducibility, not detection probability or real-world failure frequency.

For the retrieval mechanism, see detoasting. For a related read-selection problem, see Parquet checksum coverage.

Primary sources

1. PostgreSQL 16: TOAST, on-disk storage and EXTERNAL strategy
2. PostgreSQL 16: amcheck, verify_heapam and verification limits
3. PostgreSQL 16: pg_amcheck, relation selection and TOAST-pointer exclusions
4. PostgreSQL 16: pg_checksums, clean-shutdown requirement
5. PostgreSQL REL_16_15: varlena.c, byteaoctetlen and bytea_substring
6. PostgreSQL REL_16_15: detoast.c, toast_raw_datum_size
7. Bairavasundaram et al. An Analysis of Data Corruption in the Storage Stack. FAST 2008, sections 2.2, 3.2 and 4.5