Direct TOAST v2, faster, smaller and no migration needed

On Sat, Sep 05, 2026 at 02:24:50PM +0200, Hannu Krosing wrote:

Attached is a v2 patch series implementing "Direct TOAST", a new storage format
for out-of-line (TOASTed) variable-length attributes in PostgreSQL.

Thanks for splitting that into a new thread.

Michael Re: your concern in earlier discussion about just adding the
direct toast checks directly into code next to
VARATT_IS_EXTERNAL_ONDISK - this is doen this way because I consider
direct toast to be a simplified and streamlined subtype of traditional
toast which just cuts out the index lookup part. This is also
exemplified by zero-downtime / zero-migration switch to direct toast
(and back)

Noted. Now they are as well some arguments that come into mind that
don't make it sound as an acceptable design, because this proposal is
about tradeoffs, mostly. As far as I know, there is never a magical
solution when it comes to software, and restrictions of your patch set
are in place, reflecting these tradeoffs.

There's a bit of bloat in this message; quoting the most relevant
parts only to make that readable. There is a lot of AI bloat in your
text, perhaps reconsider this approach before posting to the lists...
But well..

This gist of the proposal can be summarized based on this, in simpler
words (not everything, but these are the most relevant pieces here):
- Add a new varatt_direct, that acts as a new type of external
pointer, replace va_valueid by a ItemPointerData.
- The va_tid points to a a chunk in the TOAST table, that includes an
array of tids. This array of tids redirects to each chunk.
- Instead of an index lookup combined to a heap lookup, we need to
retrieve two heap blocks, one to get the tids array, one for the chunk
itself.
- The array of tids is stored in the *last* chunk.
- Bypass the index handling, because the tids don't require that.
- Avoid the 4-byte OID value wraparound by design, as this switches to
a tid.

Then I ran separate 1 hour runs of updates on top of same tables first
for traditional toast then for direct toast .

The direct toast did 1385 TPS while traditional toast did 635 TPS

So, in terms of benchmarks, this is a claim based on:
- a pgbench workload with many toasted atttributes.
- pgvector
- no mention of configuration, as far as I can see, or anything?
Putting this last point aside for a minute..

Claiming that this approach is simply "better" based on only a subset
of workloads is debatable, and how it could be better in some other
cases while not impacting the performance of the default 4-byte TOAST
OID? A few things that come on top of my mind:
- How does this fart with readahead? The tids array is in the last
block, if we have a cold cache and need to retrieve the last block
*before* looking at a block away from that, isn't that a penalty in
itself if data does not fit completely into OS cache or even
shared_buffers?
- Support for read of slices, where retrieving short cuts of the TOAST
data could pay the price due to the last chunk requirement. substr()
is a common thing for applications.
- Lock contention and concurrency. A btree page for a TOAST table in
cache is able to hold hundreds of references to various entries.
Claiming that this can be always outperformed is unclear, to say the
least, by switching to one array of tids for each value stored in
TOAST divided in chunks? I think that this puts more pressure on the
OS cache or PG shared buffers when dealing with many hot values.

Crucially:
sizeof(varatt_direct) == sizeof(varatt_external) == 18 bytes

This claim looks incorrect to me, I am quickly measuring:
sizeof(varatt_external) = 16
sizeof(varatt_direct) = 20

So, yeah, I'm also puzzled with this statement.

2.4. Lock-Free In-Place Schema Upgrades
Existing tables can be upgraded from Plain to Direct TOAST on the fly:
ALTER TABLE my_table SET (toast_flavour = 'direct');
or via pg_ensure_direct_toast(reloid).
This performs a metadata-only catalog update adding chunk_tids and
chunk_tid_offsets with fast-default NULLs. No data rewrite or exclusive table
lock is required. Plain and Direct TOAST datums can coexist within the same
table indefinitely.

In v2-0002 (with comment pieces added to v2-0008 much later, no idea
why but):
+	TupleDescInitEntry(tupdesc, (AttrNumber) 4,
+					   "chunk_tids",
+					   TIDARRAYOID,
+					   -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 5,
+					   "chunk_tid_offsets",
+					   INT8ARRAYOID,
+					   -1, 0);

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

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

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. For this argument, I was not completely sure how
to put it into words first, so I have asked Claude about a good
definition regarding this point, to be told that direct pointers carry
no "identity", and I'm finding the term adapted here, because a value
acts as an identity to ensure that we have the chunk we expect, based
on the data on heap side.

My main assumption regarding this patch would be, mapping with
previous remarks I got, to use a reloption to decide which type of
external pointer to use and have a one-one mapping with what's stored
in heap rather than make the TOAST table definitions more complicated
than they should be. So: don't try to solve the rewrite problem now
and discard it, 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).

Saying all that, and after screening the patch set, there are two
things that I find attractive out of the bat.

Number 1, 0001. Making toast_fetch_datum() an inline function that
calls toast_fetch_datum_slice() sounds like an okay thing to do. Just
removing comments for the sake of moving code is never nice, or just
move the definition of toast_fetch_datum() to be closer to _slice().
Some could also claim about the code lacking symmetry with
toast_decompress_datum() and toast_decompress_datum_slice(), as well,
so we may also group these together..

