Skip to content

C07 — TOAST Table & Corruption Detection

Two related but distinct concerns grouped together: TOAST (PostgreSQL's mechanism for storing large field values out of line from the main table row) reference integrity, and structural data corruption more broadly — data_checksums, checksum failure counts, and (where the amcheck extension is installed) B-tree structural verification. Corruption found here is worth escalating immediately; these are the checks closest to catching silent data-loss risk before it surfaces as a query error.

9 checks, PGHF07-001 through PGHF07-010 (with gaps where a check has been retired since first shipping). Every check here is PGHF-namespace and built-in — see Metadata Columns Explained for what each field below actually means, and Resetting to Defaults for why these definitions can't be hand-edited in place.

PGHF07-001 — data_checksums

Without data checksums, silent corruption on storage goes completely undetected — the first sign of a problem might be a query returning wrong results rather than an error, long after the underlying corruption occurred.

How to fix

This can't be turned on in place — data_checksums is set at initdb time. To enable it on an existing cluster:

pg_checksums --enable -D /path/to/pgdata   # server must be cleanly stopped first, PG12+

or, on older versions (or if you'd rather not touch the cluster in place), stand up a fresh cluster with initdb --data-checksums and migrate via pg_dump/pg_restore or logical replication.

PGHF07-002 — Checksum failures

Any recorded checksum failure means data corruption was actually detected on disk — this is the alarm PGHF07-001's checksums exist to raise, and it warrants immediate hardware investigation, not a backlog item.

How to fix

Treat this as an active incident:

  1. Check the server log for exactly which relation/block failed (pg_stat_database.checksum_failure_count alone doesn't say which).
  2. Stop writes to the affected object if possible, and investigate the underlying storage (disk/controller/filesystem errors, dmesg, RAID status) — checksum failures are a storage-layer symptom, not a PostgreSQL bug.
  3. Restore the affected relation/database from a known-good backup; do not assume the rest of the cluster is unaffected without further checking (amcheck, PGHF07-007, on nearby objects).

PGHF07-003 — TOAST reference integrity

A table pointing to a TOAST relation that no longer exists in pg_class is system-catalog corruption, not an application-level problem — this typically means restoring from backup, not a routine fix.

How to fix

This is catalog corruption, not something to patch around live:

  1. Do not attempt DDL on the affected table until the extent of the corruption is understood — it can make things worse.
  2. Identify the affected table(s) from the check's output and cross-check with PGHF07-002 for a related checksum failure.
  3. Restore from the most recent known-good backup. If no clean backup exists, engage PostgreSQL support/a specialist before attempting manual catalog surgery — this is not a routine self-service repair.

PGHF07-005 — Orphaned TOAST tables

A TOAST table with no parent table referencing it is the mirror image of PGHF07-003's dangling reference — both point at the same class of catalog corruption and warrant the same investigation.

How to fix

Same remediation as PGHF07-003 — this is catalog corruption; restore from backup rather than attempting a manual fix, unless you've already confirmed with a specialist that the orphaned TOAST table can be safely dropped without affecting other objects.

PGHF07-006 — TOAST size vs main table

TOAST storage disproportionately larger than the main table it belongs to usually means an inefficient data type or encoding choice (uncompressed large text/bytea) — worth knowing before it becomes the dominant share of the table's footprint.

How to fix

For a table flagged in largest_toast_tables:

  1. Check whether the large columns compress well already — PostgreSQL applies pglz (or lz4 on PG14+) TOAST compression automatically; confirm with SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'schema.the_table'::regclass; (x = compressible, e = external/uncompressed).
  2. For a large column that's already incompressible (e.g. pre-compressed binary data like images), consider ALTER TABLE ... ALTER COLUMN col SET STORAGE EXTERNAL to skip the wasted compression attempt, or move it out of the row entirely (object storage + a reference column).
  3. On PG14+, lz4 compresses faster and often better than the default pglz for large text/bytea: ALTER TABLE the_table ALTER COLUMN col SET COMPRESSION lz4; (only affects newly-written values, not existing ones — rewrite the table to apply retroactively).

PGHF07-007 — amcheck index verification

amcheck's bt_index_check() catches structural B-tree corruption — a broken index invariant — before it manifests as wrong query results or an outright query failure, which is far harder to diagnose after the fact.

How to fix

For an index amcheck flags as corrupt:

REINDEX INDEX CONCURRENTLY schema.the_corrupt_index;

then re-run bt_index_check() against it to confirm the rebuild actually resolved it. If a fresh rebuild still fails verification, the corruption may originate below the index (heap-level, or storage) — check PGHF07-009's verify_heapam/pg_check_relation tooling and consider a restore from backup instead of repeatedly rebuilding.

PGHF07-008 — Cache hit ratio (user tables)

A low hit ratio scoped specifically to user tables (as opposed to PGHF03-016's cluster-wide figure) pinpoints whether it's application data specifically, rather than indexes or system catalogs, driving disk I/O.

How to fix

See PGHF03-001/PGHF03-016's remediation — raise shared_buffers if the working set of application tables genuinely doesn't fit; if it does fit but still misses, check for a few oversized sequential scans evicting everything else from the buffer pool (PGHF04-008 for the query, PGHF06-005/PGHF06-007 for a missing supporting index).

PGHF07-009 — Relation integrity probe availability

Without pg_check_relation or verify_heapam available, there's no proactive way to detect heap-level corruption short of a full dump/restore cycle — knowing whether this tooling exists shapes how corruption would even be found.

How to fix

CREATE EXTENSION IF NOT EXISTS amcheck;

verify_heapam() ships as part of amcheck (PG14+) and checks heap-level integrity directly; on Aiven/managed platforms exposing pg_check_relation, that fills the same role. Once installed, periodically run it against key tables rather than only reacting after a checksum failure (PGHF07-002) is already reported.

PGHF07-010 — default_toast_compression

lz4 compresses faster than pglz for large TOASTed values at negligible CPU cost — an easy win that's easy to forget since pglz stays the factory default. Complements the existing PGHF07-006 TOAST-bloat check by suggesting why TOAST might be oversized: this is a leading (configuration) indicator, PGHF07-006 is the lagging (measured) one.

How to fix

ALTER SYSTEM SET default_toast_compression = 'lz4';
SELECT pg_reload_conf();

This only changes the default for new TOASTed values going forward — existing rows keep whatever compression they were written with. To recompress existing large columns, either wait for natural row turnover, or explicitly set per-column compression and rewrite the table:

ALTER TABLE schema.the_table ALTER COLUMN the_column SET COMPRESSION lz4;
VACUUM FULL schema.the_table;  -- or pg_repack, for a table too large/busy to lock

Continue to C08 — Visibility Map Integrity.