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

Adding also the other original recipients

On Wed, Sep 30, 2026 at 10:58 AM Hannu Krosing <hannuk(at)google(dot)com> wrote:
>
> On Tue, Sep 29, 2026 at 2:19 PM Matthias van de Meent
> <boekewurm+postgres(at)gmail(dot)com> wrote:
> >
> > On Mon, 7 Sept 2026 at 08:51, Hannu Krosing <hannuk(at)google(dot)com> wrote:
> > >
> > > On Mon, Sep 7, 2026 at 5:13 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> > > >
> > > > A few things that come on top of my mind:
> > [...]
> > > > - 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.
>
> Do you have your notes on this? I can see why you'd claim fewer page
> accesses, but an order of magnitude is a very large claim; I'm getting
> to a ~5x reduction at best.
>
> With standard packing of OID indexes, I'd expect ~ 290 TIDs per page
> (at 20 bytes/entry, 70% fillfactor), with up to 25% more (to ~
> 350/page) if the btree split algo selects the split point nicely. For
> a TID array stored in a heap tuple, you'll be hard-pressed to fit more
> than 1356 (uncompressed) TIDs on a page. So, to find the same number
> of TIDs the index will read 4-5x as many pages, but that's not a full
> order of magnitude, and only with current indexing techniques.

Yeah, it was 2 binary orders of magnitude for normal use when
everything fits in memory

The extreme cases only surface when you the toast index does not fit
in memory and index part heavily dominates the lookups. That is when
you have small toast chunks.

> I think it's feasible for btree to further optimize its storage format
> of toast -like fixed-size nonnull column definitions, allowing for
> more entries per page.

This will still not address the need to do index cleanups when pruning
deleted toast, which avoiding the index entirely does.

> > > > 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.
> >
> > Don't you need a SHARE lock to build (replace) indexes?

You need an exclusive lock for less than a millisecond to convert the
index in-place.

It is a metadata only change.

> > > 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.
>
> No, but then you've not really switched over your toast.

You can rewrite all the toast using REPACK CONCURRENTLY if you want
all your toasted data to use Direct TOAST.

> > > 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
>
> But the independent repack-ability of toast tables is a very valuable
> feature. Main tables have indexes that may be very expensive to
> rebuild. Yes, CONCURRENTLY could help, but it'll still take a
> possibly huge amount of resources, in time, CPU, memory, disk, IO.
> Repacking the toast table separately solves bloat issues in the toast
> table, and does not require the main table to be rebuilt, avoiding the
> related resource consumptions of index builds etc.

Good Point. This is an issue when the main table which is much larger
and more expensive index-wise than toast and toast is already messed up

(Though in this case I would expect the main problem to be the main
table, not toast)

If toast is not messed up, in-line pruning should keep the TOAST table
growth in check.

I will look at adding REPACK TOAST CONCURRENTLY which only clusters toast.

The current Direct TOAST design still has the toast table oid in
varlena pointer for which the only justifyable use is having multiple
toast tables active concurrently.

> > 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
>
> Yes, repacking the toast table won't reorder the toast table to heap
> order, but in my humble opinion that's OK; just de-bloating the toast
> table and index is enough for some workloads.

It is for some.

And for even more workloads you do not need de-bloating at all, even
with current design.

Direct Toast allows you to not get bloated in the first place because
of inline pruning and really cheap vacuum which needs to jsut look at
dirty pages and fully skip the index cleanup.

> > > 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.
>
> What do you mean by sprinkled all over and random OIDs?
> Toast IDs that are generated at about the same time will generally be
> closely related, and after CLUSTER these will be close together in the
> toast table, for both OID (very likely) and OID8 (practically
> guaranteed).
>
> They will definitely be in OID order, so what is the
> sprinkling randomness about?

UPDATE will generate a new toasted value with newly allocated oid. If
some fields are updated late then they will not be close.

hannuk=# create table toastme(datum text storage external);
CREATE TABLE
hannuk=# insert into toastme select repeat('abracadabra', 1000);
INSERT 0 1
hannuk=# select pg_column_toast_chunk_id(datum) from toastme ;
pg_column_toast_chunk_id
──────────────────────────
16500
(1 row)
hannuk=# update toastme set datum = repeat('not abracadabra', 1000);
UPDATE 1
hannuk=# select pg_column_toast_chunk_id(datum) from toastme ;
pg_column_toast_chunk_id
──────────────────────────
16501
(1 row)

> > > 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.
> >
> > How do you fit these claims together?
> >
> > 1. "zero-downtime / zero-migration switch to direct toast (and back)"
> > (from the start of the thread);
> > 2. No index cleanup phase
> >
> > If you can do a zero-migration change, old data will still have old
> > pointers, and those must be looked up through the index.
> > Once a table has an index, its vacuum MUST apply an index cleanup
> > phase, lest it contain any references to LP_DEAD line pointers still
> > present in the table. Note that LP_DEAD entries don't carry
> > information about which index(es) do or don't contain references to
> > that item, so you can't distinguish between included in and excluded
> > from the index -- all dead items must be processed.

