Direct TOAST v2, faster, smaller and no migration needed

Hi Michael, hackers,

Attached is a v2 patch series implementing "Direct TOAST", a new storage format
for out-of-line (TOASTed) variable-length attributes in PostgreSQL.

After a few rounds of reviewing it I finally feel that the code is
reasonably clean for others to take a look.

Michael Re: your concern in earlier discussion about just adding the
direct toast checks directly into code next to
VARATT_IS_EXTERNAL_ONDISK - this is doen this way because I consider
direct toast to be a simplified and streamlined subtype of traditional
toast which just cuts out the index lookup part. This is also
exemplified by zero-downtime / zero-migration switch to direct toast
(and back)

I hoped this will result in less code, but the newly introduced
b-tree-in-toast-table ended up adding enough code that this isn't the
case now

Direct TOAST addresses longstanding write amplification, index contention, and
read latency bottlenecks in the TOAST subsystem by replacing logical OID-based
B-Tree index lookups with direct physical tuple identifier (TID) addressing.

1. Motivation & Background
--------------------------
In traditional PostgreSQL (Plain TOAST):
- Every out-of-line datum allocates a unique 32-bit OID (va_valueid).
- Chunks are stored in an auxiliary relation (pg_toast_<relid>) and indexed
by a B-Tree index on (chunk_id, chunk_seq).
- To read an out-of-line datum, PostgreSQL opens a B-Tree index scan. For small
datums (<= 2KB), reading a single chunk requires navigating 2–3 index buffer
pages before reaching the chunk heap page.
- Writing large attributes causes massive index write amplification: storing a
100-chunk datum requires inserting 100 heap tuples PLUS 100 B-Tree index
tuples, generating corresponding WAL records for each index modification, and
incurring B-Tree page lock contention.
- Generating va_valueid relies on GetNewOidWithIndex() to prevent OID
collisions,
which becomes an operational bottleneck and carries a 2^32 OID
wraparound limit
per database.

Recent discussions on pgsql-hackers (e.g. the 8-Byte TOAST proposal, Commitfest
6747) have focused on widening chunk_id from 32-bit to 64-bit to prevent OID
wraparound. However, widening chunk_id still retains the B-Tree index lookup
model, index write amplification, and WAL churn, while requiring invasive
cluster-wide changes (modifying pg_control, pg_resetwal, and varsup.c).

Direct TOAST takes a different approach: rather than widening the index key,
it eliminates the index lookup entirely for out-of-line chunk access.

1.1 Main advantages

1.1.1 No migration needed - just set toast_flavour=direct and you can
continue with the same toast table
1.1.2 Faster - see next section
1.1.3 Less space used - for huge tables this can mean tebns or
hundreds of gigabytes saved
1.1.4 Faster vacuuming - as there is no TOAST index to vacuum the
vacuums are faster and more lightweight.

1.2 Some test results:

1.2.1 large 25M row table with 64 toasted fields

My tests show that Direct Toast is both faster and saves space.
I ran on a table with 64 toasted fields, first inserting 16 fields and
then updating one of the remaining 3 16-field sets each time.

The initial filling to 25,000,000 tuples was done separately for each
table and the Direct Toast (DT) table was consistently 2x faster.

Then I ran a parallel update test of tables with traditional and
direct toast for 100 hours, updating both tables the same number of
times, and the result was that
- direct toast grew to 380GB
- traditional to 500GB .

Then I ran separate 1 hour runs of updates on top of same tables first
for traditional toast then for direct toast .

The direct toast did 1385 TPS while traditional toast did 635 TPS

1.2.2 pgvector unindexed queries

For top N queries on vectors large enough to be toasted average query
times wete 5% to 30% better for Direct Toast

2. Direct TOAST Architecture
----------------------------

2.1. Physical Pointer Layout (varatt_direct)
We introduce a new on-disk toast pointer tag:
VARTAG_DIRECT = 19

The pointer struct varatt_direct stores:
int32 va_rawsize; /* Original uncompressed data size */
uint32 va_extinfo; /* External stored size + 2
compression bits */
Oid va_toastrelid; /* RelID of TOAST table */
ItemPointerData va_tid; /* Physical TID of root/terminal chunk */

Crucially:
sizeof(varatt_direct) == sizeof(varatt_external) == 18 bytes

Because both pointer formats have the exact same size, parent table tuples
experience zero change in layout, tuple headers, or alignment padding.

2.2. Three-Tier Storage Hierarchy
Depending on value size, Direct TOAST uses three storage tiers:

a) Single Chunk (datum size <= TOAST_MAX_CHUNK_SIZE, ~2KB):
The payload fits in one chunk. The parent tuple's va_tid points directly
to the leaf chunk.
Detoasting requires 0 index lookups and exactly 1 buffer page fetch
(bypassing
B-Tree root, internal, and leaf pages).

b) Flat Multi-Chunk (<= DIRECT_TOAST_TREE_THRESHOLD = 100 chunks, up to ~200KB):
Leaf data chunks are written first. A terminal root chunk contains the last
slice of data in chunk_data and an array of preceding child TIDs in
chunk_tids
(type tid[]). The parent tuple's va_tid points to this root chunk.
Detoasting fetches the root chunk, reads chunk_tids, and directly fetches
each leaf chunk by TID without consulting an index.

