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

From: Madyshev Egor <[email-masked]>
Date: Fri, 30 Jan 2026 13:22:30 +0000

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

---

From: Boris Mironov <[email-masked]>
Date: Fri, 30 Jan 2026 14:47:54 +0000

Hello Egor,

Thank you very much for taking this patch under your wing!

> This would yield 8 possible combinations.

Sure thing. I agree with your proposal to add more flexibility to
parameters with single exception. For UNNEST test I would suggest
to use "U" instead of "I" as it might be confusing later in case of
another patch from current CommitFest will make it into the master.
I'm referring to:

https://commitfest.postgresql.org/patch/6242/

It uses parameter "-i" to start use multiple threads to populate tables.

> a full implementation including their selection.

Since logic will be slightly different by following your proposal new set
of metrics will be required. That's for sure.

My main motivation in splitting one huge transaction to fill tables
into smaller ones comes from another idea that was put on
a backburner - running data population via multiple threads. This
idea is implemented in above mentioned patch by Mircea Cadariu.
By amount of changes in that patch it is clear that we're quite equal
by number of lines. Hence putting the change into my patch would
be overwhelming for any reviewer.

Another reason for smaller in size transactions ("one per scale")
is my experience during generation of test databases that are much
bigger than host RAM (e.g., scale=5000). Data population phase is
not just slow, but more than often has to use multiple checkpoints
for such single transaction because even my max_wal_size was
smaller than size of such "change". One might argue that my DB
is not tuned properly, but it's a topic for another day. As a side
effect of decision to use multiple transactions raises another
issue - inability to use FREEZE optimisation for COPY commands
which leads to Autovacuum storm in turn even during very process
of data population.

> inform the decision on the necessity of the additional generation modes.

Agree and Agree. Both make perfect sense.

Best regards,
Boris

---

From: Boris Mironov <[email-masked]>
Date: Fri, 6 Feb 2026 10:48:03 +0000

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

---

From: Boris Mironov <[email-masked]>
Date: Fri, 6 Feb 2026 11:08:23 +0000

Resending patch as Excel spreadsheet took over second attachment in last email and testing bot got confused

---

From: Madyshev Egor <[email-masked]>
Date: Mon, 9 Feb 2026 13:19:45 +0000

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

---

From: Boris Mironov <[email-masked]>
Date: Mon, 23 Feb 2026 10:41:19 +0000

Hi Egor,

Here comes v8 of the patch with all requested changes. Sorry it took a little while to implement...

> initGenerateDataClientSideBinary to initGenerateDataClientSideBinaryFrmt

Done

>also add a description for the 'S' mode for completeness?

Fixed. Thanks for noticing

> unaffected, so perhaps we could avoid changing this function to keep the

That was done purely for debugging. Fixed

> mode Mg pgbench should not run mode Sg, it just pg_fatal.

I didn't do it initially to keep original logic intact. To unify new patch
with existing codebase I've added multi-transactional functionality
to client-side data generator and extracted "show progress" code
into own procedure. Now both client-side generators use it.

> as it already implement in client text format generation g.

Everything is unified now via new procedure showPopulateTableCopyProgress

> we increment it again, leading to a double increment.

Fixed. Thank you

> the addColumnCounter call.

Done

> switch in runInitSteps and remove call function checkInitSteps

I thought about such solution initially, but then decided to implement extra
procedure where I can count how many times we asked to initialize data.
Since this is legacy code and I can see need for ability to run init multiple
times (eg, get exact timing of different methods for comparison or run
same code multiple times to get average timing), I just show warning.

All in all, thanks a lot for your deep analysis of the code and great
suggestions on how to make better.

Best regards,
Boris