Latest few patch sets have includes a change to pruning which prunes
deleted direct toast chunks to LP_UNUSED directly

So if you have been updating only direct toast pointers there will be
no dead tids collected.

Even if there were some, all dead space from updated direct tuples is
returned to postgresql immediately.

> > > > 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.
> >
> > That's not accurate; these columns will hold NULL values, which means
> > they'll bloat OID-toast tuples with a NULL bitmap, which will be one
> > MAXALIGN quantum in size in this table definition.

There is always space for one byte of null bitmap after the 23-byte
tuple headers, so up to 8 attributes can have their null bitmap for
free.

AND even now the three user attributes are not declared NOT NULL

hannuk=# select attname, attnotnull from pg_attribute where attrelid =
'pg_toast.pg_toast_826'::regclass;
attname │ attnotnull
────────────┼────────────
tableoid │ t
cmax │ t
xmax │ t
cmin │ t
xmin │ t
ctid │ t
chunk_id │ f
chunk_seq │ f
chunk_data │ f
(9 rows)

> > > > 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.
> >
> > No, we don't recheck the valueid in the heaptuple. However, we do
> > check that the chunk counter matches the expected values, and that's
> > something that won't be possible in the direct toast design -- there
> > is nothing in the toast pointer nor the toast tuple that links it to
> > its own identity, especially not outside the scope of MVCC snapshots.

Do you think it os a valuable property to have ?

As we are not bloating the varlena pointer in case of direct toast, we
could add more info in the cunk for multi-chunk cases
One we have more than one chunk, the overhead from becomes tiny

I am already working on enabling a much richer compression and
encoding options using this - zstd and lz4 dictioonalries, Apache AVRO
encoding, per-chunk compression, etc

> > > > 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.
> >
> > It is very feasible for the main table to be packed normally, whilst
> > the toast table becomes very bloated; this happens frequently when
> > large toasted columns get updated every once in a while with vacuum
> > and/or page pruning running just frequently enough to allow the main
> > table's tuple to fit back on the same page. REPACKing the main table
> > requires more locks and possibly much more time than just repacking
> > the toast table (one btree index rebuild vs numerous arbitrarily
> > defined index rebuilds). I don't think trading the independent
> > repack-ability of a toast table for a bit of performance with small
> > toasted values is a reasonable tradeoff for every user.

Not just small toast values, large ones can also be made faster as a follow-up.

Once you have a chunk offset array, you can start putting one large
chunk per page instead of four small ones.

And I am not claiming it is best for all corner cases, likke already
having an old bloated toast table wgich needs repacking. That one
should be repacked before enabling direct toast. Once Direct TOAST is
enabled you can keep the bloat down.

> > > ## 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'd consider huge toasted values as a case where index lookups can be
> > faster, because it automatically gains the benefits of performance
> > improvements in the index lookup path. The Direct Toast path needs to
> > manually build this feature.

Very performant <something> is still slower than not doing it at all.

Currently DToast is roughly the same speed for huge toast values when
indexes are in memory, because the data page fetches dominate
It is significantly faster for cold caches.

But Direct TOAST structure allows it to be made even faster by adding
fored readahead and making use of direct io parallelism

> > And please test the performance difference between repacking a bloated
> > toast table vs repacking its main table with expensive-to-build
> > indexes (e.g. several gin on jsonb, pg_trgm with large text, HNSW,
> > etc.). I think the cost of fixing bloat in the toast table will be
> > much lower than repacking the main table+indexes with it.

I would rather make avoiding the need to repack a priority :)

There will always be ways to mess up your database.

But this makes me realise that I should only add the Direct TOAST
columns (and thus disable direct repack) if the corresponding table
option toast_flavour=direct is set so people who want to retain this
option can do so.

> > > I will run some tests to see if extra checks for direct toast and
> > > index predicate has a measurable effect in oid4 path.
> >
> > Note that PG currently has no builtin partial index definitions in its
> > catalogs, they're only present on user-controlled table definitions.
> > The suggested exclusion of "direct toast" values from the toast index
> > would be a first, and I'm not sure that's something that we want.

I have wanted it before :)

For splitting FUNCTION and PROCEDURE namespaces without needing to
move them to separate system tables.

The toast index is a special case used by a very specific path that
does not really need to care about the predicate as it already knows
weather toi use index or not fased on from which type of toast pointer
comes from.

And for user queries the system does not care if the queried table is
a system table or not.

That said, do you have any specific concerns about having partial
indexes in catalogs?

Many fancy features in catalogs are not really used *from the
catalogs* anyway - instead our code "just knows" they are there and
the catalogs only document that.

I have added a benchmark report as pdf here as it would be even less
readablke if converted to text :)

Note that the largest speedup of 129x there is not from direct toast
but from patching pg_vector to calculate vector dims directly from
varlena pointer. and some other as well are from teaching pg_vector to
use toast slice reads. I will make pull requests to pg_vector for
these soon.