Number 2, this thing, hidden in v2-0003 (for some reason, but the
split of the patch set is super weird to me, so I'm not quite sure to
follow entirely why you've done things this way for a couple of
parts):
+typedef struct ToastExternalMetadata
+{

It sounds to me that we could do that kind of thing *before* thinking
about adding new types of external pointers, because it simplifies the
data fetch in quite a few code paths? You could just rework that based
on HEAD, with only OID values around.
--
Michael

On Mon, Sep 7, 2026 at 5:13 AM Michael Paquier <michael@paquier.xyz> wrote:

On Sat, Sep 05, 2026 at 02:24:50PM +0200, Hannu Krosing wrote:

Attached is a v2 patch series implementing "Direct TOAST", a new storage format
for out-of-line (TOASTed) variable-length attributes in PostgreSQL.

Thanks for splitting that into a new thread.

Michael Re: your concern in earlier discussion about just adding the
direct toast checks directly into code next to
VARATT_IS_EXTERNAL_ONDISK - this is doen this way because I consider
direct toast to be a simplified and streamlined subtype of traditional
toast which just cuts out the index lookup part. This is also
exemplified by zero-downtime / zero-migration switch to direct toast
(and back)

Noted. Now they are as well some arguments that come into mind that
don't make it sound as an acceptable design, because this proposal is
about tradeoffs, mostly. As far as I know, there is never a magical
solution when it comes to software, and restrictions of your patch set
are in place, reflecting these tradeoffs.

There's a bit of bloat in this message; quoting the most relevant
parts only to make that readable. There is a lot of AI bloat in your
text, perhaps reconsider this approach before posting to the lists...
But well..

It is always hard for me to decide how much I have to explain things.
And I need to explain much less to you than to others as you have been
working on these parts much more :)

This gist of the proposal can be summarized based on this, in simpler
words (not everything, but these are the most relevant pieces here):
- Add a new varatt_direct, that acts as a new type of external
pointer, replace va_valueid by a ItemPointerData.
- The va_tid points to a a chunk in the TOAST table, that includes an
array of tids. This array of tids redirects to each chunk.

There is a shortcut for the relatively common case where there is just
one chunk, in which case the data is directly in the chunk pointed to.

- Instead of an index lookup combined to a heap lookup, we need to
retrieve two heap blocks, one to get the tids array, one for the chunk
itself.

This is almost always faster because we retrieve the ctid array
directly, not via index lookup, avoiding all the index lookup steps -
open index, pin pages, do scans, etc.

As you mention later, there could be more needed optimisations around
locking and read-ahead.

- The array of tids is stored in the *last* chunk.
- Bypass the index handling, because the tids don't require that.
- Avoid the 4-byte OID value wraparound by design, as this switches to
a tid.

Then I ran separate 1 hour runs of updates on top of same tables first
for traditional toast then for direct toast .

The direct toast did 1385 TPS while traditional toast did 635 TPS

So, in terms of benchmarks, this is a claim based on:
- a pgbench workload with many toasted atttributes.

Yes, I wanted to make the toasting overhead visible without having to
consume excessive amount of disk space.

I will next run the performance tests with an actual case I have
encountered where a table with four toasted JSON fields ran out of
toast oids at 1 billion rows, at size of a few tens of TB.

- pgvector

Yes, this was at the other end of the spectrum where everything was
already in shared buffers and the speedup was purely from avoiding all
the index manipulation.

- no mention of configuration, as far as I can see, or anything?

I will write a more detailed report on benchmarks.

Putting this last point aside for a minute..

Claiming that this approach is simply "better" based on only a subset
of workloads is debatable, and how it could be better in some other
cases while not impacting the performance of the default 4-byte TOAST
OID?

The default 4-byte TOAST is unchanged except for some IF-s havin the OR added

A few things that come on top of my mind:
- How does this fart with readahead? The tids array is in the last
block, if we have a cold cache and need to retrieve the last block
*before* looking at a block away from that, isn't that a penalty in
itself if data does not fit completely into OS cache or even
shared_buffers?

The current implementaton is that it is either the tid array *or* the
data , not both, so the tid array lookup is just a faster index
lookup.

- Support for read of slices, where retrieving short cuts of the TOAST
data could pay the price due to the last chunk requirement. substr()
is a common thing for applications.

Storing tid arrays directly aacts as a faster index lookup, else the
slice implementation stays the same.

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

Claiming that this can be always outperformed is unclear, to say the
least, by switching to one array of tids for each value stored in
TOAST divided in chunks? I think that this puts more pressure on the
OS cache or PG shared buffers when dealing with many hot values.

Do you have a speecific scenario in mind I could tests ?

Crucially:
sizeof(varatt_direct) == sizeof(varatt_external) == 18 bytes

This claim looks incorrect to me, I am quickly measuring:
sizeof(varatt_external) = 16
sizeof(varatt_direct) = 20
So, yeah, I'm also puzzled with this statement.

