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

From: Boris Mironov <[email-masked]>
Date: Sun, 1 Mar 2026 12:18:03 +0000

Hi Egor,

Quick update.
Sending slightly updated version of the patch as couple
of small bugs have been fixed. Also more tests have been
added to cover specifically all possible init modes when
they called with "multi-" of "single-" transaction switches
as well as combination of multiple init modes in one call
to pgbench (eg, -IdtMccGc).

Cheers,
Boris

---

From: Madyshev Egor <[email-masked]>
Date: Mon, 2 Mar 2026 14:12:09 +0000

Hi Boris,

I have reviewed the new patch. Overall, it looks correct,
but I have a few minor questions.

1. What do you think about moving characters in 'detail: Allowed step 
characters are: "dtgMScGUvpf"' so that generation modes and 
transactions count modes are not mixed? For example "dtMSgcGUvpf".
2. In the initCreateTables function, default values are set as empty 
strings '' in the pgbench_history and pgbench_accounts tables. Was 
this done intentionally, and if so, what is the reason? In the 
pgbench_tellers and pgbench_branches tables, the implicit default 
would be NULL - why was this logic changed?
3. In showPopulateTableCopyProgress, I think it would be better to 
calculate elapsed_sec and remaining_sec inside the condition blocks, 
as is done in the original code.
4. Do the changes and bug fixes in the patch affect performance? Are 
the existing performance measurements still valid?

Best regards,
Egor

---

From: Boris Mironov <[email-masked]>
Date: Tue, 3 Mar 2026 04:06:30 +0000

Hi Egor,

Thank you for your time reviewing this patch and all of its versions!

> transactions count modes are not mixed? For example "dtMSgcGUvpf".

Done

> would be NULL - why was this logic changed?

I personally would set default filler for every table since "TPC-B-like"
means that filler is not used as expected by actual requirement of
TPC-B for row length. There are 2 specific tests for not-NULL values
in those 2 tables that do not have "default values". I would prefer
to use similar approach everywhere and bring on requirement of TPC-B
for row length. Since TPC-B is kind of archaic now this isn't best
tactic to fight another battle against "well established test set".

> as is done in the original code.

I believe original code with complete copy of both variables initialization
twice in the same proc doesn't bring any benefits. Hence shortened it.

> the existing performance measurements still valid?

We didn't significantly alter logic since last benchmark. I'll try to find some
time to rerun all tests again and publish updated results.

Best regards,
Egor

---

From: Boris Mironov <[email-masked]>
Date: Tue, 3 Mar 2026 11:13:01 +0000

Hi Egor,

Sorry for so many extra changes. I promise, this one is good.

I believe it will beneficial to explain how buffer size for each row in COPY BINARY is actually calculated. Instead of hard coded values like 35, 40, 250, I put in comments actual sizes of each field as well as easy to read formulas. IMHO it adds extra value and resolves possible future questions right away.

I greatly appreciate your time spent on this patch.

Best regards,
Boris

---

From: Madyshev Egor <[email-masked]>
Date: Fri, 6 Mar 2026 07:26:44 +0000

Hi Boris,

I'm in the final stage of reviewing your patch. Before completing, 
please fix the patch file and rebase it to the current state of 
the master branch. The current version contains two commits 
that are already in the master.

Because of this, the patch cannot be cleanly applied to the repository.

Best regards,
Egor

---

From: Boris Mironov <[email-masked]>
Date: Fri, 6 Mar 2026 14:21:36 +0000

Hi Egor,

> One more thing I missed in my previous post:
> there is no documentation for the new modes (-c, -U, -M, -S).
> Please add descriptions for them in doc/src/sgml/pgbench.sgml
> (similar to the existing options).

Documentation update has been added

> As soon as the rebase and documentation update are completed,
> the patch will be ready for final review.

Patch has been rebased to current state of the main branch.
Squashing 27 separate commits shrunk patch size almost 4-fold.

Thank you very much. Greatly appreciate your help in this endeavor.

Best regards,
Boris

---

From: Boris Mironov <[email-masked]>
Date: Mon, 9 Mar 2026 05:18:58 +0000

Hi Egor,

Sorry, but I had to issue new version of the patch to include changes to documentation,
which was missed in previous version.
Now patch consists of 3 files:

 doc/src/sgml/ref/pgbench.sgml                |  90 ++-
 src/bin/pgbench/pgbench.c                    | 802 ++++++++++++++++---
 src/bin/pgbench/t/001_pgbench_with_server.pl | 236 +++++-
 3 files changed, 978 insertions(+), 150 deletions(-)

Best regards,
Boris

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Mon, 9 Mar 2026 07:55:12 +0000

Dear Boris,

There is a similar idea which parallelize the initialization. Not sure, is there
an interactions or comparison between them? Your patch seems to divide a
transaction into several parts but everything is done by the single thread,
whereas proposed in [1] is to dispatch to other threads.

[1]: https://commitfest.postgresql.org/patch/6242/

Best regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: Boris Mironov <[email-masked]>
Date: Mon, 9 Mar 2026 09:57:57 +0000

Dear Hayato,

> whereas proposed in [1] is to dispatch to other threads.

You're absolutely right. Mircea (author of multithread patch #6242 [1]) contacted
me with a question if our patches could be merged. At that point in time
none of our patches had reviewers and sheer size of each one of them was quite
big (~500 lines of new code). Therefore we decided to leave our patches
as a separate commits and see what community will say.

It's quite clear that there is certain synergy between them and perhaps it would
make perfect sense to introduce them together in same release of PostgreSQL.

I'm not familiar with intricate details of process that takes place after CommitFest,
especially if several reviewed commits in "Ready to go" status touch same
area of the product.

I believe that my patch shouldn't require too many changes in order to be merged
with Mircea's one since core logic to initialize set of rows in three main tables
for one particular "scale" is already in place.

Thank you for your interest in my commit and
Best regards,

Boris

---

From: Boris Mironov <[email-masked]>
Date: Wed, 11 Mar 2026 11:53:07 +0000

Hi Egor,

> The patch is applied with warnings in the documentation.:
> "warning: squelched 1 whitespace error
> warning: 6 lines add whitespace errors."

v15 of the patch fixes 6 trailing spaces in pgbench.sgml

> I've checked everything else is fine for me.

Hopefully there is enough time to get into list of accepted changes
from this CommitFest.

Cheers,
Boris

---

From: Boris Mironov <[email-masked]>
Date: Thu, 12 Mar 2026 14:28:07 +0000

Hi Egor,

Thank you very much for your time reviewing this patch
and guiding through some of its inefficiencies.

Yahoo!

Best regards,
Boris

---

From: Fujii Masao <[email-masked]>
Date: Fri, 20 Mar 2026 00:29:46 +0900

> 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

---

From: Boris Mironov <[email-masked]>
Date: Fri, 20 Mar 2026 02:59:01 +0000

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?

> performs better. If not, it may not be worth supporting these additional data

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-masked]>
Sent: March 19, 2026 11:29 AM
To: Boris Mironov <[email-masked]>
Cc: Madyshev Egor <[email-masked]>; Hayato Kuroda (Fujitsu) <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: Idea to enhance pgbench by more modes to generate data (multi-TXNs, UNNEST, COPY BINARY)

pgbench -i -Idtg -s1000

[ Client / Binary mode ]
pgbench -i -Idtc -s100

pgbench -i -Idtc -s1000

pgbench -i -IdtG -s1000

[ Server / Unnest ]
pgbench -i -IdtU -s100

pgbench -i -IdtU -s1000

Regards,

--
Fujii Masao