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

From: Boris Mironov <[email-masked]>
Date: Tue, 11 Nov 2025 13:33:16 +0000

Hello hackers,

For some of my specific hardware tests I needed to generate big databases well beyond RAM size. Hence I turned to pgbench tool and its default 2 modes for client- and server-side generation for TPC-B tests. When I use "scale" factor in range of few thousands (eg, 3000 - 5000) data generation phase takes quite some time. I looked at it as opportunity to prove/disprove 2 hypothesises:

  *
will INSERT mode work faster if we commit once every "scale" and turn single INSERT into "for" loop with commits for 3 tables in the end of each loop
  *
will "INSERT .. SELECT FROM unnest" be faster than "INSERT .. SELECT FROM generate_series"
  *
will BINARY mode work faster than TEXT even though we send much more data
  *
and so on

As a result of my experiments I produced significant patch for pgbench utility and though that it might be of interest not just for me. Therefore I'm sending draft version of it in diff format for current development tree on GitHub. As of November 11, 2025 I can merge with main branch of the project on GitHub.

Spoiler alert: "COPY FROM BINARY" is significantly faster than current "COPY FROM TEXT"

Would be happy to polish it if there is interest to such change.

Cheers

---

From: Ashutosh Bapat <[email-masked]>
Date: Thu, 13 Nov 2025 15:47:15 +0530

Hi Boris,

> Would be happy to polish it if there is interest to such change.

Making pgbench data initialization faster at a higher scale is
desirable and the community might be willing to accept such a change.
Running very large benchmarks is becoming common these days. However,
it's not clear what you are proposing and what's the performance
improvement. Answering following question may help: Your patch
implements all the above methods? Do all of them provide performance
improvement? If each of them performs better under certain conditions,
what are those conditions? Is there one method which performs better
than all others, which is it and why not implement just that method?
What performance numbers are we looking at? Can the methods which use
batch commits, also run those batches in parallel?

-- 
Best Wishes,
Ashutosh Bapat

---

From: Boris Mironov <[email-masked]>
Date: Fri, 14 Nov 2025 15:21:12 +0000

Hi Ashutosh,

> If there is one method that is better than all others, community will
> be more willing to accept implementation of that one method than
> multiple implementations so as to reduce maintenance burden.

Ok then. I'll leave "COPY FROM STDIN BINARY" implementation out of 3 only.
Would you prefer to replace original COPY FROM STDIN TEXT by this
code or add it as new "init-step" (e.g., with code "c")?

I also have noted that current code doesn't prevent pgbench parameter
like "--init-steps=dtgG". It allows to run data generation step twice.
Each of these "g" and "G" will present own timing in status line. Is this
an oversight or intentional?

> The code in the patch does not have enough comments. It's hard to
> understand the methods just from the code. Each of the generateData*
> functions could use a prologue explaining the data generation method
> it uses.

To add comments is not a problem at all. So far, it was just "code for myself"
and I was checking if there is any interest in community to include it.

>> I'm sure that much more testing is required to run this code under different
>> conditions and hardware to get a better picture. So far it looks very promising.

> Sure.

Cheers,
Boris

---

From: Ashutosh Bapat <[email-masked]>
Date: Mon, 17 Nov 2025 10:28:08 +0530

> code or add it as new "init-step" (e.g., with code "c")?

TEXT copy may be useful for cross platform client side data
generation. BINARY might be useful for same platform client side
generation or server side generation. Just a thought, use TEXT or
BINARY automatically based on where it's cross-platform or same
platform setup.

> an oversight or intentional?

I would review the commit a386942bd29b0ef0c9df061392659880d22cdf43 and
the discussion thread
https://postgr.es/m/alpine.DEB.2.21.1904061826420.3678@lancre
mentioned in the commit message to find that out. At first glance it
looks like an oversight, but I haven't reviewed the commit and thread
myself. That thread might reveal why generate_series() was used
instead of BINARY COPY for server side data generation. If it needs to
change it's better to start a separate thread and separate patch for
that discussion.

--
Best Wishes,
Ashutosh Bapat

---

From: Boris Mironov <[email-masked]>
Date: Mon, 17 Nov 2025 12:43:52 +0000

Hi Ashutosh,

> BINARY automatically based on where it's cross-platform or same

It is true that BINARY format is not as flexible as TEXT. Postgres expects
data in wire to arrive in "network byte order". AFAIK only Solaris can
send its data without byte reordering. I support such exception via
#ifdef __sparc__

I don't see an easy way to make decision within pgbench on which
COPY mode to use TEXT or BINARY except specifically asking for one
via command line parameter. This is why I left flag "g" for TEXT mode
and added BINARY as "C" (for "Copy" and upper case as faster). I guess,
we can add alias "c" for old client-side generation as "copy", but slower
version of it.

While we are on topic of client- vs server-side generation. IMHO these are
quite misleading terms. Both of them are executed by RDBMS on server side,
but "server" one gets quite short query (and quite slow in execution) and
"client" one gets quite big network transfer (and quite fast in execution). The
reason is difference in data path that needs to reflected in documentation.
On top of it server-side thrashes DB cache, while client-side works via ring buffer
that doesn't allocate more than 1/8 of shared_buffers,

> change it's better to start a separate thread and separate patch for

Thank you for this hint. I went through whole thread and there they discuss
how to reflect certain behavior of init-steps and nothing about COPY BINARY.
Major point of generate_series() introduction is to send short query to DB
and not to worry about network performance. It is quite true that COPY
sends tons of data over network and it might be an issue for slow network.
They also touched on topic of "one huge transaction" for whole generated
dataset or few smaller transaction.

Allow me to repost my benchmarks here (as it was lost for pgsql-hasckers
because I just used Reply instead of Reply-To-All)

Tests:
Test |  Binary  | Init mode | Query and details
-----|----------|-----------|-------------------------------------------------------
1    | original |   G       | INSERT FROM generate_series in single huge transaction
2    | enhanced |   G       | INSERT FROM generate_series in single huge transaction
3    | enhanced |   i       | INSERT FROM generate_series in one transaction per scale
4    | enhanced |   I       | INSERT FROM unnest in one transaction per scale
5    | original |   g       | COPY FROM STDIN TEXT in single transaction
6    | enhanced |   g       | COPY FROM STDIN TEXT in single transaction
7    | enhanced |   C       | COPY FROM STDIN BINARY in single transaction

Test |      Scale and seconds to complete data generation        
     |   1  |   2  |  10  |  20  |  100  |  200  |  1000  |  2000
-----|------|------|------|------|-------|-------|--------|--------
1    | 0.19 | 0.37 | 2.01 | 4.34 | 22.58 | 46.64 | 245.98 | 525.99
2    | 0.30 | 0.47 | 2.18 | 4.37 | 25.38 | 56.66 | 240.89 | 482.63
3    | 0.18 | 0.39 | 2.14 | 4.19 | 23.78 | 47.63 | 240.91 | 483.19
4    | 0.18 | 0.38 | 2.17 | 4.39 | 23.68 | 47.93 | 242.63 | 487.33
5    | 0.11 | 0.22 | 1.46 | 2.95 | 15.69 | 32.86 | 154.16 | 311.00
6    | 0.11 | 0.22 | 1.43 | 2.89 | 16.01 | 29.41 | 158.10 | 307.54
7    | 0.14 | 0.12 | 0.56 | 1.16 |  6.22 | 12.70 |  64.70 | 135.58

"Original" binary is pgbench v17.6.
"Enhanced" binary is pgbench 19-devel with proposed patch.

As we can see another point of discussion in mentioned earlier
thread on pgsql-hackers said that multi transactions for init-step
do NOT bring any benefit. My numbers show some increase in
performance by simply INSERT-ing data in loop with one COMMIT
per "scale" on lower scales. On higher scales benefit dissapears. My
guess here is quite active process WAL archiver.

COPY TEXT is 36% faster than INSERT with multiple transactions.
COPY BINARY is ~72% faster than INSERT with multiple transactions.

At this point I'm torn between keeping old modes and logic for
backward compatibility and introduction of new modes for INSERT & COPY
versus simply replacing old less efficient logic with new one.

Sorry for quite long response.

Best regards,
Boris

________________________________
From: Ashutosh Bapat <[email-masked]>
Sent: November 16, 2025 11:58 PM
To: Boris Mironov <[email-masked]>
Cc: [email-masked] <[email-masked]>
Subject: Re: Idea to enhance pgbench by more modes to generate data (multi-TXNs, UNNEST, COPY BINARY)

--
Best Wishes,
Ashutosh Bapat

---

From: Boris Mironov <[email-masked]>
Date: Fri, 21 Nov 2025 13:26:05 +0000

Hi Ashutosh,

Just wanted to let you know that I've submitted this patch
to CommitFest (see https://commitfest.postgresql.org/patch/6245/)

Interestingly enough there is one more patch from Mircea Cadariu in the same
CommitFest about pgbench (https://commitfest.postgresql.org/patch/6242/)
That patch has been submitted few days ago and is proposing to run
data generation phase in parallel threads. It shows significant
improvements over performance of original single-thread code.

Hopefully sooner or later pgbench will get significant performance
gains in data generation from these two patches.

Original version of my patch failed in GitHub tests. Therefore I have
to start posting updated versions here.

Attached is updated version that sets default value for filler columns.
This trick allows significantly shrink network traffic for COPY FROM BINARY.
Absence of filler column in dataflow has failed my original patch in GitHub
pipeline.

I also switched from one huge transaction for COPY FROM BINARY to
"one per scale". This will simplify merge with multi-threaded data load
proposed by Mircea. Unfortunately, it killed possibility to freeze data right
away, which was possible when table truncation and data load was done
in the same transaction.

I think it would be fair to leave all original modes of data generation
until official review in CommitFest. Hence "INSERT SELECT FROM UNNEST"
is staying so far as there might be interest in community for benchmarking
of columnar tables (eg, for OLAP loads or Timescale DB).

Best regards,
Boris

---

From: Boris Mironov <[email-masked]>
Date: Sat, 22 Nov 2025 11:02:12 +0000

Hello,

Adding tests for new modes into Perl testing framework for pgbench.

The goal is to pass GitHub checks for the patch in green to simplify reviewer's life.

Cheers,
Boris

---

From: Boris Mironov <[email-masked]>
Date: Sun, 23 Nov 2025 07:51:30 +0000

Hello,

Updating code to satisfy compiler warnings about unused code.

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