Обсуждение: 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