Direct Toast PoC

Hi hackers, Jan

I would like to propose a design and prototype implementation for a new
TOAST format, which we are calling **Direct TOAST**.
Not a big change, just leaving out the index part.
Hopefully this gives TOAST another 25-30 years :)

I have been talking about the need of this at least since having "Fixing
the TOAST" section in my
https://www.pgcon.org/2023/schedule/session/469-postgresql-is-nowhere-near-ready-3-new-directions-to-develop-postgresql/index.html
talk

### A few Caveats:

- This patch is NOT for code review, just to verify the concept
- This patch came to be because I was tuning some pgvector stuff and the
TOAST overhead completely drawned out any tuning effects. So I asked Jetski
to implement Direct Toast
- this implements the simplest possible Direct Toast variant (I have
several more in mind :) )
- the code is fully AI-generated, I have not even looked at it so not meant
for any kind of *code* review (actually I took a very quick peek and saw
that some things are weird)

#### The good parts:

- The patch cleanly aopplies to REL18_STABLE, passes all tests and seems
generally stable
- The patch is under 1200 lines and likely can be shortened from that.
- The expected speedups are indeed there
- non-indexed TOP-N vector queries are 5% - 35% faster (see below)
- a million-rows heavily toasted table `COPY table TO '/dev/null' WITH
(FORMAT BINARY)`
- takes 31 sec with Direct Toast and 67 sec with Plain/old Toast
(with everything in memory, 5GB of shared buffers)
- `SELECT sum(length(t::text)) FROM toasttable AS t` is 22 sec with
direct and 36 sec with plain
- The OID exhaustion at 4B is obviously not there - you can not run out of
tuple ids

As expected, bigger gains show up with larger tables where TOAST index is
of significant size

I plan to run also the pessimal case test where the TOAST index does not
fit even in Linux disk cache and then almost each row may force a disk
access

## Goals

The main goals of this proposal are to significantly improve TOAST write
and read performance, and to address the issue of OID counter exhaustion in
tables with billions of toasted entries.

### The Problem with Plain TOAST

Currently, Plain TOAST relies on logical OIDs (`chunk_id`) to link parent
tuples to toast chunks.
- **On Write**: Generating a unique OID requires scanning the index
(`GetNewOidWithIndex`), and inserting chunks requires inserting entries
into the unique index, causing index write amplification.
- **On Read**: Retrieving a toasted value always requires a B-Tree index
scan on the toast table.
- **OID Exhaustion**: Every toasted value consumes an OID from the shared
32-bit global counter. For systems inserting billions of toasted entries,
this leads to rapid OID wraparound.

### High-Level Design of Direct TOAST

Direct TOAST optimizes this by a new toast pointer type `VARTAG_DIRECT
storing physical Tuple IDs (TIDs) instead of logical OIDs where possible:

1. **Toast Pointer (`VARTAG_DIRECT`)**: The parent relation stores the TID
of the *final* chunk of the toasted value directly in its toast pointer.
2. **Multi-Page Chunks (`tid[]`)**: For toasted values spanning multiple
pages, the final chunk stores an array of TIDs (`chunk_tids` of type
`tid[]`) pointing to all preceding chunks. Any page referenced in the array
can recursively contain its own `tid[]` array, allowing hierarchical tree
structures.
3. **Zero-OID Chunks**: Direct TOAST chunks are written with `chunk_id =
InvalidOid (0)`.
4. **Index Skip on Write**: We completely skip inserting direct TOAST
chunks into the toast table's unique index during writes.
5. **Partial Index**: The toast table's primary key index is created as a
partial index: `UNIQUE INDEX ... WHERE chunk_id <> 0`. This excludes direct
chunks from the index, allowing `REINDEX` to rebuild the index from the
heap without unique constraint violations on duplicate `(0, seq)` entries.

### Advantages
- **Write Performance**: Bypassing OID generation and index inserts yields
significant write path speedups and reduces WAL volume.
- **Read Performance**: Single-page toasted values are retrieved directly
via TID in a single heap fetch, completely bypassing the index. Multi-page
values bypass index lookups for all but the root chunk.
- **No OID Exhaustion**: By using `chunk_id = 0` for direct chunks, we
completely stop consuming OIDs for these entries.

#### Speedup of TOP-N vector queries
For the folowing test query

SELECT * FROM embtabof<DIM> ORDER BY embedding <-> (SELECT embedding from
e384 limit 1) LIMIT N;

The results for plain and direct toast are as follows
+------+---------+---------------+---------+---------+----------+
| Dim | Rows | Table Size | LIM 10 | LIM 100 | LIM 1000 |
+------+---------+---------------+---------+---------+----------+
| 768 | 598,875 | 2,516,844,544 | 1,883 | 1,885 | 1,898 |
| | Direct Toast --> | 1,413 | 1,418 | 1,427 |
| | Speedup --> | 25% | 25% | 25% |
| | | | | | |
| 1536 | 272,750 | 2,276,147,200 | 1,605 | 1,611 | 1,620 |
| | Direct Toast --> | 1,381 | 1,378 | 1,375 |
| | | | 14% | 14% | 15% |
| | | | | | |
| 5376 | 110,762 | 2,284,707,840 | 949 | 1,002 | 1,015 |
| | Direct Toast --> | 893 | 890 | 887 |
| | | | 6% | 11% | 13% |
+------+---------+---------------+---------+---------+----------+
(Top row is Plain Toast)

### Current Implementation Status
I have a working prototype with code written by Jetski against the
`REL_18_STABLE` branch.
- Read/Write paths are fully implemented and integrated.
- Delete path handles recursive deletion of TIDs during parent tuple
cleanup.
- Added GUC `toast_flavour` and table-level storage parameter
`toast_flavour = 'direct' | 'plain'` to control the format.
- All regression tests compile and pass.

### Limitations & Trade-offs
- **No Manual `CLUSTER` on Toast Tables**: Because the toast index is
partial, running `CLUSTER pg_toast.pg_toast_xxx` directly on the toast
table is not supported (fails with `cannot cluster on partial index`).
However, `CLUSTER` on the parent table works normally as it rebuilds the
toast table by swapping files.
- **TID Dependency**: If toast rows are moved (e.g. by `VACUUM FULL`), the
TIDs change. The prototype relies on PostgreSQL's internal table rebuilding
mechanisms to update these pointers during such operations.

### Future plans

There are many more things that can be done once we move to new structure

- substring replacment (would allow replacing full large Large Object
functionality)
- flexible compression - adding info for compression on the TOAST side is
no not limited by the two available bits in toast pointer
- partially updated structured types (JSON, BSON, ...)

### Initially I would love to get discussion going on this design,
particularly regarding:

- The partial index approach to allow duplicate `chunk_id = 0` in the heap.
- The use of `tid[]` arrays for multi-page storage.
- Any concerns about the TID dependency.
- what other changes are needed in addition to disabling direct VACUUM FULL
on TOAST table ?

Regards,
Hannu