Me too, must have been left in from some earlier edit :(

2.4. Lock-Free In-Place Schema Upgrades
Existing tables can be upgraded from Plain to Direct TOAST on the fly:
ALTER TABLE my_table SET (toast_flavour = 'direct');
or via pg_ensure_direct_toast(reloid).
This performs a metadata-only catalog update adding chunk_tids and
chunk_tid_offsets with fast-default NULLs. No data rewrite or exclusive table
lock is required. Plain and Direct TOAST datums can coexist within the same
table indefinitely.

In v2-0002 (with comment pieces added to v2-0008 much later, no idea
why but):
+       TupleDescInitEntry(tupdesc, (AttrNumber) 4,
+                                          "chunk_tids",
+                                          TIDARRAYOID,
+                                          -1, 0);
+       TupleDescInitEntry(tupdesc, (AttrNumber) 5,
+                                          "chunk_tid_offsets",
+                                          INT8ARRAYOID,
+                                          -1, 0);

This was to cover a case where toast_flavour was set via global GUC.

Allowing the GUC for this now seems like a design mistake and should
not be allowed, as this will require all these checks.

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.

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.

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

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.

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.

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.

And even when they are looked up, it is an order of magnitude faster
than opening and pinning index the same toast lookup

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.

For this argument, I was not completely sure how
to put it into words first, so I have asked Claude about a good
definition regarding this point, to be told that direct pointers carry
no "identity", and I'm finding the term adapted here, because a value
acts as an identity to ensure that we have the chunk we expect, based
on the data on heap side.

My main assumption regarding this patch would be, mapping with
previous remarks I got, to use a reloption to decide which type of
external pointer to use and have a one-one mapping with what's stored
in heap rather than make the TOAST table definitions more complicated
than they should be.

The complexity exists for a good reason: it removes complexity from
the toast field lookup path.

I acknowledge that the complexity of index lookup is currently well
hidden within the single function call "get toast chunks using index,"
but it is nonetheless present.

And it does not affect you unless you actually use direct toast - for
old 4-byte toast none of it is used.

So: don't try to solve the rewrite problem now
and discard it,

In my professional work zero-downtime fixes are very high on the priority list.

Making TOAST cheaper in space usage and toast lookups faster would
allow much wider flexibility in toast usage.

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.

Saying all that, and after screening the patch set, there are two
things that I find attractive out of the bat.

Number 1, 0001. Making toast_fetch_datum() an inline function that
calls toast_fetch_datum_slice() sounds like an okay thing to do. Just
removing comments for the sake of moving code is never nice, or just
move the definition of toast_fetch_datum() to be closer to _slice().
Some could also claim about the code lacking symmetry with
toast_decompress_datum() and toast_decompress_datum_slice(), as well,
so we may also group these together..

Yes, I will take a look.

I did not look closely at any (de)compression code as I think we can
do much better there in the future once we can use extra fields in the
toast table instead of cramming everything into two bits in the toast
pointer.

Number 2, this thing, hidden in v2-0003 (for some reason, but the
split of the patch set is super weird to me, so I'm not quite sure to
follow entirely why you've done things this way for a couple of
parts):
+typedef struct ToastExternalMetadata
+{
It sounds to me that we could do that kind of thing *before* thinking
about adding new types of external pointers, because it simplifies the
data fetch in quite a few code paths? You could just rework that based
on HEAD, with only OID values around.

Agreed

I only noticed that refactoring opportunity after adding the direct
toast code, when some functions became too long for my taste. I did
not set out to refactor code; I just did it when the non-refactored
code became uncomfortable to work with.

## 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 will run some tests to see if extra checks for direct toast and
index predicate has a measurable effect in oid4 path.

----
Best Regards
Hannu

Hi Hannu!

Great you're continuing this work! I've started to review your patch set,
and have question about tests and performance: have you tested it against
large toasted values? When experimenting with direct toast before my
prototype
was much faster on small values but starting with relatively large (have to
recover
previous test results so cannot say exact size), about tens of Mbs,
performance
starts to degrade and on very large values it is much slower compared to
the original
mechanics.

--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/

On Mon, Sep 7, 2026 at 9:45 AM Nikita Malakhov <hukutoc@gmail.com> wrote:

Hi Hannu!

Great you're continuing this work! I've started to review your patch set,
and have question about tests and performance: have you tested it against
large toasted values? When experimenting with direct toast before my prototype
was much faster on small values but starting with relatively large (have to recover
previous test results so cannot say exact size), about tens of Mbs, performance
starts to degrade and on very large values it is much slower compared to the original
mechanics.

Hi, here is a guick tests inserting 10 rows of 1MB, 10MB and 100MB text datums

Direct toast is consistently faster by 7 to 15% on writes

datum size | plain | direct | speed-up
-------------------------------------------
1 MB | 58 | 54 | 7.4%
10 MB | 568 | 495 | 14.7%
100 MB | 6821 | 6139 | 11.1%
1000 MB | 64363 | 58763 | 9.5%

On the read side they are the same when the toast index fits in
memory, varying less than a percent between runs

select id, length(data) from largedirecttoast where id between 21 and
30; -> 19 ms
select id, length(data) from largedirecttoast where id between 21 and
30; -> 183 ms
select id, length(data) from largedirecttoast where id between 21 and
30; -> 1920 ms

For 1GB rows the above select was 2x slower on plain, 47 s vs 23 sec on direct

Here is what I tested:

dtoast=# create table largedirecttoast(id serial primary key, data
text storage external) with (toast_flavour=direct);
CREATE TABLE
Time: 4.351 ms
dtoast=# create table largeplaintoast(id serial primary key, data text
storage external) with (toast_flavour=plain);
CREATE TABLE
Time: 4.269 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 100000) as datum1m)
insert into largeplaintoast(data) select large.datum1m from large,
generate_series(1,10);
INSERT 0 10
Time: 58.330 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 100000) as datum1m)
insert into largedirecttoast(data) select large.datum1m from large,
generate_series(1,10);
INSERT 0 10
Time: 54.280 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 1000000) as datum10m)
insert into largeplaintoast(data) select large.datum10m from large,
generate_series(1,10);
INSERT 0 10
Time: 567.808 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 1000000) as datum10m)
insert into largedirecttoast(data) select large.datum10m from large,
generate_series(1,10);
INSERT 0 10
Time: 494.823 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 10000000) as datum100m)
insert into largeplaintoast(data) select large.datum100m from large,
generate_series(1,10);
INSERT 0 10
Time: 6820.868 ms (00:06.821)
dtoast=# with large as (SELECT repeat('abcdefghij', 10000000) as datum100m)
insert into largedirecttoast(data) select large.datum100m from large,
generate_series(1,10);
INSERT 0 10
Time: 6138.979 ms (00:06.139)
dtoast=# with large as (SELECT repeat('abcdefghij', 100000000) as datum1000m)
insert into largedirecttoast(data) select large.datum1000m from large,
generate_series(1,10);
INSERT 0 10
Time: 58763.165 ms (00:58.763)
dtoast=# with large as (SELECT repeat('abcdefghij', 100000000) as datum1000m)
insert into largeplaintoast(data) select large.datum1000m from large,
generate_series(1,10);
INSERT 0 10
Time: 64363.909 ms (01:04.364)
....
dtoast=# select id, length(data) from largedirecttoast where id
between 31 and 40;
id │ length
────┼────────────
31 │ 1000000000
32 │ 1000000000
33 │ 1000000000
34 │ 1000000000
35 │ 1000000000
36 │ 1000000000
37 │ 1000000000
38 │ 1000000000
39 │ 1000000000
40 │ 1000000000
(10 rows)

