Re: Direct TOAST v2, faster, smaller and no migration needed

From 105ac823c6c749d3cc586f4ab76464cde94ee5a6 Mon Sep 17 00:00:00 2001
From: Hannu Krosing <hannuk(at)google(dot)com>
Date: Wed, 30 Sep 2026 17:57:53 +0000
Subject: [PATCH v12 00/10] Direct TOAST: Index-free out-of-line
varlena storage via physical TIDs

Hi hackers,

Please find attached v12 of the Direct TOAST patch series, rebased onto
current master (a36e2f7cd21), incorporating native interoperability with
64-bit TOAST value identifiers (toast_value_type = 'oid' / 'oid8'),
index-free self-pruning (LP_UNUSED / direct_toast_self_prune), vectored
multi-block detoasting, and deterministic regression tests and benchmarks
demonstrating Direct TOAST's advantages over both 32-bit oid and 64-bit
oid8 plain TOAST.

=== Overview & Architecture ===

Standard ("Plain") TOAST assigns a logical identifier (chunk_id) to each
out-of-line value and relies on a B-tree index on (chunk_id, chunk_seq) to
locate chunks. While 64-bit oid8 TOAST identifiers eliminate the global OID
wraparound and collision bottleneck, Plain TOAST still incurs:
1. B-tree index insertion, page splits, and WAL overhead on every out-of-line
write (and, for 64-bit oid8, 50% wider 24-byte aligned index tuples vs
16-byte oid index tuples).
2. B-tree root-to-leaf traversal buffer pins on every detoast read.
3. Two-phase vacuuming before dead TOAST heap line pointers (LP_DEAD) can
be recycled to LP_UNUSED, because index entries may still reference
them.

Direct TOAST (VARTAG_DIRECT, 20-byte struct varatt_direct payload, 22-byte
total on-disk pointer datum) eliminates the B-tree lookup and index maintenance
entirely by embedding the physical ItemPointerData (va_tid) of the root
TOAST tuple directly in the external varlena pointer.

When toast_default_flavour = 'plain' (the default) and toast_flavour is not
set to 'direct' on the parent table, newly created TOAST tables use the
traditional 3-column schema (chunk_id, chunk_seq, chunk_data) with a
non-partial primary key index on (chunk_id, chunk_seq).

When toast_default_flavour = 'direct' or when toast_flavour = 'direct' is set
on the parent table, TOAST tables use the 5-column Direct TOAST schema:
- chunk_id (oid or oid8, NULL for Direct TOAST tuples)
- chunk_seq (int4, 0-based sequential chunk index)
- chunk_data (bytea, NULL for pure interior tree nodes)
- chunk_tids (tid[], NULL for leaf chunks)
- chunk_tid_offsets (int8[], NULL for leaf and flat multi-chunk roots)

and a partial unique index:
CREATE UNIQUE INDEX ... ON pg_toast.pg_toast_<relid> (chunk_id,
chunk_seq) WHERE chunk_id IS NOT NULL;

Because chunk_id is NULL for Direct TOAST tuples, the B-tree index is
completely bypassed during Direct TOAST writes, updates, and deletes, while
remaining available for Plain TOAST tuples in the same table. Actual INSERT
and UPDATE operations always follow the parent table's toast_flavour storage
parameter, and setting ALTER TABLE ... SET (toast_flavour = 'direct') on a
table with a 3-column TOAST table automatically upgrades its TOAST table
in-place to the 5-column Direct TOAST format.

=== Three-Tier Storage Layout ===

Depending on the size of the out-of-line value (total_chunks relative to
TOAST_MAX_CHUNK_SIZE), Direct TOAST automatically selects one of three
organizations:

1. Tier 1 — Single-Chunk (total_chunks == 1, <= ~2 KB):
va_tid points directly to the single TOAST tuple (chunk_id = NULL,
chunk_tids = NULL, chunk_tid_offsets = NULL). Reading requires 1 heap
fetch and 0 index fetches.

2. Tier 2 — Flat Multi-Chunk (2 <= total_chunks <= DIRECT_TOAST_TREE_THRESHOLD
[100], up to ~200 KB):
Leaf chunks 0 .. N-2 are written first (chunk_tids = NULL), followed by
root chunk N-1 which stores its own trailing chunk_data slice alongside
chunk_tids = [TID_0 .. TID_{N-2}] (chunk_tid_offsets = NULL). Uniform
chunk sizing allows O(1) offset calculation for substring slice reads.

