MIME-Version: 1.0
References: 
 <DS0PR08MB9565D91414C65B3AC363825488CFA@DS0PR08MB9565.namprd08.prod.outlook.com>
In-Reply-To: 
 <DS0PR08MB9565D91414C65B3AC363825488CFA@DS0PR08MB9565.namprd08.prod.outlook.com>
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Thu, 13 Nov 2025 15:47:15 +0530
Message-ID: 
 <CAExHW5vtdtd5QnFobxOhbVeh5jk3_61zYRxLqCXEZpO3jOeFDg@mail.gmail.com>
Subject: Re: Idea to enhance pgbench by more modes to generate data
 (multi-TXNs, UNNEST, COPY BINARY)
To: Boris Mironov <boris_mironov@outlook.com>
Cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAExHW5vtdtd5QnFobxOhbVeh5jk3_61zYRxLqCXEZpO3jOeFDg%40mail.gmail.com>
Precedence: bulk

Hi Boris,

On Wed, Nov 12, 2025 at 3:25=E2=80=AFAM Boris Mironov <boris_mironov@outloo=
k.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 pgbench tool and its default 2 mod=
es for client- and server-side generation for TPC-B tests. When I use "scal=
e" 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 h=
ypothesises:
>
> will INSERT mode work faster if we commit once every "scale" and turn sin=
gle INSERT into "for" loop with commits for 3 tables in the end of each loo=
p
> 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 ut=
ility 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 o=
n GitHub. As of November 11, 2025 I can merge with main branch of the proje=
ct on GitHub.
>
> Spoiler alert: "COPY FROM BINARY" is significantly faster than current "C=
OPY 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?

--=20
Best Wishes,
Ashutosh Bapat