Time: 22981.708 ms (00:22.982)
dtoast=# select id, length(data) from largeplaintoast where id between
31 and 40;
id │ length
────┼────────────
31 │ 1000000000
32 │ 1000000000
33 │ 1000000000
34 │ 1000000000
35 │ 1000000000
36 │ 1000000000
37 │ 1000000000
38 │ 1000000000
39 │ 1000000000
40 │ 1000000000
(10 rows)

Time: 47346.034 ms (00:47.346)

The slowness of 1GB datum test in previous email is likely just
because of more levels in toast inex, as it should fit well into
shared buffers

table_name │ table_size │ toast_table_size │ toast_index_size │
──────────────────────────┼──────────────┼──────────────────┼──────────────────┼
public.largeplaintoast │ 11527708672 │ 11399487488 │ 125042688 │
public.largedirecttoast │ 11495727104 │ 11492515840 │ 8192 │

Time: 8.935 ms
dtoast=# show shared_buffers;
shared_buffers
────────────────
8GB
(1 row)

Show quoted text

On Mon, Sep 7, 2026 at 10:47 AM Hannu Krosing <hannuk@google.com> wrote:

On Mon, Sep 7, 2026 at 9:45 AM Nikita Malakhov <hukutoc@gmail.com> wrote:

Hi Hannu!

Great you're continuing this work! I've started to review your patch set,
and have question about tests and performance: have you tested it against
large toasted values? When experimenting with direct toast before my prototype
was much faster on small values but starting with relatively large (have to recover
previous test results so cannot say exact size), about tens of Mbs, performance
starts to degrade and on very large values it is much slower compared to the original
mechanics.

Hi, here is a guick tests inserting 10 rows of 1MB, 10MB and 100MB text datums

Direct toast is consistently faster by 7 to 15% on writes

datum size | plain | direct | speed-up
-------------------------------------------
1 MB | 58 | 54 | 7.4%
10 MB | 568 | 495 | 14.7%
100 MB | 6821 | 6139 | 11.1%
1000 MB | 64363 | 58763 | 9.5%

On the read side they are the same when the toast index fits in
memory, varying less than a percent between runs

select id, length(data) from largedirecttoast where id between 21 and
30; -> 19 ms
select id, length(data) from largedirecttoast where id between 21 and
30; -> 183 ms
select id, length(data) from largedirecttoast where id between 21 and
30; -> 1920 ms

For 1GB rows the above select was 2x slower on plain, 47 s vs 23 sec on direct

Here is what I tested:

dtoast=# create table largedirecttoast(id serial primary key, data
text storage external) with (toast_flavour=direct);
CREATE TABLE
Time: 4.351 ms
dtoast=# create table largeplaintoast(id serial primary key, data text
storage external) with (toast_flavour=plain);
CREATE TABLE
Time: 4.269 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 100000) as datum1m)
insert into largeplaintoast(data) select large.datum1m from large,
generate_series(1,10);
INSERT 0 10
Time: 58.330 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 100000) as datum1m)
insert into largedirecttoast(data) select large.datum1m from large,
generate_series(1,10);
INSERT 0 10
Time: 54.280 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 1000000) as datum10m)
insert into largeplaintoast(data) select large.datum10m from large,
generate_series(1,10);
INSERT 0 10
Time: 567.808 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 1000000) as datum10m)
insert into largedirecttoast(data) select large.datum10m from large,
generate_series(1,10);
INSERT 0 10
Time: 494.823 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 10000000) as datum100m)
insert into largeplaintoast(data) select large.datum100m from large,
generate_series(1,10);
INSERT 0 10
Time: 6820.868 ms (00:06.821)
dtoast=# with large as (SELECT repeat('abcdefghij', 10000000) as datum100m)
insert into largedirecttoast(data) select large.datum100m from large,
generate_series(1,10);
INSERT 0 10
Time: 6138.979 ms (00:06.139)
dtoast=# with large as (SELECT repeat('abcdefghij', 100000000) as datum1000m)
insert into largedirecttoast(data) select large.datum1000m from large,
generate_series(1,10);
INSERT 0 10
Time: 58763.165 ms (00:58.763)
dtoast=# with large as (SELECT repeat('abcdefghij', 100000000) as datum1000m)
insert into largeplaintoast(data) select large.datum1000m from large,
generate_series(1,10);
INSERT 0 10
Time: 64363.909 ms (01:04.364)
....
dtoast=# select id, length(data) from largedirecttoast where id
between 31 and 40;
id │ length
────┼────────────
31 │ 1000000000
32 │ 1000000000
33 │ 1000000000
34 │ 1000000000
35 │ 1000000000
36 │ 1000000000
37 │ 1000000000
38 │ 1000000000
39 │ 1000000000
40 │ 1000000000
(10 rows)

