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 <[email protected]> Sent: March 19, 2026 11:29 AM To: Boris Mironov <[email protected]> Cc: Madyshev Egor <[email protected]>; Hayato Kuroda (Fujitsu) <[email protected]>; [email protected] <[email protected]> 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 <[email protected]> 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