3. Tier 3 — Hierarchical Tree (total_chunks > 100, up to 1 GB):
Leaf data chunks are written first, followed by interior routing nodes with
fanout up to DIRECT_TOAST_FANOUT (50) and maximum depth bounded by
DIRECT_TOAST_MAX_DEPTH (16). Each interior node stores chunk_data = NULL,
chunk_tids (tid[]), and chunk_tid_offsets (int8[] of count + 1
cumulative byte boundaries), enabling O(log_50 N) binary-search routing for
arbitrary byte-range slice reads.

=== Changes in v12 ===

1. Rebased onto master (a36e2f7cd21) with full oid / oid8 interoperability
and separated GUC vs reloption semantics:
- Integrates with upstream's toast_value_type ('oid' or 'oid8') storage
parameter alongside the toast_flavour ('plain' or 'direct') table
reloption and the toast_default_flavour ('plain' or 'direct') GUC.
- toast_default_flavour controls only whether new TOAST tables are created
in the traditional 3-column format or the 5-column Direct TOAST format
(keeping default catalog and regression table definitions unchanged from
master), whereas actual INSERT/UPDATE operations always use the table's
toast_flavour reloption.
- VACUUM FULL / CLUSTER / REPACK directly on a TOAST table checks the TOAST
table's column format (RelationGetNumberOfAttributes(OldHeap) >= 5).
- Fixed partial index predicate generation in create_toast_table() and
ensure_direct_toast() to inspect the TOAST table's actual chunk_id
attribute type (oid or oid8).

2. Index-free self-pruning (LP_UNUSED) and direct_toast_self_prune:
- In heap_page_prune_and_freeze() (pruneheap.c), dead TOAST tuples with
chunk_id IS NULL (heap_tuple_header_is_unindexed_toast()) cannot be
referenced by the partial index (WHERE chunk_id IS NOT NULL) and never
use HOT chains, allowing them to be pruned directly to LP_UNUSED in a
single pass without waiting for index vacuuming.
- Added the direct_toast_self_prune GUC and reloption (default on),
propagated from parent tables to their TOAST relations in
create_toast_table() and ATExecSetRelOptions().
- When deleting Direct TOAST chunks, candidate free space is recorded in the
TOAST relation's FSM and delete_count is incremented in the per-backend
DirectToastPruneState cache (keyed by TOAST relation OID via
toast_get_prune_state(), so state survives relcache invalidations and
-DRELCACHE_FORCE_RELEASE). During on-access pruning and
RelationGetBufferForTuple() on 5-column Direct TOAST tables, pages that
actually contain deleted Direct TOAST tuples
(toast_page_has_deleted_direct_tuple()) are pruned and recorded in the
FSM, and a bounded per-relation clock-hand probe reclaims space from
recently deleted pages before extending the TOAST relation, with zero
probe overhead on Plain TOAST tables or INSERT-only workloads.

3. Vectored detoasting & fast deletion:
- toast_fetch_datum_direct_slice_recursive() caches buffer pins per
tree depth (DirectToastLevelBufState), extracts chunk attributes in-place
via fastgetattr() without per-chunk TupleTableSlot allocations, and
prefetches contiguous multi-block leaf runs using StartReadBuffers() /
WaitReadBuffers() (up to io_combine_limit).
- toast_delete_datum_direct_recursive() replaces TupleTableSlot allocations
with heap_fetch() and fastgetattr(), and deletes Tier 2 leaf chunks
directly by TID via toast_direct_delete_tid() without fetching them first.

4. In-place 3-column TOAST upgrade & amcheck verification:
- ensure_direct_toast() / pg_ensure_direct_toast() (commit 0007) upgrades
3-column TOAST tables in-place to the 5-column Direct TOAST schema and
partial index without table rewrites (and runs automatically when
ALTER TABLE ... SET (toast_flavour = 'direct') is executed), preserving
readability of existing Plain TOAST pointers alongside new Direct TOAST
pointers.
- verify_heapam (contrib/amcheck, commit 0005) validates Direct TOAST
pointers (va_toastrelid, va_tid, size metadata) and recursively verifies
the Direct TOAST chunk graph (chunk_id IS NULL, chunk_seq >= 0,
chunk_tid_offsets monotonicity, child visibility, and exact byte size).

