Обсуждение: Idea to enhance pgbench by more modes to generate data (multi-TXNs, UNNEST, COPY BINARY)

• 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

Hi Boris,

On Wed, Nov 12, 2025 at 3:25 AM Boris Mironov <boris_mironov@outlook.com> wrote:
>
> Hello hackers,
>
> For some of my specific hardware tests I needed to generate big databases well beyond RAM size. Hence I turned to
pgbenchtool and its default 2 modes for client- and server-side generation for TPC-B tests. When I use "scale" factor
inrange of few thousands (eg, 3000 - 5000) data generation phase takes quite some time. I looked at it as opportunity
toprove/disprove 2 hypothesises: 
>
> will INSERT mode work faster if we commit once every "scale" and turn single INSERT into "for" loop with commits for
3tables 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
interestnot 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.

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

On Fri, Nov 14, 2025 at 8:51 PM Boris Mironov <boris_mironov@outlook.com> wrote:
>
> 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")?
>

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.

> 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?
>

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

Hello Boris,

I've reviewed and tested your patch. In some modes, I did observe a
performance improvement. However, in my opinion, the current set of
modes is not transparent enough.

I propose, by analogy with the existing 'g'/'G' modes, to use lowercase
letters for client-side data generation and uppercase letters for
server-side generation. Furthermore, I propose considering making the
"one transaction per scale" mode a separate setting. This would result
in the following modes:
1.  g: COPY .. FROM STDIN .. TEXT, single transaction (orig. mode)
2.  c: COPY .. FROM STDIN .. BINARY, single transaction (added mode)
3.  G: INSERT .. SELECT generate_series, single transaction (orig. mode)
4.  I: INSERT .. SELECT unnest, single transaction (added mode)
And: M: multiple transactions. A setting that, when used, makes a mode
run with a transaction for each scale instead of a single transaction.
This would yield 8 possible combinations.

It would be reasonable to first collect performance measurements for
these modes and then decide whether to keep them, before proceeding with
a full implementation including their selection.
The provided measurements do not seem conclusive. For example, in mode
G(1) at scale 200, the result was 46.64, while the next measurement for
G(2) was 56.6, while mode i(3) falls between them at 47.63. Could you
please describe how you collected the performance measurements? Is it
possible that the measurement deviations significantly affected the
results shown in the table? It would be correct to take measurements
several times and then present a table with averages.

> 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.

It seems incorrect to me to make a decision to abandon the freeze
optimization solely because of another patch, especially one that is
not yet in master. Please provide more motivation on why using one
transaction per scale is more beneficial than a single transaction
combined with the freeze optimization. Having a setting to switch
the transaction mode would allow avoiding this trade-off.

Thus, I propose reconsidering the approach to data generation modes
and adding a setting to control the number of transactions.
I also suggest conducting new, more accurate performance measurements to
inform the decision on the necessity of the additional generation modes.

Best regards,
Egor

Hello, Boris

Thank you for making new productivity measurements. I`ve reviewed the v7
version and have some feedback below.

1. Perhaps we should rename functions initRowMethodBin to
initRowMethodBinary, initPopulateTableText to initPopulateTableTextCopy,
initPopulateTableBinary to initPopulateTableBinaryCopy,
initGenerateDataClientSideText to initGenerateDataClientSideTextFrmt,
initGenerateDataClientSideBinary to initGenerateDataClientSideBinaryFrmt
for better clarity.

2. The --help output currently describes only the 'M' mode. Should we
also add a description for the 'S' mode for completeness?

3. I`m wondering if the default value for the 'filler' column in
initCreateTables is necessary? The current functionality seems
unaffected, so perhaps we could avoid changing this function to keep the
diff minimal.

4. I noticed that vanilla client functionality in M mode is not
implemented. Is there a specific reason for this? It seems feasible to
implement by passing a counter, similar to how it`s done in
initPopulateTableBinary. If there are reasons not to implement it, in
mode Mg pgbench should not run mode Sg, it just pg_fatal.

5. In mode client binary format generation c, It would be the right
thing to do implement write progress of generating data and 'quiet' mode,
as it already implement in client text format generation g.

6. In bufferData, when len == 1, we call bufferCharData, which already
increments bin_copy_buffer_length. However, at the end of bufferCharData
we increment it again, leading to a double increment.

7. I suggest adding column count in function initPopulateTableBinary,
and initBranchBinary, initTellerBinary, initAccountBinary, to pass it
from initAccountBinary to init_row, and use it in functions
initBranchBinary, initTellerBinary, initAccountBinary. This will
increase the readability of the code, and remove the magic numbers in
the addColumnCounter call.

8. I think check and install data_generation_type in function
checkInitSteps is not quite right. In the current realization, pgbench
allows run data generation many times (dtCdtC...), so i suggest do not
touching this functionality. My suggestion would be to revert all the
changes from function checkInitSteps, set the data_generation_type in
switch in runInitSteps and remove call function checkInitSteps
from main.


Best regards,
Egor

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

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

Hello, Boris,

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).

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

Thank you,
Egor