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

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(at)google(dot)com> wrote:
>
> 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)