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

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)

On Mon, Sep 7, 2026 at 10:54 AM Hannu Krosing <hannuk(at)google(dot)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(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)