c) Hierarchical Tree / DAG (> DIRECT_TOAST_TREE_THRESHOLD = 100 chunks):
For large values, chunks form a multi-level balanced tree with a fanout of
DIRECT_TOAST_FANOUT (50). Intermediate chunks store:
- chunk_tids (tid[]): child chunk TIDs.
- chunk_tid_offsets (int8[]): cumulative byte offsets.
Partial slice fetching (detoast_attr_slice) uses binary search over
chunk_tid_offsets to prune non-overlapping subtrees, achieving O(log N)
buffer lookups without touching an index.

2.3. Zero-Index Writes & WAL Reduction
- Direct TOAST chunks are written with chunk_id = InvalidOid (0).
- Writing direct chunks completely skips index_insert().
- For a 100-chunk write, this eliminates 100 index tuple inserts, eliminates
B-Tree concurrency lock contention, and cuts TOAST WAL volume by ~40–50%.
- The TOAST table index is defined as a partial index:
UNIQUE INDEX ON pg_toast_xxx (chunk_id, chunk_seq)
WHERE chunk_id IS NOT NULL;
This allows existing Plain TOAST rows to be indexed normally while keeping
Direct TOAST chunks out of the index, ensuring REINDEX and VACUUM remain safe.

2.4. Lock-Free In-Place Schema Upgrades
Existing tables can be upgraded from Plain to Direct TOAST on the fly:
ALTER TABLE my_table SET (toast_flavour = 'direct');
or via pg_ensure_direct_toast(reloid).
This performs a metadata-only catalog update adding chunk_tids and
chunk_tid_offsets with fast-default NULLs. No data rewrite or exclusive table
lock is required. Plain and Direct TOAST datums can coexist within the same
table indefinitely.

3. Structure of the Patch Series
--------------------------------

Patch 1: Refactor detoasting pipeline to unify full and slice fetches
Consolidates toast_fetch_datum() and toast_fetch_datum_slice() in detoast.c.
Historically, both functions duplicated buffer allocation, compression header
decoding, and table lifecycle logic. toast_fetch_datum() becomes a
clean inline
wrapper around toast_fetch_datum_slice(attr, 0, -1).

Patch 2: Add Direct TOAST catalog, GUC, and reloptions infrastructure
Defines struct varatt_direct and VARTAG_DIRECT in varatt.h. Adds the
default_toast_flavour GUC ('plain' | 'direct', default 'plain') and the
table storage parameter toast_flavour. Extends create_toast_table() to add
chunk_tids (tid[]) and chunk_tid_offsets (int8[]) to TOAST relations and
creates the partial index predicate (WHERE chunk_id IS NOT NULL).

Patch 3: Implement Direct TOAST core storage reading and writing
Implements the core read, write, slice, and cascaded delete engines:
- Writing: toast_save_datum_direct() implementing single-chunk fast-path,
flat multi-chunk arrays, and hierarchical DAG trees (>100 chunks).
- Reading: detoast.c direct TID fetching, single-chunk heap_fetch
optimization,
and recursive tree traversal with slice boundary pruning.
- Deletion: toast_delete_datum_direct_recursive() cascading deletes by TID.
- Adds comprehensive regression test suite
(src/test/regress/sql/direct_toast.sql).

Patch 4: Support Direct TOAST in logical decoding, replication, and
online REPACK
Adds replication and decoding support:
- reorderbuffer.c keys toast reassembly on tuple TID when chunk_id is NULL,
reconstructing direct TOAST DAGs bottom-up from the WAL stream.
- Supports unchanged and changed direct TOAST attributes in decode.c, proto.c,
and pgoutput.c.
- Adds isolation test specs (repack_direct_toast.spec) using injection points.

Patch 5: Add amcheck verification for Direct TOAST tuples
Extends verify_heapam in contrib/amcheck to validate direct TOAST pointers,
checking that va_tid points to a valid block and offset, verifying
that chunk_id
is NULL, verifying offset monotonicity in tree chunks, and cross-checking byte
counts against va_extinfo.

Patch 6: Add documentation for Direct TOAST
Adds SGML documentation in doc/src/sgml/storage.sgml, config.sgml, and
ref/create_table.sgml detailing Direct TOAST architecture, GUCs, storage
options, and performance considerations.

Patch 7: Add pg_ensure_direct_toast for in-place legacy TOAST table upgrade
Introduces ensure_direct_toast() and
pg_ensure_direct_toast(regclass) to perform
instant, metadata-only catalog upgrades on existing 3-column TOAST tables.
Adds automatic upgrade invocation in ATExecSetRelOptions() when setting
toast_flavour = 'direct'.

Patch 8: Add backend TOAST architecture documentation and clean up
detoast access
- Adds src/backend/access/common/README.toast providing comprehensive backend
architectural documentation for the TOAST subsystem.
- Enriches in-source comments in varatt.h, toast_internals.c, and toasting.c.
- Refactors ToastExternalMetadata in detoast.c to use an anonymous union for
direct_tp and tp, enforcing format mutual exclusivity and reducing stack
footprint.
- Cleans up attribute retrieval in detoast.c.

4. Testing & Verification
-------------------------
The patch series passes:
- Core regression tests: `make -C src/test/regress check-tests
TESTS="direct_toast"`
- Isolation tests: `make -C src/test/modules/injection_points check`
- Integrity checks: `make -C contrib/amcheck check` (SQL and TAP suites)
- Both pg_upgrade --link and pg_dump / pg_restore test matrices.

Feedback, suggestions, and reviews are very welcome!

Regards,
Hannu Krosing