Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rL65A-0066SG-Rw for pgsql-general@arkaria.postgresql.org; Wed, 03 Jan 2024 18:35:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rL659-007WK4-Hv for pgsql-general@arkaria.postgresql.org; Wed, 03 Jan 2024 18:35:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rL61a-007Qyy-74 for pgsql-general@lists.postgresql.org; Wed, 03 Jan 2024 18:31:30 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rL61V-00FNSR-Oy for pgsql-general@lists.postgresql.org; Wed, 03 Jan 2024 18:31:29 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 4A62E5C0262; Wed, 3 Jan 2024 13:31:23 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute2.internal (MEProxy); Wed, 03 Jan 2024 13:31:23 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm1; t=1704306683; x=1704393083; bh=EhEVTuCfQH wH9SfsvfAC6VRGEg0gzbsI9YgEn9E7Qzc=; b=bH0O0iLewZjPOx4IDguZHkKrhU 4teSkZ1o7WJLxZ8G/PPGUQDCKLPiNbJdObtYk+2JGnVvGMmjbK5je8ntfLO2TSaH zaTKZpZUaJkUWd+9boZ6PIAy076OJf6LC8FT/KolAWZii5v7pMrSpsjZQRIx56T0 BsUXe3M7id+IzD4rJZ+U1960CnSXPQ21y8O92z1kd6YdaxKZuVVHyhXL96U6megB 3uyg682bxoviLM6yguGWMVil3ScP8sEYbp+vmDxAKqH8kZcpAfC/SdbAHc2R5yRZ OvWWjsBcmbveMsld9mu6UFFuW5XgZg9ECxF8XiBBrqYcRcwiwB/oYmYXebZQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm2; t=1704306683; x=1704393083; bh=EhEVTuCfQHwH9SfsvfAC6VRGEg0g zbsI9YgEn9E7Qzc=; b=MCztXc0OO8Xf72ZtBlSM4dFT04QIyzlM3rrLn9PKh197 4mKrJk7DnqaBzfyCXXs37gHfAVJWzW7CjUMOBPvJed653BVGeb4Y93LhXQXaoMzO gSV/2pWM0UUPAp7FJN3XXkhuuSBoQAzDlPhDEs8BKJ1QFyAlwS8nFqCxTJRmnpPI vxHa9pIfhBrfw7Na1bDarTJXEJ4MSB7Ov6hGNZeMcqpwBEGvkrsE87W7V5xfb99f gJF+zbL9HGj+lzUanpFYLlUYHlRasuJJiAF2v5zeU0NpY2MRaNMp+axAzx4ECi// yLec9uZ0Z43U2+P7ktmoRxi8asof3ebD+PsMU3EJRg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdeghedguddugecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurheptgfkffggfgfuvfevfhfhjgesrg dtreertddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpedukeejgf dtfeekvefhueekvefhheefveefkeelvddvffeukeekueeiieffudejteenucffohhmrghi nhepphhoshhtghhrvghsqhhlrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrg hrrghmpehmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdr tghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 3 Jan 2024 13:31:22 -0500 (EST) Content-Type: multipart/alternative; boundary="------------y7LMK07wD3hK3wcykByGUZAi" Message-ID: <49380122-9643-43f0-bbef-bd34ab6d434d@aklaver.com> Date: Wed, 3 Jan 2024 10:31:20 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Sample data generator for performance testing To: arun chirappurath Cc: pgsql-general@lists.postgresql.org References: <42a39fc7-e5e1-4ee7-ab9e-a2da4bf5209f@aklaver.com> <25f33c90-38e6-4f86-940f-05ed6baf27ab@aklaver.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------y7LMK07wD3hK3wcykByGUZAi Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/3/24 9:50 AM, arun chirappurath wrote: > > > On Wed, 3 Jan, 2024, 23:03 Adrian Klaver, > wrote: > > On 1/3/24 09:24, arun chirappurath wrote: > > Hi Adrian, > > > > Thanks for your mail. > > > > Is this for all tables in the database or a subset? Yes > > Yes all tables or yes just some tables? > All tables.except some which has user details. > > > > > > Does it need to deal with foreign key relationships? No > > > > What are the sizes of the existing data and what size sample > data do you > > want to produce?1Gb and 1Gb test data. > > If the source data is 1GB and the test data is 1GB then there is no > sampling, you are using the data population in its entirety. > > Yes.would like to double the load and test. > Does that mean you want to take the 1GB of your existing data and double it to 2GB while maintaining the data distribution from the original data? > > Also do we have any standard methods for sampling and generating test data Something like?: https://www.postgresql.org/docs/current/sql-select.html "|TABLESAMPLE /|sampling_method|/ ( /|argument|/ [, ...] ) [ REPEATABLE ( /|seed|/ ) ]| A |TABLESAMPLE| clause after a /|table_name|/ indicates that the specified /|sampling_method|/ should be used to retrieve a subset of the rows in that table. This sampling precedes the application of any other filters such as |WHERE| clauses. The standard PostgreSQL distribution includes two sampling methods, |BERNOULLI| and |SYSTEM|, and other sampling methods can be installed in the database via extensions ... " Read the rest of the documentation for TABLESAMPLE to get the details. > > > > > > On Wed, 3 Jan, 2024, 22:40 Adrian Klaver, > > > wrote: > > > >     On 1/2/24 23:23, arun chirappurath wrote: > >      > Hi All, > >      > > >      > 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 > > > > > > > >      > > >      > Regards, > >      > Arun > > > >     -- > >     Adrian Klaver > > adrian.klaver@aklaver.com > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > --------------y7LMK07wD3hK3wcykByGUZAi Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 1/3/24 9:50 AM, arun chirappurath wrote:


On Wed, 3 Jan, 2024, 23:03 Adrian Klaver, <adrian.klaver@aklaver.com> wrote:
On 1/3/24 09:24, arun chirappurath wrote:
> Hi Adrian,
>
> Thanks for your mail.
>
> Is this for all tables in the database or a subset? Yes

Yes all tables or yes just some tables?
All tables.except some which has user details. 

>
> Does it need to deal with foreign key relationships? No
>
> What are the sizes of the existing data and what size sample data do you
> want to produce?1Gb and 1Gb test data. 

If the source data is 1GB and the test data is 1GB then there is no
sampling, you are using the data population in its entirety.

Yes.would like to double the load and test.


Does that mean you want to take the 1GB of your existing data and double it to 2GB while maintaining 

the data distribution from the original data?



Also do we have any standard methods for sampling and generating test data


Something like?:


https://www.postgresql.org/docs/current/sql-select.html


"TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]

A TABLESAMPLE clause after a table_name indicates that the specified sampling_method should be used to retrieve a subset of the rows in that table. This sampling precedes the application of any other filters such as WHERE clauses. The standard PostgreSQL distribution includes two sampling methods, BERNOULLI and SYSTEM, and other sampling methods can be installed in the database via extensions

...
"

Read the rest of the documentation for TABLESAMPLE to get the details.





>
> On Wed, 3 Jan, 2024, 22:40 Adrian Klaver, <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 1/2/24 23:23, arun chirappurath wrote:
>      > Hi All,
>      >
>      > 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
>
>
>
>      >
>      > Regards,
>      > Arun
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

--------------y7LMK07wD3hK3wcykByGUZAi--