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

On Wed, 30 Sept 2026 at 11:15, 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:
> > 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.

As I described in my other mail, it does not avoid index maintenance
unless you avoid indexes on the toast table entirely, which this
proposal doesn't.

> > > > > 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.

Yes, but that still waits for other lockers to release, and newer
lockers will conflict with this lock request and wait. A CREATE INDEX
on a table will still cause writers to block when locking the table to
add the new index definition.

> > > > 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

It's more an issue when the main table's indexes are expensive to
build. It doesn't really depend on the size of the main table data; a
toast table+index is (with high certainty) always cheaper to rebuild
on its own than a toast table+index with its main table and its
indexes.

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

Why would it be? HOT updates on a table, with little data stored in
the main table but a lot of columns pushed into TOAST (e.g. json(b)
metadata fields) can definitely have a bloated TOAST table associated
to a compact and well-organized main table.

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

Fragmentation can be a problem in any table with row churn that
doesn't reap the benefits of guaranteed HOT updates. I don't see how
any new direct TOAST system completely avoids this.

> > > 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.

Why would vacuum only need to look at dirty pages? Visibility
(cleanup) horizons frequently outlast the current checkpoint, and can
outlast even the buffer pool if/when replication is enabled, so only
looking at dirtied pages is a very short-sighted method of cleanup.

And I don't see why skipping index cleanup would be allowable in any world.

> > > > 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.

What do you mean by late update? The SQL script you included didn't
really show much other than that TOASTing values close in time
produces close-in-value toast IDs, which is expected. And those
close-in-value IDs are likely placed near eachother (and definitely
placed near eachother after a CLUSTER of the toast 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.
> > >
> > > 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.

I categorically disagree with such a change. Index cleanup _must_
happen for non-HOT tuples when the table contains at least one
non-summarizing index.

> > > > > 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

Correct, but this new scheme would change these into nullable
attributes, requiring much more effort from every system to make sure
they're accessing the right tuple data.

> > > 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 ?

Yes. We have an active problem with SnapshotAny-scans in DDL that
detoast recently-dead tuples' removed TOAST pointers, and the checks
that we do have in place actively protect against real corruptions.
It's bad we're hitting ERROR paths here, but it's much better than the
alternative.

> > > > > 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.

How do you do that? Direct toast pins the location of a toasted value
into a specific physical page, you can't move it. If there's a churn
in toasted values that temporarily can't be removed, and the newest
(and surviving) value is located on the final page of the toast
relation, how would you de-bloat that toast table?

> > > > ## 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.

True. But in this case we do need <something> (a way to handle large
column values). And given the page size limit, we'll have to cut up
large values one way or another. And given that cutting up values,
we'll probably want to avoid fragmenting the table.

> 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

Which part of indexing won't allow that same optimization to be
applied? Direct toast seems to be not dissimilar from a specialized
inlined index; and these should allow similar features to be applied.
The time spent optimizing the toast system for its own bespoke
solution might better be spent on making the index path work better
for TOAST.

> > > 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.

Yes, but I'd like to avoid raising the minimum cost of cleaning up
messes that have already been made. I believe there is no reason
that's good enough to require a strong coupling between toast and main
table repacking.

You may be able to get around this coupling by storing the TID + a
"generation" ID, where repacking the toast table causes new generation
IDs to be produced, and old generations are looked up in an index by
[generation id, old head TID -> head TID], or even rewriting the
chunks into the index as [generation_id, old TID, chunk_id -> chunk
tid].
You'd keep most of the performance of direct toast in the common case
(values created after table created & after last repack) without
throwing out independent repackability.

Note that TOAST tables can be the largest part of a table's total
storage cost, or secondary only after the main table's indexes.
Rebuilding it independently is exceedingly likely to be worth the
effort, vs not cleaning up.

> 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.

Except that you can't disable the direct toast -enabled "never repack
again" poison once it's there, and I think that that is not an
acceptable tradeoff.

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

They require relatively complicated allocations and handling in
critical paths, so I like to avoid them whenever I can. We can make
it work, for sure, but I'm not sure it's worth it for toast;
especially given that expressions aren't exactly cheap to store in
memory.

FYI, there is an ongoing thread over at [0] discussing partial indexes
in catalogs for other purposes.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

[0] https://www.postgresql.org/message-id/179089136170.117434.3768797469846977966%40gmail.com