Time: 22981.708 ms (00:22.982)
dtoast=# select id, length(data) from largeplaintoast where id between
31 and 40;
id │ length
────┼────────────
31 │ 1000000000
32 │ 1000000000
33 │ 1000000000
34 │ 1000000000
35 │ 1000000000
36 │ 1000000000
37 │ 1000000000
38 │ 1000000000
39 │ 1000000000
40 │ 1000000000
(10 rows)

Time: 47346.034 ms (00:47.346)

Also, truncate was faster on direct toast

dtoast=# truncate table largedirecttoast;
TRUNCATE TABLE
Time: 891.392 ms
dtoast=# truncate table largeplaintoast;
TRUNCATE TABLE
Time: 1193.535 ms (00:01.194)

Show quoted text

On Mon, Sep 7, 2026 at 10:54 AM Hannu Krosing <hannuk@google.com> wrote:

The slowness of 1GB datum test in previous email is likely just
because of more levels in toast inex, as it should fit well into
shared buffers

table_name │ table_size │ toast_table_size │ toast_index_size │
──────────────────────────┼──────────────┼──────────────────┼──────────────────┼
public.largeplaintoast │ 11527708672 │ 11399487488 │ 125042688 │
public.largedirecttoast │ 11495727104 │ 11492515840 │ 8192 │

Time: 8.935 ms
dtoast=# show shared_buffers;
shared_buffers
────────────────
8GB
(1 row)

On Mon, Sep 7, 2026 at 10:47 AM Hannu Krosing <hannuk@google.com> wrote:

On Mon, Sep 7, 2026 at 9:45 AM Nikita Malakhov <hukutoc@gmail.com> wrote:

Hi Hannu!

Great you're continuing this work! I've started to review your patch set,
and have question about tests and performance: have you tested it against
large toasted values? When experimenting with direct toast before my prototype
was much faster on small values but starting with relatively large (have to recover
previous test results so cannot say exact size), about tens of Mbs, performance
starts to degrade and on very large values it is much slower compared to the original
mechanics.

Hi, here is a guick tests inserting 10 rows of 1MB, 10MB and 100MB text datums

Direct toast is consistently faster by 7 to 15% on writes

datum size | plain | direct | speed-up
-------------------------------------------
1 MB | 58 | 54 | 7.4%
10 MB | 568 | 495 | 14.7%
100 MB | 6821 | 6139 | 11.1%
1000 MB | 64363 | 58763 | 9.5%

On the read side they are the same when the toast index fits in
memory, varying less than a percent between runs

select id, length(data) from largedirecttoast where id between 21 and
30; -> 19 ms
select id, length(data) from largedirecttoast where id between 21 and
30; -> 183 ms
select id, length(data) from largedirecttoast where id between 21 and
30; -> 1920 ms

For 1GB rows the above select was 2x slower on plain, 47 s vs 23 sec on direct

Here is what I tested:

dtoast=# create table largedirecttoast(id serial primary key, data
text storage external) with (toast_flavour=direct);
CREATE TABLE
Time: 4.351 ms
dtoast=# create table largeplaintoast(id serial primary key, data text
storage external) with (toast_flavour=plain);
CREATE TABLE
Time: 4.269 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 100000) as datum1m)
insert into largeplaintoast(data) select large.datum1m from large,
generate_series(1,10);
INSERT 0 10
Time: 58.330 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 100000) as datum1m)
insert into largedirecttoast(data) select large.datum1m from large,
generate_series(1,10);
INSERT 0 10
Time: 54.280 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 1000000) as datum10m)
insert into largeplaintoast(data) select large.datum10m from large,
generate_series(1,10);
INSERT 0 10
Time: 567.808 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 1000000) as datum10m)
insert into largedirecttoast(data) select large.datum10m from large,
generate_series(1,10);
INSERT 0 10
Time: 494.823 ms
dtoast=# with large as (SELECT repeat('abcdefghij', 10000000) as datum100m)
insert into largeplaintoast(data) select large.datum100m from large,
generate_series(1,10);
INSERT 0 10
Time: 6820.868 ms (00:06.821)
dtoast=# with large as (SELECT repeat('abcdefghij', 10000000) as datum100m)
insert into largedirecttoast(data) select large.datum100m from large,
generate_series(1,10);
INSERT 0 10
Time: 6138.979 ms (00:06.139)
dtoast=# with large as (SELECT repeat('abcdefghij', 100000000) as datum1000m)
insert into largedirecttoast(data) select large.datum1000m from large,
generate_series(1,10);
INSERT 0 10
Time: 58763.165 ms (00:58.763)
dtoast=# with large as (SELECT repeat('abcdefghij', 100000000) as datum1000m)
insert into largeplaintoast(data) select large.datum1000m from large,
generate_series(1,10);
INSERT 0 10
Time: 64363.909 ms (01:04.364)
....
dtoast=# select id, length(data) from largedirecttoast where id
between 31 and 40;
id │ length
────┼────────────
31 │ 1000000000
32 │ 1000000000
33 │ 1000000000
34 │ 1000000000
35 │ 1000000000
36 │ 1000000000
37 │ 1000000000
38 │ 1000000000
39 │ 1000000000
40 │ 1000000000
(10 rows)

Time: 22981.708 ms (00:22.982)
dtoast=# select id, length(data) from largeplaintoast where id between
31 and 40;
id │ length
────┼────────────
31 │ 1000000000
32 │ 1000000000
33 │ 1000000000
34 │ 1000000000
35 │ 1000000000
36 │ 1000000000
37 │ 1000000000
38 │ 1000000000
39 │ 1000000000
40 │ 1000000000
(10 rows)

Time: 47346.034 ms (00:47.346)

On Mon, Sep 7, 2026 at 10:54 AM Hannu Krosing <hannuk@google.com> wrote:

The slowness of 1GB datum test in previous email is likely just
because of more levels in toast inex, as it should fit well into
shared buffers

table_name │ table_size │ toast_table_size │ toast_index_size │
──────────────────────────┼──────────────┼──────────────────┼──────────────────┼
public.largeplaintoast │ 11527708672 │ 11399487488 │ 125042688 │
public.largedirecttoast │ 11495727104 │ 11492515840 │ 8192 │

Time: 8.935 ms
dtoast=# show shared_buffers;
shared_buffers
────────────────
8GB
(1 row)

Looks like this was caused by something else pushing data out of shared buffers.

Once thee index (and most data) was there, plain toast was as fast as
direct toast also on 1GB reads.

The latest patch set, now includes patch to pruning to treat a TOAST tuple
with NULL chunk_id as if it had HEAP_ONLY_TUPLE set and release it
immediately.

I am running some tests on both this (in classic and direct toast mode)
and the OID8 toast to write up a better comparison

A few more fixes to pass CI tests

and another one to account for recently committed OID8 TOAST

Show quoted text

On Mon, Sep 21, 2026 at 2:11 PM Hannu Krosing <hannuk@google.com> wrote:

A few more fixes to pass CI tests

one more round

Show quoted text

On Mon, Sep 21, 2026 at 5:02 PM Hannu Krosing <hannuk@google.com> wrote:

and another one to account for recently committed OID8 TOAST

On Mon, Sep 21, 2026 at 2:11 PM Hannu Krosing <hannuk@google.com> wrote:

A few more fixes to pass CI tests

On 2026-09-21 20:29:14 +0200, Hannu Krosing wrote:

one more round

Please just run CI on your own repo. Sending a patchset three times in a day,
to thousands of people, just to see CI results, is ... not a scalable approach.

Is there a way to make sure that it actually runs the same way?

The reason I have been sending it is that the tests do pass locally,
even the ones that fail in CI and I have been trying to figure out why

Show quoted text

On Mon, Sep 21, 2026 at 8:49 PM Andres Freund <andres@anarazel.de> wrote:

On 2026-09-21 20:29:14 +0200, Hannu Krosing wrote:

one more round

Please just run CI on your own repo. Sending a patchset three times in a day,
to thousands of people, just to see CI results, is ... not a scalable approach.

On 9/21/26 22:01, Hannu Krosing wrote:

Is there a way to make sure that it actually runs the same way?

The reason I have been sending it is that the tests do pass locally,
even the ones that fail in CI and I have been trying to figure out why

That's why Andres was suggesting you enable CI on your github repo, in
which case you'll be able to debug the failures there.

It's also possible to investigate the logs from cfbot runs, by clicking
on the link in cfbot:

https://github.com/postgres/postgres-cfbot/actions/runs/35639175676/attempts/1

From a cursory look, I guess there's a couple compiler warnings due to
toast_decompress_datum now being unused, etc. Some of the failures seem
to be caused by lz4 being the default TOAST on some of the machines (and
you seem to expect pglz).

Haven't looked further.

regards

--
Tomas Vondra

Hi Hannu,

On Mon, Sep 21, 2026 at 10:01 PM Hannu Krosing <hannuk@google.com> wrote:

Is there a way to make sure that it actually runs the same way?

Yes. cfbot runs the GitHub Actions workflow that is in the tree, so
your own fork runs the same jobs. Push the branch to your fork, enable
pg-ci.yml under Actions, and add a repository variable PG_CI_ENABLED
with the value 1. It is all in src/tools/ci/README.

The reason I have been sending it is that the tests do pass locally,
even the ones that fail in CI and I have been trying to figure out why

That is usually a platform you do not build on, like 32 bit, Windows
or the autoconf build, or a warning your local compiler does not
report.

Regards,
Shihao

On Mon, Sep 21, 2026 at 10:12 PM shihao zhong <zhong950419@gmail.com> wrote:

Hi Hannu,

On Mon, Sep 21, 2026 at 10:01 PM Hannu Krosing <hannuk@google.com> wrote:

Is there a way to make sure that it actually runs the same way?

Yes. cfbot runs the GitHub Actions workflow that is in the tree, so
your own fork runs the same jobs.

Thanks a lot!

That was the missing piece :)

Push the branch to your fork, enable
pg-ci.yml under Actions, and add a repository variable PG_CI_ENABLED
with the value 1. It is all in src/tools/ci/README.

Regards
Hannu

Now the CI passes in my repo
(https://github.com/postsql/postgres/actions/runs/35711192304)

Show quoted text

On Mon, Sep 21, 2026 at 10:21 PM Hannu Krosing <hannuk@google.com> wrote:

On Mon, Sep 21, 2026 at 10:12 PM shihao zhong <zhong950419@gmail.com> wrote:

Hi Hannu,

On Mon, Sep 21, 2026 at 10:01 PM Hannu Krosing <hannuk@google.com> wrote:

Is there a way to make sure that it actually runs the same way?

Yes. cfbot runs the GitHub Actions workflow that is in the tree, so
your own fork runs the same jobs.

Thanks a lot!

That was the missing piece :)

