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

On Mon, Sep 7, 2026 at 9:45 AM Nikita Malakhov <hukutoc(at)gmail(dot)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)