Re: Direct TOAST v2, faster, smaller and no migration needed - Mailing list pgsql-hackers

On Mon, 7 Sept 2026 at 08:51, Hannu Krosing <hannuk@google.com> wrote:
>
> On Mon, Sep 7, 2026 at 5:13 AM Michael Paquier <michael@paquier.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.

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

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

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

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

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

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

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

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


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

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

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


Kind regards,

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