Direct Toast PoC - Mailing list pgsql-hackers
| From | Hannu Krosing |
|---|---|
| Subject | Direct Toast PoC |
| Date | |
| Msg-id | CAMT0RQQtLQrY1T2fHCQnH_MZzj9bx0PxbkdOT+f1-wytQZAbaQ@mail.gmail.com Whole thread |
| Responses |
Re: pg_rewind does not rewind diverging timelines
|
| List | pgsql-hackers |
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
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
Attachment
pgsql-hackers by date: