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 <[email protected]> wrote:
>
> On Mon, Sep 7, 2026 at 9:45 AM Nikita Malakhov <[email protected]> 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)