5. Four deterministic performance improvement regression tests in
direct_toast.sql:
- Performance Improvement Test 1 (Write Amplification, TOAST Index
Size & WAL Volume):
Compares plain (oid), plain (oid8), and direct on identical bulk inserts.
Verifies that the Direct TOAST partial index remains a single empty 8 KB
header page (pg_relation_size = 8192) while Plain oid and oid8 indexes
grow (idx_oid8_sz >= idx_oid_sz > 8192), and that Direct TOAST generates
fewer backend-local non-FPI WAL records and bytes (EXPLAIN (ANALYZE, WAL)).
- Performance Improvement Test 2 (Read & Slice Buffer Access
Efficiency Across All 3 Tiers):
Measured via pg_stat_get_xact_blocks_fetched /
pg_stat_get_xact_idx_blocks_fetched:
* Tier 1 (1.6 KB single-chunk) full read: Direct pins 1 TOAST heap block +
0 index blocks (vs Plain 1 heap + >= 1 index blocks).
* Tier 2 (22.4 KB flat multi-chunk) 500B prefix slice: Direct pins 2 TOAST
heap blocks (root + 1 leaf) and 0 index blocks.
* Tier 3 (320 KB tree DAG, 161 leaf chunks) 100B middle slice at offset
150 KB: Direct pins 3 TOAST heap blocks (root -> internal -> target leaf)
and 0 index blocks (53x fewer blocks than a full 161-chunk scan).
- Performance Improvement Test 3 (On-Access LP_UNUSED Self-Pruning
Without VACUUM):
With autovacuum_enabled = off and no VACUUM:
* Across 10 passes of per-row UPDATE churn, direct_toast_self_prune = on
maintains a TOAST table > 4x smaller than direct_toast_self_prune = off,
plain (oid), and plain (oid8), with a 1-page (8 KB) TOAST index.
* Across 6 passes of full-table single-transaction batch UPDATEs,
direct_toast_self_prune = on achieves zero steady-state growth
(sz_batch6 <= sz_batch1).
* Verifies that Plain TOAST tables (oid and oid8) do not incur 256-block
clock-hand probe amplification on extension.
- Performance Improvement Test 4 (Single-Pass VACUUM Without Index Scans):
After deleting all rows, VACUUM (INDEX_CLEANUP OFF) reclaims all dead
unindexed Direct TOAST tuples directly to LP_UNUSED during the first heap
pass and truncates the TOAST relation to 0 bytes, whereas plain (oid) and
plain (oid8) can only mark chunks LP_DEAD in the first pass and remain
> 500 KB without index cleanup and a second heap pass.

=== Benchmark Summary (Plain oid vs Plain oid8 vs Direct TOAST) ===

1. 30-Minute (1,800s) Parallel pgbench Multi-Column Insert Benchmark
(8 clients, 4 threads, shared_buffers = 16MB, synchronous_commit = off,
autovacuum = off; 2 KB inline pad + 8 x 100B STORAGE EXTERNAL columns =
8 TOAST chunks per row):
- plain (oid): 14,653.6 TPS (0.545 ms) | 26.38M rows (211.0M chunks) |
TOAST heap: 28 GB | TOAST index: 4,520 MB (16.1% of TOAST heap) |
1.32B TOAST index buffer hits (50.0 index pins/row)
- plain (oid8): 18,144.3 TPS (0.440 ms) | 32.66M rows (261.3M chunks) |
TOAST heap: 36 GB | TOAST index: 7,848 MB (21.8% of TOAST heap) |
604.8M TOAST index buffer hits (18.5 index pins/row)
- direct: 26,007.3 TPS (0.306 ms) | 46.81M rows (374.5M chunks) |
TOAST heap: 49 GB | TOAST index: 8,192 B (1 empty metapage) |
0 TOAST index buffer hits (0 index pins/row)
-> Direct TOAST sustains 1.77x higher insert TPS than plain (oid) and
1.43x higher TPS than plain (oid8), while eliminating 4.5–7.8 GB of
TOAST B-tree index bloat.

2. High-Churn UPDATE Self-Pruning (autovacuum = off, zero VACUUM calls):
- Single-client 20-pass full-table UPDATE churn (50 rows x 10 KB, 1,000
updates): direct (self_prune = on) maintains 512 kB TOAST heap (64 pages,
1.00x bloat = zero bloat), whereas direct (self_prune = off), plain (oid),
and plain (oid8) bloat to 10 MB (1,313 pages, ~19.1x–19.4x bloat).
- 8-client concurrent pgbench UPDATE churn (500 rows x ~9.6 KB,
autovacuum = off):
* plain (oid): 5,085 TPS (1.573 ms), 213 MB TOAST size
* plain (oid8): 1,535 TPS (5.211 ms), 66 MB TOAST size
* direct (self_prune = on): 29,941 TPS (0.267 ms), 7.2 MB TOAST size
-> Direct TOAST is 5.89x faster than oid (19.5x faster than oid8) with a
29.5x smaller TOAST table.

