Re: Support for 8-byte TOAST values, round two

On Mon, Aug 10, 2026 at 10:03 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Mon, Aug 10, 2026 at 09:37:43AM +0200, Hannu Krosing wrote:
> > Are function pointers really that much of a concern considering that
> > we use tables of virtual pointers in core code paths like Table and
> > Index Access Methods?
>
> I am pretty sure that TOAST paths could get hotter than AMs under some
> COPY-related workloads.
>
> > Was this performance impact actually measured ?
>
> I didn't measure any. Another comment I had was "overengineered",
> which is perhaps fair as the TOAST code is quite linear in terms of
> internals.
>
> > Do I understand correctly that with this patch the selection between
> > oid and oid8 happens at the table level not at the individual toasted
> > item level ?
>
> Yes, it's a design choice to use a table-level comparison. Choose one
> with the reloption, then stick to it. Across upgrades, we keep the
> same TOAST table. Rewrites are the same: no changes after a VACUUM
> FULL, same atttype. There is nothing difference than what we do now,
> which is why it's appealing to me.
>
> > Maybe it would make sense to get better backwards compatibility and
> > smoother upgrades by doing the same I did in my direct toast patch to
> > allow both versions to live in the same table, namely to add a
> > separate oid8 column and use conditional indexes for both new oid8 and
> > old plain oid lookups?
>
> Backward-compatibility and upgrades are handled in the patch set.
> And TBH, I don't like much mixing multiple vartags in a single TOAST
> table because it makes the whole layer more annoying to deal with.

Can you elaborate how having multiple vartags in the single table is
more annoying than having them in separate tables.

My experience is that on insert you select the vartag to use from a
GUC or table attributem and on read we already have a number of
different vartags each requiring special handling.

> Sticking with one atttype for each toast table is simpler. One
> optimization would be to allow a OID external pointer to point to a
> oid8 TOAST table, which offers benefits as long as we have less than 4
> billion OIDs generated. This would not last long for some
> deployments.

Agreed, but we also never need more than 5 bytes worth of space with
current 8 kb pages, as there would be not toast table rows to point to
:)

As you are concerned about space usage, but want to keep accessing
toast chunks via indexes, have you considered something like the
43-bit integers used to represent tuple ids in GIN posting lists?

> Another idea that I found overkill was the use of a
> per-toast sequence to generate the numbers. This can also be built on
> top of what I have here, just found that expensive and a single oid8
> counter in the control file is fine for all tables in a cluster
> anyway.

I tend to agree. Theoretically it could result in better locking
behaviour between the sequences, but in practice it would be very
unlikely to have a large number of toast tables in active parallel
use.
And the overhead of index inserts will always be larger than getting
the number from sequence, so one sequence should be good for at least
10 tables. And it is unlikely to need more.

> > In case of oid8 it would be
> > * add a column for new chunk_id8 oid8
> > * add a conditional index on the (chunk_id8, chunk_seq) WHERE
> > chunk_id_oid8 IS NOT NULL
> > * making the original toast index conditional on WHERE chunk_id IS NOT NULL
>
> Err. You're making TOAST tables larger than they should with
> attributes that may not be required because some attributes may not be
> required.

The overhead is only in the the pg_attribute table, as we already have
the 8 bits of null bitmnap in the header and this will not grow, and
any values not used will not be stored.
And in case of direct toast storing (potentially compressed) tid lists
will be way more space efficient than having an index, in addition to
having much better performance.

In direct toas case it can even result in (very slighly) smaller space
usage because of not storing oid, and at least in the case of single
chunk toast also not he chunk_seq.

> With the TOAST data, it does not matter overall, but I'm
> not much a fan of adding more data to disk than necessary, with more
> indexes to maintain on each insert.

The benefit of my scheme is that you do not need to maintain the
indexes you are not using.

> As a whole I'm not planning to go beyond what's presented on this
> thread. Sticking to the simpler plan of having 8-byte values would
> make a lot of customers I know of quite happy, because we have no exit
> route now when it comes to core.

I still hope to convince you that an even simpler plan to get all the
benefits of larger id space by using tids directly :)

The patch is much simpler and mostly just bypasses code paths that are used.

At high level it

For select
- goes directly to toast tuple, bypassing index lookup with all its
locking and pinning
- if there are more than a single chunk it hets the list of tuple ids,
again bypassing the index lookups, and loads them directly
- if there are larger number of chunks than can fit their tids in one
array, theis is repeated recursively for some of the toast tuples
fetched using this list

At insert/update time
- it bypasses getting an OID
- it inserts the chunks exactly as the current code
- it bypasses index operations, opening, pinning, adding the entry,
poissibly splitting index page, etc.

At vacuum time (not implemented yet) it can bypass collecting the
tuple id for index cleanup for deleted tuples

So the only thing _added_ is collecting the chunk ids in an array and
storing it in selected end-of-run pages instead of insering them in
the toast index.

Everything else streamlines the code bypassing expensive operations.

(And I know adding tid array messes with fitting exacly 4 chunks of
~2000 bytes on each page, but longer term plan is to relax this by
also adding another array of chunk offsets togetehr with the tid array
which would allow using all the page space in toast pages. Also the
tid array is highly compressible if the tuples are allocated together,
as shown in GIN posting lists. And I have a separate patch that would
improve the compression another 6x over GIN posting lists for common
cases, totaling 20-30 x compression)

The main thing (that I know of) that I have not yet done handling
VACUUM FULL and CLUSTER for direct toast tables.

Disabling them if direct toast tuples exist is trivial.

Supporting explicit CLUSTER / VACUUM FULL on main table which also
handles toast would need some work.

Just clustering the toast table only in OID order can be useless for
performance if the toasted columns have been frequently updated or if
data has been added later to some.

Currently this can be done manually using CREATE TABLE (LIKE oldtable
INCLUDING ALL) + INSERT INTO ... SELECT and then swapping the table
but it would be much nicer to have it integrated as CLUSTER / VACUUM
FULL
This would also be what the new

Because this is what the new REPACK command does anyway I am not sure
that also supporting CLUSTER AND VACUUM to include TOAST reqrite is
worth doing.

I am still investigating your patch set to understand if I missed any
other parts that would need changes.

---
Hannu