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

On Mon, Sep 7, 2026 at 5:13 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Sat, Sep 05, 2026 at 02:24:50PM +0200, Hannu Krosing wrote:
> > Attached is a v2 patch series implementing "Direct TOAST", a new storage format
> > for out-of-line (TOASTed) variable-length attributes in PostgreSQL.
>
> Thanks for splitting that into a new thread.
>
> > 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)
>
> Noted. Now they are as well some arguments that come into mind that
> don't make it sound as an acceptable design, because this proposal is
> about tradeoffs, mostly. As far as I know, there is never a magical
> solution when it comes to software, and restrictions of your patch set
> are in place, reflecting these tradeoffs.
>
> There's a bit of bloat in this message; quoting the most relevant
> parts only to make that readable. There is a lot of AI bloat in your
> text, perhaps reconsider this approach before posting to the lists...
> But well..

It is always hard for me to decide how much I have to explain things.
And I need to explain much less to you than to others as you have been
working on these parts much more :)

> This gist of the proposal can be summarized based on this, in simpler
> words (not everything, but these are the most relevant pieces here):
> - Add a new varatt_direct, that acts as a new type of external
> pointer, replace va_valueid by a ItemPointerData.
> - The va_tid points to a a chunk in the TOAST table, that includes an
> array of tids. This array of tids redirects to each chunk.

There is a shortcut for the relatively common case where there is just
one chunk, in which case the data is directly in the chunk pointed to.

> - Instead of an index lookup combined to a heap lookup, we need to
> retrieve two heap blocks, one to get the tids array, one for the chunk
> itself.

This is almost always faster because we retrieve the ctid array
directly, not via index lookup, avoiding all the index lookup steps -
open index, pin pages, do scans, etc.

As you mention later, there could be more needed optimisations around
locking and read-ahead.

> - The array of tids is stored in the *last* chunk.
> - Bypass the index handling, because the tids don't require that.
> - Avoid the 4-byte OID value wraparound by design, as this switches to
> a tid.
>
> > 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
>
> So, in terms of benchmarks, this is a claim based on:
> - a pgbench workload with many toasted atttributes.

Yes, I wanted to make the toasting overhead visible without having to
consume excessive amount of disk space.

I will next run the performance tests with an actual case I have
encountered where a table with four toasted JSON fields ran out of
toast oids at 1 billion rows, at size of a few tens of TB.

> - pgvector

Yes, this was at the other end of the spectrum where everything was
already in shared buffers and the speedup was purely from avoiding all
the index manipulation.

> - no mention of configuration, as far as I can see, or anything?

I will write a more detailed report on benchmarks.

> Putting this last point aside for a minute..
>
> Claiming that this approach is simply "better" based on only a subset
> of workloads is debatable, and how it could be better in some other
> cases while not impacting the performance of the default 4-byte TOAST
> OID?

The default 4-byte TOAST is unchanged except for some IF-s havin the OR added

> A few things that come on top of my mind:
> - How does this fart with readahead? The tids array is in the last
> block, if we have a cold cache and need to retrieve the last block
> *before* looking at a block away from that, isn't that a penalty in
> itself if data does not fit completely into OS cache or even
> shared_buffers?

The current implementaton is that it is either the tid array *or* the
data , not both, so the tid array lookup is just a faster index
lookup.

> - Support for read of slices, where retrieving short cuts of the TOAST
> data could pay the price due to the last chunk requirement. substr()
> is a common thing for applications.

Storing tid arrays directly aacts as a faster index lookup, else the
slice implementation stays the same.

> - Lock contention and concurrency. A btree page for a TOAST table in
> cache is able to hold hundreds of references to various entries.

Fair point.
The reasons why I think it s still faster are:
1. the case for tiny toasted values will go directly to the page.
Currently "tiny" is one 2k chunk , but could be expanded to full page
as a follow-up
2. the case with just a small number of chunks will likely have the
data and tid array(s) in the same page, or pages very cloes to each
other.
3. for huge toasted values the full tid array is constructed before
the actual data retrieval starts, and it is done in order of magnitude
less page accesses than getting the same data from a b-tree index
takes.

> Claiming that this can be always outperformed is unclear, to say the
> least, by switching to one array of tids for each value stored in
> TOAST divided in chunks? I think that this puts more pressure on the
> OS cache or PG shared buffers when dealing with many hot values.

Do you have a speecific scenario in mind I could tests ?

> > Crucially:
> > sizeof(varatt_direct) == sizeof(varatt_external) == 18 bytes
>
> This claim looks incorrect to me, I am quickly measuring:
> sizeof(varatt_external) = 16
> sizeof(varatt_direct) = 20
> So, yeah, I'm also puzzled with this statement.