3. Read & Slice Efficiency Across All 3 Tiers + pgvector MRL subvector Slicing:
- Single-client (10 iterations):
* Tier 1 (1.6 KB x 5,000 rows) full scan: Direct is 3.57x faster than oid
(70.37 ms vs 251.48 ms; 5,000 total TOAST pins vs 15,000).
* pgvector vec1536 (6.1 KB x 2,000 rows): Full detoast (vector_norm) is
1.62x faster (104.07 ms vs 168.22 ms); MRL prefix slice subvector(1..128)
is 2.33x faster (45.27 ms vs 105.54 ms; 4,000 TOAST pins vs 6,000).
* pgvector vec5376 (21.5 KB x 1,000 rows): Full detoast (vector_norm) is
1.25x faster (138.74 ms vs 173.55 ms); MRL prefix slice subvector(1..256)
is 2.04x faster (26.22 ms vs 53.50 ms; 2,000 TOAST pins vs 3,000);
slice-aware vector_dims(emb) is 129x faster than full detoast (1.34 ms,
0 TOAST pins).
* Tier 3 (320 KB Tree DAG x 100 rows) middle slice @150 KB: Direct is
1.53x faster (3.65 ms vs 5.57 ms; 300 heap pins + 0 index pins).
- 8-client concurrent pgbench point reads on vec5376:
* Full vector read: 64,189 TPS (+8.2% vs 59,333 TPS oid / 59,277 TPS oid8).
* MRL subvector(1..256) slice: 70,612 TPS (+4.3% vs 67,715 TPS oid, and
+19.0% vs Plain full read).

4. 10 x 1 GB (10 GB Total, STORAGE EXTERNAL) Bulk Insert & 3-Level Cache Reads
(shared_buffers = 16GB, ~500,000 leaf chunks per 1 GB datum):
- Bulk INSERT (10 x 1 GB):
* plain (oid): 83.550 s (119.7 MB/s), 107 MB TOAST index, 5.24M idx hits,
10,143.5 MiB WAL
* plain (oid8): 83.065 s (120.4 MB/s), 151 MB TOAST index, 5.21M idx hits,
10,208.0 MiB WAL
* direct: 75.225 s (132.9 MB/s), 8,192 B TOAST index, 0 idx hits,
9,912.6 MiB WAL (-10.0% wall time, +11.0% throughput, -231 to
-295 MiB WAL)
- Level 1 Read (Hot shared_buffers + Hot OS page cache):
* 36.952 s (270.6 MB/s) for direct vs 38.502 s (oid) and 38.581 s (oid8).
Subtracting the fixed ~33.2 s UTF-8 length() character-counting CPU
overhead across 10B bytes, pure in-memory detoasting takes ~3.75 s
(2.67 GB/s) vs ~5.30 s (1.89 GB/s) — a 1.41x pure detoasting speedup.
- Level 2 Read (Cold shared_buffers + Hot OS page cache, 100% mincore):
* 45.747 s (218.6 MB/s) for direct vs 48.518 s (oid) and 48.677 s (oid8)
(-5.7% to -6.0% wall time). Vectored multi-block lookahead via
StartReadBuffers() issues 12.4x fewer preadv syscalls (102,396 vs
1,266,336 / 1,274,397, ~100 KB/op) and reduces pg_stat_io read I/O time
by 1.43–1.58 s (-12.9%).
- Level 3 Read (Cold shared_buffers + Cold OS page cache via
POSIX_FADV_DONTNEED, 0% mincore):
* 66.844 s (149.6 MB/s) for direct vs 68.898 s (oid) and 68.742 s (oid8)
(1.90–2.05 s faster end-to-end, 12.4x fewer preadv syscalls).

=== Patch Series Structure ===

0001: Refactor detoasting and decompression pipeline to unify full
and slice operations
0002: Add Direct TOAST catalog, GUC, and reloptions infrastructure
0003: Implement Direct TOAST core storage reading and writing
0004: Support Direct TOAST in logical decoding, replication, and online REPACK
0005: Add amcheck verification for Direct TOAST tuples
0006: Add documentation for Direct TOAST
0007: Add pg_ensure_direct_toast for in-place legacy TOAST table upgrade
0008: Add backend TOAST architecture documentation and clean up detoast access
0009: Prune dead unindexed TOAST tuples directly to LP_UNUSED
0010: Add direct_toast_self_prune, vectored detoasting, and
performance improvement tests

