Re: Idea to enhance pgbench by more modes to generate data (multi-TXNs, UNNEST, COPY BINARY)

Dear Fujii Masao,

Thank you for your interest in this patch.

For convenience I duplicate my results from previous post and
reformat your results below:

pgbench (PostgreSQL) 19devel / client-side generation
Mode
Scale | 1 | 2 | 10 | 20 | 100 | 200 | 1000 | 2000
Mc | 0.15 | 0.29 | 1.39 | 2.93 | 14.79 | 30.78 | 161.52 | 330.63
Sc | 0.13 | 0.27 | 1.37 | 2.69 | 14.71 | 29.99 | 152.83 | 298.88
Mg | 0.17 | 0.30 | 1.58 | 3.26 | 15.91 | 31.95 | 160.31 | 326.46
Sg | 0.20 | 0.38 | 1.66 | 3.39 | 18.72 | 36.26 | 176.54 | 351.66

pgbench (PostgreSQL) 19devel / server-side generation
Mode
Scale | 1 | 2 | 10 | 20 | 100 | 200 | 1000 | 2000
MU | 0.22 | 0.47 | 2.35 | 4.71 | 24.80 | 53.19 | 261.44 | 536.05
SU | 0.22 | 0.44 | 2.35 | 4.78 | 24.42 | 49.16 | 246.41 | 495.51
MG | 0.22 | 0.45 | 2.29 | 4.44 | 24.03 | 50.21 | 256.54 | 544.17
SG | 0.23 | 0.43 | 2.35 | 5.04 | 27.74 | 52.72 | 250.27 | 492.77

Fujii's single run results / client-side generation
Mode
Scale | 100 | 1000
Sc | 18.31 | 204.47
Sg | 13.38 | 151.81

Fujii's single run results / server-side generation
Mode
Scale | 100 | 1000
SU | 23.16 | 249.07
SG | 21.30 | 230.93

> I ran some tests on my MacBook (server and pgbench on the same machine)

In my environment I ran pgbench on different virtual machine from DB
server and win of COPY BINARY over COPY TEXT was clear. All results are
average of 5 runs. You can use "-Idtccccc" parameter to run 5 times
same init mode. By default it will be "single transaction for whole data
set". Otherwise "-IdtMccccc" will execute init in "one transaction per
scale" mode 5 times. Could you please rerun your tests with these
tweaks?

> So I'm wondering if there are specific conditions where binary mode or unnest()
> performs better. If not, it may not be worth supporting these additional data
> loading options...

Besides introduction of new data generation modes this patch prepares ground
for multi-threaded data initialization with introduction of "one COMMIT per scale"
mode. By the way, all of your tests were executed in old (default) "single transaction
for whole data set" mode.

For addition of UNNEST init mode, it was added for PostgreSQL forks like TigerDB
that add columnar mode of data storage. This is quite useful for OLAP systems as
much more efficient way of data retrieval of few columns from wide tables. These
are also quite efficient as databases for monitoring systems like Prometheus.

Best regards,
Boris

________________________________
From: Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
Sent: March 19, 2026 11:29 AM
To: Boris Mironov <boris_mironov(at)outlook(dot)com>
Cc: Madyshev Egor <E(dot)Madyshev(at)ftdata(dot)ru>; Hayato Kuroda (Fujitsu) <kuroda(dot)hayato(at)fujitsu(dot)com>; pgsql-hackers(at)postgresql(dot)org <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Idea to enhance pgbench by more modes to generate data (multi-TXNs, UNNEST, COPY BINARY)

On Thu, Mar 12, 2026 at 11:28 PM Boris Mironov
<boris_mironov(at)outlook(dot)com> wrote:
>
> Hi Egor,
>
> Thank you very much for your time reviewing this patch
> and guiding through some of its inefficiencies.

I like the idea of improving the performance of the initial data load
in pgbench. That's definitely useful.

I ran some tests on my MacBook (server and pgbench on the same machine)
using different data loading options. Here are the results:

------------------------------------------------
[ Client / Text mode ]
pgbench -i -Idtg -s100
done in 13.38 s (drop tables 0.00 s, create tables 0.01 s, client-side
generate 13.38 s).

pgbench -i -Idtg -s1000
done in 151.81 s (drop tables 0.00 s, create tables 0.01 s,
client-side generate 151.81 s).

[ Client / Binary mode ]
pgbench -i -Idtc -s100
done in 18.32 s (drop tables 0.00 s, create tables 0.01 s, client-side
generate 18.31 s).

pgbench -i -Idtc -s1000
done in 204.48 s (drop tables 0.00 s, create tables 0.01 s,
client-side generate 204.47 s).

[ Server / generate_series ]
pgbench -i -IdtG -s100
done in 21.30 s (drop tables 0.00 s, create tables 0.00 s, server-side
generate 21.30 s).

pgbench -i -IdtG -s1000
done in 230.94 s (drop tables 0.00 s, create tables 0.01 s,
server-side generate 230.93 s).

[ Server / Unnest ]
pgbench -i -IdtU -s100
done in 23.16 s (drop tables 0.00 s, create tables 0.00 s, server-side
generate 23.16 s).

pgbench -i -IdtU -s1000
done in 249.08 s (drop tables 0.00 s, create tables 0.01 s,
server-side generate 249.07 s).
------------------------------------------------

In my tests, text mode was faster than binary mode. Also, on the server side,
generate_series() was faster than unnest().

So I'm wondering if there are specific conditions where binary mode or unnest()
performs better. If not, it may not be worth supporting these additional data
loading options...

Regards,

--
Fujii Masao