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

Hi Egor,

As per your recommendations I've updated code and attached it below.

I've also prepared Excel spreadsheet with benchmarks in raw format as well as processed for better presentation.

Just for reference I provide my test configuration and benchmarks.

Setup
VM: 4 CPU 2.2 GHz Intel Xeon Silver 4114, 16 GB RAM
shared_buffers=2GB
wal_keep_size=300MB
Same DB for all tests. No replicas, just primary instance

Scale | 1 | 2 | 10 | 20 | 100 | 200 | 1000 | 2000
DB size | 1761 MB | 1774 MB | 1878 MB | 2009 MB | 3050 MB | 4351 MB | 14 GB | 27 GB

Data generation modes
Prefixes S Single transaction for whole data set
M Multiple transactions (as 1 transaction per "scale")
Modes G INSERT .. FROM generate_series
U INSERT .. FROM unnest
g COPY .. FROM STDIN TEXT
c COPY .. FROM STDIN BINARY

Average time of 5 runs to complete data initialization

pgbench (PostgreSQL) 17.7 (Debian 17.7-3.pgdg12+1)         
Mode
Scale | 1 | 2 | 10 | 20 | 100 | 200 | 1000 | 2000
SG | 0.22 | 0.45 | 2.18 | 4.39 | 23.94 | 47.89 | 241.99 | 550.91
Sg | 0.16 | 0.27 | 1.53 | 3.19 | 16.08 | 32.98 | 161.31 | 327.56

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

Best regards,
Boris