Hannu Krosing (10):
Refactor detoasting and decompression pipeline to unify full and slice
operations
Add Direct TOAST catalog, GUC, and reloptions infrastructure
Implement Direct TOAST core storage reading and writing
Support Direct TOAST in logical decoding, replication, and online
REPACK
Add amcheck verification for Direct TOAST tuples
Add documentation for Direct TOAST
Add pg_ensure_direct_toast for in-place legacy TOAST table upgrade
Add backend TOAST architecture documentation and clean up detoast
access
Prune dead unindexed TOAST tuples directly to LP_UNUSED
Add direct_toast_self_prune, vectored detoasting, and performance
improvement tests

contrib/amcheck/expected/check_heap.out | 29 +
contrib/amcheck/sql/check_heap.sql | 19 +
contrib/amcheck/verify_heapam.c | 302 +++++-
doc/src/sgml/config.sgml | 42 +
doc/src/sgml/ref/create_table.sgml | 40 +
doc/src/sgml/storage.sgml | 155 ++-
src/backend/access/common/README.toast | 124 +++
src/backend/access/common/detoast.c | 920 +++++++++++++----
src/backend/access/common/reloptions.c | 30 +
src/backend/access/common/toast_compression.c | 133 ++-
src/backend/access/common/toast_internals.c | 461 ++++++++-
src/backend/access/heap/hio.c | 113 +++
src/backend/access/heap/pruneheap.c | 86 +-
src/backend/access/table/toast_helper.c | 6 +-
src/backend/catalog/toasting.c | 287 +++++-
src/backend/commands/repack.c | 20 +-
src/backend/commands/tablecmds.c | 34 +
src/backend/replication/logical/decode.c | 7 +-
src/backend/replication/logical/proto.c | 2 +-
.../replication/logical/reorderbuffer.c | 210 +++-
src/backend/replication/pgoutput/pgoutput.c | 4 +-
src/backend/replication/pgrepack/pgrepack.c | 2 +-
src/backend/utils/adt/arrayfuncs.c | 6 +
src/backend/utils/fmgr/README | 4 +-
src/backend/utils/misc/guc_parameters.dat | 13 +
src/backend/utils/misc/guc_tables.c | 7 +
src/backend/utils/misc/postgresql.conf.sample | 2 +
src/include/access/detoast.h | 11 +
src/include/access/toast_internals.h | 51 +
src/include/catalog/pg_proc.dat | 5 +
src/include/catalog/toasting.h | 1 +
src/include/utils/rel.h | 15 +
src/include/varatt.h | 69 +-
src/test/modules/injection_points/Makefile | 1 +
.../expected/repack_direct_toast.out | 276 +++++
src/test/modules/injection_points/meson.build | 1 +
.../specs/repack_direct_toast.spec | 225 +++++
src/test/regress/expected/direct_toast.out | 1066 ++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/direct_toast.sql | 914 +++++++++++++++++
40 files changed, 5324 insertions(+), 371 deletions(-)
create mode 100644 src/backend/access/common/README.toast
create mode 100644
src/test/modules/injection_points/expected/repack_direct_toast.out
create mode 100644
src/test/modules/injection_points/specs/repack_direct_toast.spec
create mode 100644 src/test/regress/expected/direct_toast.out
create mode 100644 src/test/regress/sql/direct_toast.sql

--
2.56.0.rc1.315.gc6ed9934b7-goog

On Wed, Sep 30, 2026 at 11:29 AM Hannu Krosing <hannuk(at)google(dot)com> wrote:
>
> On Wed, Sep 30, 2026 at 4:56 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> >
> > Yes, we've relied on the concept of TOAST tuple "identity" heavily for
> > 20-ish years.
>
> I will investigate an option to include more resiliency data,
> including chunk numbers, in the larger TOAST chunks. They probably
> would be redundant in single-chunk toast
>
> I will also investigate adding a back-pointer to the original tuple,
> which can be handy in some repacking scenarios.
>
> > You could introduce a new behavior based on tids as an opt-in, I
> > guess, leaving the default 4-bytes be, giving access to tid-based
> > access tables for newly-created TOAST tables.
>
> It was already optional and dynamic ALTER TABLE ... WITH (toast_flavour=direct)
>
> It is already implemented for both 4- and 8-byte-oid toast
>
> The latest discussion made me realise that I should also keep the
> table structure as the classic/plain toast with just three fields
> unless direct toast is requested so that the VACUUMing properties stay
> the same .
>
> The extra fields will only be added when you set the direct toast
> option, after that, direct vacuuming of toast will be disabled.
>
> --
> Hannu