pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeremy Schneider <schneider@ardentperf.com>
To: arun chirappurath <arunsnmimt@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Sample data generator for performance testing
Date: Wed, 3 Jan 2024 09:56:53 -0800
Message-ID: <ecdd077c-cc28-4f61-bdc1-8fa7a8912d90@ardentperf.com> (raw)
In-Reply-To: <CAA23Sdv+0PnmpkK1CtHMzVBuHmw09m-aBefEDL2PTRv5rFsB+w@mail.gmail.com>
References: <CAA23Sdv+0PnmpkK1CtHMzVBuHmw09m-aBefEDL2PTRv5rFsB+w@mail.gmail.com>

On 1/2/24 11:23 PM, arun chirappurath wrote:
> Do we have any open source tools which can be used to create sample data
> at scale from our postgres databases?
> Which considers data distribution and randomness

I would suggest to use the most common tools whenever possible, because
then if you want to discuss results with other people (for example on
these mailing lists) then you're working with data sets that are widely
and well understood.

The most common tool for PostgreSQL is pgbench, which does a TPCB-like
schema that you can scale to any size, always the same [small] number of
tables/columns and same uniform data distribution, and there are
relationships between tables so you can create FKs if needed.

My second favorite tool is sysbench. Any number of tables, easily scale
to any size, standardized schema with small number of colums and no
relationships/FKs.  Data distribution is uniformly random however on the
query side it supports a bunch of different distribution models, not
just uniform random, as well as queries processing ranges of rows.

The other tool that I'm intrigued by these days is benchbase from CMU.
It can do TPCC and a bunch of other schemas/workloads, you can scale the
data sizes. If you're just looking at data generation and you're going
to make your own workloads, well benchbase has a lot of different
schemas available out of the box.

You can always hand-roll your schema and data with scripts & SQL, but
the more complex and bespoke your performance test schema is, the more
work & explaining it takes to get lots of people to engage in a
discussion since they need to take time to understand how the test is
engineered. For very narrowly targeted reproductions this is usually the
right approach with a very simple schema and workload, but not commonly
for general performance testing.

-Jeremy


-- 
http://about.me/jeremy_schneider






view thread (8+ messages)  latest in thread

Message-ID: <ecdd077c-cc28-4f61-bdc1-8fa7a8912d90@ardentperf.com>
Permalink:  ../ecdd077c-cc28-4f61-bdc1-8fa7a8912d90@ardentperf.com/
Also on:    postgresql.org/message-id/ecdd077c-cc28-4f61-bdc1-8fa7a8912d90@ardentperf.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: schneider@ardentperf.com, arunsnmimt@gmail.com
  Subject: Re: Sample data generator for performance testing
  In-Reply-To: <ecdd077c-cc28-4f61-bdc1-8fa7a8912d90@ardentperf.com>
  References: <CAA23Sdv+0PnmpkK1CtHMzVBuHmw09m-aBefEDL2PTRv5rFsB+w@mail.gmail.com> <ecdd077c-cc28-4f61-bdc1-8fa7a8912d90@ardentperf.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox