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

Rebased as needed!


On Mon, Oct 5, 2026 at 5:37 PM Hannu Krosing <hannuk@google.com> wrote:
>
> On Mon, Oct 5, 2026 at 3:18 PM Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> >
> > On Wed, 30 Sept 2026 at 11:15, Hannu Krosing <hannuk@google.com> wrote:
> > > On Tue, Sep 29, 2026 at 2:19 PM Matthias van de Meent
> > > > <boekewurm+postgres@gmail.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