Push the branch to your fork, enable
pg-ci.yml under Actions, and add a repository variable PG_CI_ENABLED
with the value 1. It is all in src/tools/ci/README.

Regards
Hannu

[PATCH v10 0/9] Direct TOAST: bypass TOAST B-Tree index via physical TIDs
============================================================

Hello hackers,

Here is version 10 of the Direct TOAST patch set.

Direct TOAST introduces an alternative out-of-line varlena storage format
that bypasses the traditional TOAST B-Tree index scans during reads and index
insertions during writes by embedding physical TIDs directly in the varlena
pointer (varatt_direct) and within hierarchical chunk trees.

This update addresses several concurrency, resource safety, and permission
invariants identified during recent reviews, and adds interrupt checks and
recursion guards across chunk traversal paths.

The updated branch is also pushed to:
https://github.com/postsql/postgres/tree/direct-toast-submission

Key Changes in v10
==================

1. Superuser Privilege Check for In-Place Upgrades
- Added an explicit superuser check (superuser()) in pg_ensure_direct_toast()
before modifying catalog state. Only superusers may alter TOAST table
metadata and mark TOAST indexes partial.
- Added regression test coverage in direct_toast.sql / direct_toast.out
verifying that unprivileged roles receive ERRCODE_INSUFFICIENT_PRIVILEGE.

2. Relation Locking and Transaction Lifetime Invariants
- In ensure_direct_toast(), upgraded the target relation lock from
AccessShareLock to ShareUpdateExclusiveLock while catalog columns and
index predicates are being modified, preventing concurrent conflicting
schema modifications.
- Followed standard backend conventions by using table_close(..., NoLock) on
both the target relation and toast relation in ensure_direct_toast(),
ensuring locks are held until transaction commit rather than released
prematurely.
- In toast_delete_datum_direct(), changed table_close(toastrel,
RowExclusiveLock)
to table_close(toastrel, NoLock) to avoid premature lock release/downgrade
before the transaction finishes.

3. Safe Buffer Pin Cleanup on Corrupted Chunk Error Paths
- In toast_fetch_datum_slice() (detoast.c), explicitly call
ReleaseBuffer(buffer)
before invoking elog(ERROR, ...) on unexpected NULL chunk data or corrupted
chunk headers, preventing buffer pin leaks during error recovery.

4. Recursion Depth Protection (Stack Safety)
- Added check_stack_depth() calls to all recursive chunk tree traversals:
* toast_fetch_datum_direct_slice_recursive() in detoast.c
* toast_delete_datum_direct_recursive() in toast_internals.c
* check_toasted_attribute_direct_recursive() in
contrib/amcheck/verify_heapam.c

5. Interrupt Responsiveness in Iteration Loops
- Added CHECK_FOR_INTERRUPTS() in chunk iteration loops across the backend
to ensure statement timeouts and cancel requests (SIGINT) are processed
promptly when processing very large TOAST DAGs:
* Multi-chunk tree slice traversal in detoast.c
* Direct chunk deletion loop in toast_internals.c
* Logical decoding chunk reconstruction in reorderbuffer.c
* Recursive child verification loop in contrib/amcheck/verify_heapam.c

6. Pointer Size Documentation and Static Assertions
- Updated varatt.h and README.toast to document that sizeof(varatt_direct)
is 20 bytes (matching varatt_external_oid8 due to 2 bytes of trailing
compiler struct alignment padding), resulting in a 22-byte on-disk
external varlena header.
- Added a compile-time StaticAssertDecl on sizeof(varatt_direct) to ensure
struct layout invariants are enforced across platforms.

Patch Series Overview (v10)
===========================

v10-0001: Refactor detoasting and decompression pipeline to unify full and
slice operations.
v10-0002: Add Direct TOAST catalog, GUC, and reloptions infrastructure.
v10-0003: Implement Direct TOAST core storage reading and writing (flat array
and hierarchical tree DAG, recursive slicing, deletion).
v10-0004: Support Direct TOAST in logical decoding, replication, and online
REPACK.
v10-0005: Add amcheck verification for Direct TOAST tuples (verify_heapam).
v10-0006: Add documentation for Direct TOAST.
v10-0007: Add pg_ensure_direct_toast for in-place legacy TOAST table upgrade.
v10-0008: Add backend TOAST architecture documentation and clean up detoast
access.
v10-0009: Prune dead unindexed TOAST tuples directly to LP_UNUSED
during VACUUM.

Testing & Verification
======================

The patch series builds cleanly under GCC and Clang with:
CFLAGS="-O2 -ggdb -fsanitize=alignment,undefined -fno-sanitize-recover=all"
CPPFLAGS="-DRELCACHE_FORCE_RELEASE
-DENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS"
--enable-cassert --enable-injection-points --enable-tap-tests

Verification results:
- make check in src/test/regress: 240/240 tests passed (including
direct_toast).
- make check in contrib/amcheck: 4 regression tests and 6 TAP suites (298/298
tests) passed.
- make check in src/test/modules/injection_points: 4 regression tests and
15 isolation tests (including repack_direct_toast) passed.
- Server log audit confirmed zero buffer refcount leaks, zero cache leaks,
and zero resource warnings under RELCACHE_FORCE_RELEASE.

Feedback and review are warmly welcome.

Best regards,
Hannu

Rebased

