Controlled PostgreSQL observations / synthetic bytea

PostgreSQL TOAST: which checks reach the value?

A controlled one-bit change survives row counts, length and prefix reads; page checksums and trusted payload comparisons reveal different coverage boundaries.

Metadata and hashes / Read the analysis / Published 2026-10-21

Method

Three executions of the same PostgreSQL 16.15 harness on synthetic uncompressed bytea values; separate checksum-on/off clusters, clean controls and a one-bit offline mutation. Each fixture holds one 24,045-byte value in 13 TOAST chunks. The selected bit lies at relation offset 32,716 in page 3 (zero-based); page size is 8,192 bytes. No checksum is recomputed. All clean online probes succeed and the full-value digest equals the independently specified input.

Executions 1 and 2 are the original runs; execution 3 repeats the same harness during publication review. Six fresh clusters yield 120 online executions and 12 offline invocations. The latter include six disabled-checksum refusals, not six successful checks. These are designed cases on one host, not independent random samples.

Damaged-value outcomes

First execution shown; all three agree. Every pg_amcheck variant selects public.payloads and excludes dependent index checks.

A successful hash query can return a digest that fails comparison with expected content. A failed read yields no digest. Exit 0 from a structural checker is not a statement that the bytes match the original application payload. The two pg_amcheck exclusions control pointer checks and dependent-relation selection separately.

Schema

repetition (integer)

Execution 1 or 2; 3 is the review rerun using the same harness.

checksums (boolean)

Checksum setting in a separately initialized cluster.

state (string)

Clean fixture or the subsequent one-bit damaged fixture.

phase (string)

Online probe or offline checksum utility.

probe (string)

Stable probe identifier; raw JSON contains SQL/options.

returncode (integer)

Process exit code, not an application-content verdict.

matches_expected (boolean-or-null)

Only successful full-value hashes: true/false comparison with the trusted input; blank otherwise.

CSV booleans are lowercase true/false. A blank matches_expected field means no digest comparison, including failed hash reads and every non-hash probe. Raw JSON preserves SQL, utility options, stdout, stderr, settings, mutation details and binary identity.

Reproduce

Start with the package manifest and instructions. Keep the listed files together; the metadata hashes cover every downloadable package file. Use Python 3.12 without optimization; no third-party Python package is required.

python3 -m unittest -v test_experiment.py test_publication.py
python3 verify.py
python3 build-publication.py --surface dev --public-root ./rebuild

Raw run 1 / Raw run 2 / Review rerun / Native runner / Table generator / Experiment tests / Publication tests / Original replay / Original two-run summary / Original method / Source mapping / Runtime provenance.

The native runner creates private local-only clusters and mutates only its own stopped fixtures. Never use production data or an existing valuable database. Matching PostgreSQL server/client binaries and the amcheck extension are needed for a native rerun, but not for recorded-data replay.

Limits and reuse

• One payload and selected bit on one Linux host, not a corruption-frequency estimate or exhaustive campaign.
• No compressed values, concurrency, restore operation, power loss or hardware failure was tested.
• The third execution repeats the same harness; it is not independent peer review.

No I/O trace, cold-cache timing, checksum-collision measurement or detection probability is inferred. The original two-run summary deliberately retains its 80 online probes; the publication CSV adds the separately identified review rerun. CC0-1.0 for original Hesela code and synthetic observations; upstream sources retain their licenses.

Related: PostgreSQL TOAST, detoasting and backup verification.

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