Me too, must have been left in from some earlier edit :(

> > 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.
>
> In v2-0002 (with comment pieces added to v2-0008 much later, no idea
> why but):
> + TupleDescInitEntry(tupdesc, (AttrNumber) 4,
> + "chunk_tids",
> + TIDARRAYOID,
> + -1, 0);
> + TupleDescInitEntry(tupdesc, (AttrNumber) 5,
> + "chunk_tid_offsets",
> + INT8ARRAYOID,
> + -1, 0);

This was to cover a case where toast_flavour was set via global GUC.

Allowing the GUC for this now seems like a design mistake and should
not be allowed, as this will require all these checks.

> This is ensured by adding two new concepts to TOAST tables, even in
> the existing TOAST 4-byte case: two new attributes and a partial
> index. The existing attribute layer is moot when using one (4-byte
> value) or the other (direct). This is a waste, and unlikely free.
> The addition of a new partial index does not help much in that.

It is not a *new* partial index, but the current PK is replaced with
this. In case of online conversion, the current PK constraint is
converted into this in-place.

> I understand that you've written that this way to claim a cheap rewrite
> when switching over by manipulating data later on on upgrades, but
> that does not sound acceptable here.

You do not need to manipulate data at all if you are ok with current
data staying accessed via the 4-byte OID and index.

> Finally, and the biggest elephant in the room here by far.. VACUUM
> FULL, CLUSTER and REPACK *have* to be forbidden, because on rewrite
> each command rewrites the tids in the parent.

They are only forbidden directly on the toast table, they work fine
when run on main table and they also result in a clustered order
synchronized with main table, which is not the case when running on
toast table directly with the current design

> That's a legal
> defensive set of commands because it is possible to reclaim bloat from
> TOAST relations directly, and I doubt that we'd *ever* want to drop
> this property, especially based on the benchmark claim of upthread.

If you have a workload that updates toasted columns this results in
these being sprinkled all over the toast table, with random oids, so
even CLUSTER will not put them in the same order as main table.

This is why you want to run REPACK on the main table if you need to
recover space AND also care about performance.

In my tests autovacuum kept the direct toast table in shape more
efficiently, most likely because it could skip the expensive index
cleanup phase, so there was less bloat accumulating.

> A
> worst thing to me is that this seems to entirely disable their use due
> to this in v2-0004, cluster_rel() or cluster.c:
> + if (OldHeap->rd_rel->relkind == RELKIND_TOASTVALUE)
> [..,]
> + if (OldHeap->rd_att->natts >= 4 &&
>
> The two new attributes are added *unconditionally*.

Yes, but they are not used if you keep using only 4-byte OIDs. In that
case they only appear in the catalog tables.

And even when they are looked up, it is an order of magnitude faster
than opening and pinning index the same toast lookup

> Another thing that is really disturbing to me is that using tids
> lowers the protection regarding TOAST lookups. A TOAST value acts a
> second barrier of protection if we miss a chunk, and we have a long
> history of bugs in this area (spoiler: we still had two recent
> discussions about the same set of issues for very old problems, still
> unresolved). Relying on only a get_toast_snapshot() and a bare TID
> lookup neither verifies nor enforces that the chunk we have retrieved
> is the correct one.

Are we really re-checking the OID in the chunk tuple in current implementation.

I don't think we re-check the OID in the chunk tuple for b-tree index lookups.

> For this argument, I was not completely sure how
> to put it into words first, so I have asked Claude about a good
> definition regarding this point, to be told that direct pointers carry
> no "identity", and I'm finding the term adapted here, because a value
> acts as an identity to ensure that we have the chunk we expect, based
> on the data on heap side.

> My main assumption regarding this patch would be, mapping with
> previous remarks I got, to use a reloption to decide which type of
> external pointer to use and have a one-one mapping with what's stored
> in heap rather than make the TOAST table definitions more complicated
> than they should be.

The complexity exists for a good reason: it removes complexity from
the toast field lookup path.

I acknowledge that the complexity of index lookup is currently well
hidden within the single function call "get toast chunks using index,"
but it is nonetheless present.

And it does not affect you unless you actually use direct toast - for
old 4-byte toast none of it is used.

> So: don't try to solve the rewrite problem now
> and discard it,

In my professional work zero-downtime fixes are very high on the priority list.

Making TOAST cheaper in space usage and toast lookups faster would
allow much wider flexibility in toast usage.

> give the option for new tables to choose this method
> (for the reasons listed in the last two paragraphs, I guess no anyway,
> but that's what I would recommend if following up).

The main reason you may want to REPACK *only* the toast table is that
it currently behaves badly, partly because of expensive toast index
cleanups.

If you want performance back, you want to REPACK the main table, which
fixes the random placement of toasted field problem.

> Saying all that, and after screening the patch set, there are two
> things that I find attractive out of the bat.
>
> Number 1, 0001. Making toast_fetch_datum() an inline function that
> calls toast_fetch_datum_slice() sounds like an okay thing to do. Just
> removing comments for the sake of moving code is never nice, or just
> move the definition of toast_fetch_datum() to be closer to _slice().
> Some could also claim about the code lacking symmetry with
> toast_decompress_datum() and toast_decompress_datum_slice(), as well,
> so we may also group these together..

Yes, I will take a look.

I did not look closely at any (de)compression code as I think we can
do much better there in the future once we can use extra fields in the
toast table instead of cramming everything into two bits in the toast
pointer.

> Number 2, this thing, hidden in v2-0003 (for some reason, but the
> split of the patch set is super weird to me, so I'm not quite sure to
> follow entirely why you've done things this way for a couple of
> parts):
> +typedef struct ToastExternalMetadata
> +{
> It sounds to me that we could do that kind of thing *before* thinking
> about adding new types of external pointers, because it simplifies the
> data fetch in quite a few code paths? You could just rework that based
> on HEAD, with only OID values around.

Agreed

I only noticed that refactoring opportunity after adding the direct
toast code, when some functions became too long for my taste. I did
not set out to refactor code; I just did it when the non-refactored
code became uncomfortable to work with.

## In conclusion:

I still think that these three goals
- zero-downtime upgrade
- less space used
- faster performance
are equally important and should be tackled together.

As for your performance concerns, can you point me to use cases where
you think the current oid4 can be faster?
It does not have to be very detailed, just a general workload description helps.

I will run some tests to see if extra checks for direct toast and
index predicate has a measurable effect in oid4 path.

----
Best Regards
Hannu