Conflict resolved in Commit 0001 (detoast.c): Upstream commit
e27f3b2cad7 ("Remove duplicate definition of the compressed varlena
header") replaced TOAST_COMPRESS_METHOD / TOAST_COMPRESS_EXTSIZE with
VARDATA_COMPRESSED_GET_COMPRESS_METHOD /
VARDATA_COMPRESSED_GET_EXTSIZE. Resolved in
toast_decompress_datum_slice() to use
VARDATA_COMPRESSED_GET_COMPRESS_METHOD(attr).

Show quoted text

On Fri, Sep 25, 2026 at 8:49 AM Hannu Krosing <hannuk@google.com> wrote:

[PATCH v10 0/9] Direct TOAST: bypass TOAST B-Tree index via physical TIDs
============================================================

Hello hackers,

Here is version 10 of the Direct TOAST patch set.

Direct TOAST introduces an alternative out-of-line varlena storage format
that bypasses the traditional TOAST B-Tree index scans during reads and index
insertions during writes by embedding physical TIDs directly in the varlena
pointer (varatt_direct) and within hierarchical chunk trees.

This update addresses several concurrency, resource safety, and permission
invariants identified during recent reviews, and adds interrupt checks and
recursion guards across chunk traversal paths.

The updated branch is also pushed to:
https://github.com/postsql/postgres/tree/direct-toast-submission

Key Changes in v10
==================

1. Superuser Privilege Check for In-Place Upgrades
- Added an explicit superuser check (superuser()) in pg_ensure_direct_toast()
before modifying catalog state. Only superusers may alter TOAST table
metadata and mark TOAST indexes partial.
- Added regression test coverage in direct_toast.sql / direct_toast.out
verifying that unprivileged roles receive ERRCODE_INSUFFICIENT_PRIVILEGE.

2. Relation Locking and Transaction Lifetime Invariants
- In ensure_direct_toast(), upgraded the target relation lock from
AccessShareLock to ShareUpdateExclusiveLock while catalog columns and
index predicates are being modified, preventing concurrent conflicting
schema modifications.
- Followed standard backend conventions by using table_close(..., NoLock) on
both the target relation and toast relation in ensure_direct_toast(),
ensuring locks are held until transaction commit rather than released
prematurely.
- In toast_delete_datum_direct(), changed table_close(toastrel,
RowExclusiveLock)
to table_close(toastrel, NoLock) to avoid premature lock release/downgrade
before the transaction finishes.

3. Safe Buffer Pin Cleanup on Corrupted Chunk Error Paths
- In toast_fetch_datum_slice() (detoast.c), explicitly call
ReleaseBuffer(buffer)
before invoking elog(ERROR, ...) on unexpected NULL chunk data or corrupted
chunk headers, preventing buffer pin leaks during error recovery.

4. Recursion Depth Protection (Stack Safety)
- Added check_stack_depth() calls to all recursive chunk tree traversals:
* toast_fetch_datum_direct_slice_recursive() in detoast.c
* toast_delete_datum_direct_recursive() in toast_internals.c
* check_toasted_attribute_direct_recursive() in
contrib/amcheck/verify_heapam.c

5. Interrupt Responsiveness in Iteration Loops
- Added CHECK_FOR_INTERRUPTS() in chunk iteration loops across the backend
to ensure statement timeouts and cancel requests (SIGINT) are processed
promptly when processing very large TOAST DAGs:
* Multi-chunk tree slice traversal in detoast.c
* Direct chunk deletion loop in toast_internals.c
* Logical decoding chunk reconstruction in reorderbuffer.c
* Recursive child verification loop in contrib/amcheck/verify_heapam.c

6. Pointer Size Documentation and Static Assertions
- Updated varatt.h and README.toast to document that sizeof(varatt_direct)
is 20 bytes (matching varatt_external_oid8 due to 2 bytes of trailing
compiler struct alignment padding), resulting in a 22-byte on-disk
external varlena header.
- Added a compile-time StaticAssertDecl on sizeof(varatt_direct) to ensure
struct layout invariants are enforced across platforms.

Patch Series Overview (v10)
===========================

v10-0001: Refactor detoasting and decompression pipeline to unify full and
slice operations.
v10-0002: Add Direct TOAST catalog, GUC, and reloptions infrastructure.
v10-0003: Implement Direct TOAST core storage reading and writing (flat array
and hierarchical tree DAG, recursive slicing, deletion).
v10-0004: Support Direct TOAST in logical decoding, replication, and online
REPACK.
v10-0005: Add amcheck verification for Direct TOAST tuples (verify_heapam).
v10-0006: Add documentation for Direct TOAST.
v10-0007: Add pg_ensure_direct_toast for in-place legacy TOAST table upgrade.
v10-0008: Add backend TOAST architecture documentation and clean up detoast
access.
v10-0009: Prune dead unindexed TOAST tuples directly to LP_UNUSED
during VACUUM.

Testing & Verification
======================

The patch series builds cleanly under GCC and Clang with:
CFLAGS="-O2 -ggdb -fsanitize=alignment,undefined -fno-sanitize-recover=all"
CPPFLAGS="-DRELCACHE_FORCE_RELEASE
-DENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS"
--enable-cassert --enable-injection-points --enable-tap-tests

Verification results:
- make check in src/test/regress: 240/240 tests passed (including
direct_toast).
- make check in contrib/amcheck: 4 regression tests and 6 TAP suites (298/298
tests) passed.
- make check in src/test/modules/injection_points: 4 regression tests and
15 isolation tests (including repack_direct_toast) passed.
- Server log audit confirmed zero buffer refcount leaks, zero cache leaks,
and zero resource warnings under RELCACHE_FORCE_RELEASE.

Feedback and review are warmly welcome.

Best regards,
Hannu