Direct Toast PoC
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
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
Attachments:
direct_toast_rel18stable.diffapplication/x-patch; name=direct_toast_rel18stable.diffDownload+733-20
On Wed, Jul 29, 2026 at 09:13:47PM +0200, Hannu Krosing wrote:
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 am going to be honest here, but I think beginning a thread this way
is sloppy. First, the entire email and its contents are entirely
AI-generated, perhaps you have spent some time double-checking the
contents of the message but it does not give this impression at all;
at least you are being honest regarding the code by saying that you
have *not looked at the code at all*. And even if the code is
generated, looking at the result is the first thing you should try to
do, especially if you are asking people to look at it.
Relying on such tools is a trend these days, and that's fine because
LLM models are tools and they can be terribly efficient when used
well, but it does not change the review and lookup part (Postgres
committers do that). Some people seem to have success with such
tools, which is also fine. But please, if you plan to ask people to
spend some time on what you are doing, the least you can do is to give
the impression that you have yourself spent some time on the actual
code. I cannot speak for others, but beginning a thread this way
discourages me entirely to look at what you have sent.
Okay, spoiler: I did look at the code, and your LLM has just been
hard-coding a new vartag_external, plumbing it into the TOAST
internals without caring about any concept of backward-compatibility.
Posting patches based on HEAD and not a stable branch would be a
better idea, as well, as a starter. I understand that this is a POC,
still.
Please do not take it bad, this is just my opinion. I think that
time is the most important resource we have. It is limited. However,
beginning a thread this way does not give the impression that you
value the time of other people at all.
My 2c.
--
Michael
Hi Hannu!
Haven't looked at the code yet but want to mention - I've tried 'direct'
approach some time ago [1]/messages/by-id/CAN-LCVOh9DRWNqoDUx+Q1ZDM_O3VyX6ctRuEX0mzA+_JTkUKvg@mail.gmail.com
using list-like representation. Despite giving speedup for relatively small
values and does not having
TOAST index table at all, the performance degraded over standard TOAST with
large ones,
and dependency on TIDs have the immediate effect of VACUUM FULL breaking
the overall structure,
so it should be modified accordingly too.
Michael had already answered that, you could check out [1]/messages/by-id/CAN-LCVOh9DRWNqoDUx+Q1ZDM_O3VyX6ctRuEX0mzA+_JTkUKvg@mail.gmail.com for discussion.
[1]: /messages/by-id/CAN-LCVOh9DRWNqoDUx+Q1ZDM_O3VyX6ctRuEX0mzA+_JTkUKvg@mail.gmail.com
/messages/by-id/CAN-LCVOh9DRWNqoDUx+Q1ZDM_O3VyX6ctRuEX0mzA+_JTkUKvg@mail.gmail.com
On Thu, Jul 30, 2026 at 7:41 AM Michael Paquier <michael@paquier.xyz> wrote:
On Wed, Jul 29, 2026 at 09:13:47PM +0200, Hannu Krosing wrote:
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 am going to be honest here, but I think beginning a thread this way
is sloppy. First, the entire email and its contents are entirely
AI-generated, perhaps you have spent some time double-checking the
contents of the message but it does not give this impression at all;
at least you are being honest regarding the code by saying that you
have *not looked at the code at all*. And even if the code is
generated, looking at the result is the first thing you should try to
do, especially if you are asking people to look at it.Relying on such tools is a trend these days, and that's fine because
LLM models are tools and they can be terribly efficient when used
well, but it does not change the review and lookup part (Postgres
committers do that). Some people seem to have success with such
tools, which is also fine. But please, if you plan to ask people to
spend some time on what you are doing, the least you can do is to give
the impression that you have yourself spent some time on the actual
code. I cannot speak for others, but beginning a thread this way
discourages me entirely to look at what you have sent.Okay, spoiler: I did look at the code, and your LLM has just been
hard-coding a new vartag_external, plumbing it into the TOAST
internals without caring about any concept of backward-compatibility.
Posting patches based on HEAD and not a stable branch would be a
better idea, as well, as a starter. I understand that this is a POC,
still.Please do not take it bad, this is just my opinion. I think that
time is the most important resource we have. It is limited. However,
beginning a thread this way does not give the impression that you
value the time of other people at all.My 2c.
--
Michael
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
Hi Michael
You are absolutely right about AI use, and this is also what I
repeatedl;y said at the beginning of the mail - this is an
AI-generated code to verify the concept and approach, not something
that is meant for CODE review.
I am going to be honest here, but I think beginning a thread this way
is sloppy. First, the entire email and its contents are entirely
AI-generated, perhaps you have spent some time double-checking the
contents of the message but it does not give this impression at all;
I added the parts about why I am sharing this now in this form and
that it confirmed the expected performance improvements.
at least you are being honest regarding the code by saying that you
have *not looked at the code at all*. And even if the code is
generated, looking at the result is the first thing you should try to
do, especially if you are asking people to look at it.
I did NOT ask people to look at it :)
I provided it in hope anyone having similar problems can verify it
also solves theirs.
Relying on such tools is a trend these days, and that's fine because
LLM models are tools and they can be terribly efficient when used
well, but it does not change the review and lookup part (Postgres
committers do that). Some people seem to have success with such
tools, which is also fine. But please, if you plan to ask people to
spend some time on what you are doing, the least you can do is to give
the impression that you have yourself spent some time on the actual
code. I cannot speak for others, but beginning a thread this way
discourages me entirely to look at what you have sent.
Again, I thought I was very explicit tat it is not for code review,
just for verification that the concept is workable and does solve the
main issues.
I may have not been very explicit about the worst issue of
TOAST-with-index -- when you get to really large indexes that do not
fit in memory your sequential scan speeds can drop to a few tens of
lines per second because decoding each row can cause several disk
reads.
Okay, spoiler: I did look at the code, and your LLM has just been
hard-coding a new vartag_external, plumbing it into the TOAST
internals without caring about any concept of backward-compatibility.
That direct plumbing was intentional to keep the code small. It was
not meant as refactoring exercise.
But the code is fully backward compatible with existing TOAST which
co-exists happily with old toast, even in the same table. The toast
flavour is chosen at the time the entry is toasted and read path just
sees another type of toast pointer abd processes it accordingly.
Posting patches based on HEAD and not a stable branch would be a
better idea, as well, as a starter. I understand that this is a POC,
still.
The patch was against stable exactly because it was not meant to be
considered for inclusion yet, but for testing, possibly against a
clone of existing database.
Please do not take it bad, this is just my opinion. I think that
time is the most important resource we have. It is limited. However,
beginning a thread this way does not give the impression that you
value the time of other people at all.
I am ver sorry if I caused you to waste you time.
I tried to be explicit that this code was only for verifying the
concept of ditching the toast index.
Re-sending my last reply to the list
On Thu, Jul 30, 2026 at 8:24 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
Hi Hannu!
Haven't looked at the code yet but want to mention - I've tried 'direct' approach some time ago [1]
using list-like representation. Despite giving speedup for relatively small values and does not having
TOAST index table at all, the performance degraded over standard TOAST with large ones,
For a linked list, this degradation was to be expected , as you can
only start requesting the next page once you have fetched the previous
one.
This is why I implemented it as tidarray (or N-ary tree of TID arrays
for larger data), which does not have this fundamental problem.
You get a large span of tids for pages at once, an could be at least
as fast as index scan and possibly faster, as you get the tid array
slightly cheaper
and dependency on TIDs have the immediate effect of VACUUM FULL breaking the overall structure,
so it should be modified accordingly too.
Yes, VACUUM FULL of the toast table is one known thing that has to be
modified for this
OTOH the current behaviour of VACUUM FULL is not even very useful for
fixing heavily out-of-order toast table anyway, as it just compacts
free space but does not do anything about putting the toasted fields
in same order as main heap fields, so your slow queries stay slow even
after VACUUM FULL.
Import Notes
Reply to msg id not found: CAMT0RQR53+T+orLWje+eO7HQuPL-G_X_qaw2sf4kzhEk=di_nA@mail.gmail.com
Hi!
I keep thinking about this. Hannu, have you thought about a mixed approach?
In [1] above Michael mentioned the very serious drawback of using direct
TIDs -
while vacuuming the TOAST table we have to modify the original table as
well,
so I don't see a way to avoid using some kind of index at all. My direct
TOAST
patch was just a PoC to show it is possible.
On Thu, Jul 30, 2026 at 10:30 AM Hannu Krosing <hannuk@google.com> wrote:
Re-sending my last reply to the list
On Thu, Jul 30, 2026 at 8:24 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
Hi Hannu!
Haven't looked at the code yet but want to mention - I've tried 'direct'
approach some time ago [1]
using list-like representation. Despite giving speedup for relatively
small values and does not having
TOAST index table at all, the performance degraded over standard TOAST
with large ones,
For a linked list, this degradation was to be expected , as you can
only start requesting the next page once you have fetched the previous
one.This is why I implemented it as tidarray (or N-ary tree of TID arrays
for larger data), which does not have this fundamental problem.You get a large span of tids for pages at once, an could be at least
as fast as index scan and possibly faster, as you get the tid array
slightly cheaperand dependency on TIDs have the immediate effect of VACUUM FULL breaking
the overall structure,
so it should be modified accordingly too.
Yes, VACUUM FULL of the toast table is one known thing that has to be
modified for thisOTOH the current behaviour of VACUUM FULL is not even very useful for
fixing heavily out-of-order toast table anyway, as it just compacts
free space but does not do anything about putting the toasted fields
in same order as main heap fields, so your slow queries stay slow even
after VACUUM FULL.
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
On Thu, Jul 30, 2026 at 9:53 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
Hi!
I keep thinking about this. Hannu, have you thought about a mixed
approach?
In [1] above Michael mentioned the very serious drawback of using direct
TIDs -
while vacuuming the TOAST table we have to modify the original table as
well,
so I don't see a way to avoid using some kind of index at all.
My main motivation is getting rid of the index, as the individual
index lookups are the main source of query slowdowns.
And increasing the toast OID size to solve running out of OIDs will
only worsen the performance issue as it makes the toast index
larger. And it makes the issue worse in exactly the case it tries to
solve, as the index lookup slowdown worsens with larger indexes.
In case of VACUUM FULL / CLUSTER you very much *want to re-order*
the toasted fields to be *in the main table order* not in oid order
if you do any sequential scans, or even spot look-ups for rows with
multiple toasted fields.
So the solution is to disallow VACUUM FULL directed to the toast
table and to modify the VACUUM FULL / CLUSTER to have an option to
also cluster the toast table while working on the main table.
My direct TOAST patch was just a PoC to show it is possible.
I'll take a serious look at your patch when moving forward to implement
something aimed to actually go into the core.
It could be easier to start from that than cleaning up the current AI code
:)
---
Hannu
To recap the main goals for Direct Toast
Why do this
-----------
# performance
- 5% - 25% for in-memory vector queries without
indexes for vector sizes where indexing isn't
possible
- 2x for simple in-memory queries where fetching
fetching toast is a significant part of the work
- 1.5x to 100x for cases where toast index does
not fit in memory the worst case would be a
full table with an out-of-order index that
doesn't fit in memory where each toasted field
causes an extra disk access for index.
Assuming 1 ms for that the extra disk access
it adds 46 days to the full scan.
I have not seen a dump or copy operation that long,
but I have seen one taking over a week.
Chris Travers had some anecdotal evidence of
a case where sequential scans degraded to
single-digit rows per second rows per second
on a database with large external SCSI arrays
# no running out of OIDs at 4B
- added bonus - not slowing down finding scarce
free oids when close to 4B
# space savings
- if your toasted fields are small
the index space usage can make up a significant
portion of the data size
Migration
---------
As direct toast just adds one more VARATT pointer type
and does not change anything else it is fully backwards
compatible. The toast table format change is also backwards
compatible as it adds a field at the end of the tuple
VACUUM FULL needs to be modified to refuse to work on toast tables.
CLUSTER already refuses as now the toast index is partial
VACUUM FULL and CLUSTER need to get an option to simultaneously
also rewrite toast table in main table row order.
I have to remind that currently there is no UPDATE implemented
for the TOASTed values, and TOAST replication is an issue too.
On Thu, Jul 30, 2026 at 11:25 AM Hannu Krosing <hannuk@google.com> wrote:
To recap the main goals for Direct Toast
Why do this
-----------# performance
- 5% - 25% for in-memory vector queries without
indexes for vector sizes where indexing isn't
possible
- 2x for simple in-memory queries where fetching
fetching toast is a significant part of the work
- 1.5x to 100x for cases where toast index does
not fit in memory the worst case would be a
full table with an out-of-order index that
doesn't fit in memory where each toasted field
causes an extra disk access for index.
Assuming 1 ms for that the extra disk access
it adds 46 days to the full scan.
I have not seen a dump or copy operation that long,
but I have seen one taking over a week.
Chris Travers had some anecdotal evidence of
a case where sequential scans degraded to
single-digit rows per second rows per second
on a database with large external SCSI arrays
# no running out of OIDs at 4B
- added bonus - not slowing down finding scarce
free oids when close to 4B
# space savings
- if your toasted fields are small
the index space usage can make up a significant
portion of the data sizeMigration
---------As direct toast just adds one more VARATT pointer type
and does not change anything else it is fully backwards
compatible. The toast table format change is also backwards
compatible as it adds a field at the end of the tupleVACUUM FULL needs to be modified to refuse to work on toast tables.
CLUSTER already refuses as now the toast index is partialVACUUM FULL and CLUSTER need to get an option to simultaneously
also rewrite toast table in main table row order.
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
Yes, the fact that TOASTed values are fully replaced on update helped to
keep the patch short :)
The physical replication should have no issues, as the patch just removes
some operations when inserting or updating in direct mode.
Logical decoding indeed may need to change the toast lookup if it uses its
own functions instead of the standard ones.
On Thu, Jul 30, 2026 at 11:47 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
Show quoted text
I have to remind that currently there is no UPDATE implemented
for the TOASTed values, and TOAST replication is an issue too.On Thu, Jul 30, 2026 at 11:25 AM Hannu Krosing <hannuk@google.com> wrote:
To recap the main goals for Direct Toast
Why do this
-----------# performance
- 5% - 25% for in-memory vector queries without
indexes for vector sizes where indexing isn't
possible
- 2x for simple in-memory queries where fetching
fetching toast is a significant part of the work
- 1.5x to 100x for cases where toast index does
not fit in memory the worst case would be a
full table with an out-of-order index that
doesn't fit in memory where each toasted field
causes an extra disk access for index.
Assuming 1 ms for that the extra disk access
it adds 46 days to the full scan.
I have not seen a dump or copy operation that long,
but I have seen one taking over a week.
Chris Travers had some anecdotal evidence of
a case where sequential scans degraded to
single-digit rows per second rows per second
on a database with large external SCSI arrays
# no running out of OIDs at 4B
- added bonus - not slowing down finding scarce
free oids when close to 4B
# space savings
- if your toasted fields are small
the index space usage can make up a significant
portion of the data sizeMigration
---------As direct toast just adds one more VARATT pointer type
and does not change anything else it is fully backwards
compatible. The toast table format change is also backwards
compatible as it adds a field at the end of the tupleVACUUM FULL needs to be modified to refuse to work on toast tables.
CLUSTER already refuses as now the toast index is partialVACUUM FULL and CLUSTER need to get an option to simultaneously
also rewrite toast table in main table row order.--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
On Thu, Jul 30, 2026 at 6:41 AM Michael Paquier <michael@paquier.xyz> wrote:
Okay, spoiler: I did look at the code, and your LLM has just been
hard-coding a new vartag_external, plumbing it into the TOAST
internals without caring about any concept of backward-compatibility.
If "backward-compatibility" meant the need to refactor the code for easier
extensibility I very much agree with you. Some refactoring would make the whole
thing much more pleasant to work on, and your patches did a good job at that.
Maybe we can move the cleanup/refactoring into a separate patch set
to get at least that part moving ?
I would be happy to help reviewing that
---
Hannu