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

On Mon, Oct 5, 2026 at 3:18 PM Matthias van de Meent
<boekewurm+postgres(at)gmail(dot)com> wrote:
>
> 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.

It does avoid index maintenance if you do not *use* any "plain" toast
If VACUUM only sees dead direct toast tuples it collects zero dead
tuple tids, and it does not run the index cleanup in case it has
collected no indead tuples.

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

It does not do CREATE INDEX. It updates pg_index and pg_constraint dirtectly

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

But main table and Direct TOAST *without indexes* can be in many cases
cheaper to rebuild than toast table with 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.

Sure. But I'd expect Direct TOAST to be able to avoid most of that
bloat via in-line pruning to UNUSED

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

Nothing *completely* avoids this, at leas not with DBA doing some
amount of planning and tuning.

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

Again, it's not absolute solution. I can always costruct a use case
where the most sensible solution is to shut down your business for a
few hours and run VACUUM FULL or CLUSTER

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

We do skip index cleanup when we prune other HEAP_ONLY_TUPLES.

It is allowable exactly because there are no index pointers to them.

Same for Direct TOAST tuples

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

When say you have multiple toasted values and you initially only
insert one and other coime later, or when there are workflows that
keep updating some of the toasted columns.

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

I can send you another script which you can then run for ~10 days (I
did) to verify that thins DO get messed up

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

Can you elaborate, whay do you think you need to perform index cleanup
when the firs pass deleted no rows that had indexes pointing to them ?

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

Again, they ALREADY are nullable,

"> > AND even now the three user attributes are NOT declared NOT NULL"

Can you elaborate on "requiring much more effort from every system" -
which systems do you have in mind ?

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

Can you point me to the places where this is done ?

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

Basically the same way I have been doing on-line de-bloating since 2006 :)

Find the main row pointing to that toasted value, then update the
toasted value until it does not fit on that page and then it moves to
a lower one.

See the attachce python script for doing this on normal table, this
can be easily adapted to Direct TOAST, especially if we start adding
back-pinters to toast chunks

I know that the orthodox PostgreSQL de-bloating in this case either
lcoking everything and running VACUUM FULL or REPACK

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

We can come back to this discussion once these benefits of performance
improvements in the index lookup path materialize.

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

You can't really avoid accessing the index for index lookup paths,
which by default involve fetching, pinning and locking the index
pages. You can not make that faster than not doing any of that.

The Indexed toast is currently a few tens of percent slower even if
everything is in shared buffers.

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

There are a few proposals pfor bringing back the overflow pages from
the 1976 Ingres design. That would really make the coupling tight.

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

I have been working on a separate proposal doing something similar for
GENERATED ALWAYS AS *ROW* IDENTITY which mostly uses TID expanded to
64 bits as ROW id and only puts the "row id" into a partial index if
the row has to move off the original page and hot chains stop working
for lookup :)

This is closely related to my work on enabling REPLICA IDENTITY ROWID,
which uses tid as identity.

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

Yes, I have seen real cases of running out of toast oids wheremore
than 8TB of a total 9TB table was toast+toast index.

In my extreme test running 10 days of massive updates on small toasted
columns the Toast INDEX became the largest part of storage cost in
case of indexed toast.

Direct TOAST did not bloat the main TOAST table much, and of course it
did not have the toast index at all.

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

You can still repack, just you have to do it together with main table.

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

Using from low-level system functions is allowed to just not care.

And user query already do the right thing anyway, because the always
*do* base their actions on what is in system tables.

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

Thank, will definitely rtake a look.

> Kind regards,
>
> Matthias van de Meent
> Databricks (https://www.databricks.com)
>
> [0] https://www.postgresql.org/message-id/179089136170.117434.3768797469846977966%40gmail.com