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 1qsNiY-007nzW-4Q for pgsql-admin@arkaria.postgresql.org; Mon, 16 Oct 2023 13:33:10 +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 1qsNiV-000wD9-QY for pgsql-admin@arkaria.postgresql.org; Mon, 16 Oct 2023 13:33:08 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qsNiV-000wCd-7k for pgsql-admin@lists.postgresql.org; Mon, 16 Oct 2023 13:33:08 +0000 Received: from mail-yw1-x112b.google.com ([2607:f8b0:4864:20::112b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qsNiT-000l3o-1p for pgsql-admin@lists.postgresql.org; Mon, 16 Oct 2023 13:33:06 +0000 Received: by mail-yw1-x112b.google.com with SMTP id 00721157ae682-5a7e5dc8573so55759417b3.0 for ; Mon, 16 Oct 2023 06:33:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697463184; x=1698067984; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=TGxf2SSuntnJxpWa1pt59+j79BBgm/SUSVCj7RG6m0Q=; b=WNv8WIcDGc7ymkoS8r3ZXvA/eTads40uMp1IfqzKhjzoV3BzI1xaElpHpk2zYX4cYu CrtzzNQxd1vy7mTMCP4giUk9komRaD8ap11cYWi9YQwK8K7UQ9e/9qjoAP19NG6/RvSK dQVY5kT2DyBkZ632EF89b9ZEtn4Q8Mz8Jzyv5XxYhVvXcEKA9t7nE4czIPrXa/2A1wJj vHKu2j7MTPJmSwtbvsYfApz1FYK9dwMbXrWOinosUqqYtAo1by7jdthJWHRZU8+Cy8uO XISkUCMODV/M5z6xkqbBtgEobKVwTW3/PSz2RLGTLuv7eDXrzAcP++Ovm2nWoaqlGITw V3mw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697463184; x=1698067984; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=TGxf2SSuntnJxpWa1pt59+j79BBgm/SUSVCj7RG6m0Q=; b=N0+HYniJHBoTwFJ7DCdw6gfNzgQ9NaRdCfxDjKy7IvAeY/h0Ny+Mqt/Va+DRwPFtRy RF6E8jMaA+GeCpcnd6rqKnC43qOWZSBSKs3CLvhRg1jdhkLcyvo7bZCNVvWAIpL1kMRE vAYnbFruzphHk1NMnJtP2TU9ovh156uYh6BhxGWRYlyDdRn2KLnfH5wuhQBcDbz/tM0E UKG2JD6H3AdB71MjQdxg7kCwWJS4TYRvG0bcd7p1M7Yij9JlnXTLngKehaMdeD1Z6Lx8 xEX+wyPSVy7qL23cnDuR/HsG0I9+bliHHDdFiRtPFMPY8fcq1RZPB3SvMRgMHNsWoRqx Ik0Q== X-Gm-Message-State: AOJu0YzclL4yE1X0Ef1m4I5wSNIvPFWD5Zj8sauqAkGKYsyCwcGZ8iIH bRKeQ64ZmrHXMeiX8fthbagE93zLZ4o= X-Google-Smtp-Source: AGHT+IGLi8cdQVlwqWHTFoKMzRl9SgPAgChBfBW9fptGN0BQP7GXMnq1ZnTKxKO2R6Nyh8YnZUQlcw== X-Received: by 2002:a81:4e8c:0:b0:5a8:19c0:bb6b with SMTP id c134-20020a814e8c000000b005a819c0bb6bmr10708734ywb.36.1697463183653; Mon, 16 Oct 2023 06:33:03 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:40a2:fd1b:5726:298c? ([2600:1700:bd2c:b810:40a2:fd1b:5726:298c]) by smtp.googlemail.com with ESMTPSA id h83-20020a815356000000b005a82f14b8dbsm2199484ywb.49.2023.10.16.06.33.03 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 16 Oct 2023 06:33:03 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------xbIOePEbvUz1C27gMdXeiGb5" Message-ID: <474fd0b8-2dde-4947-b464-b697daa0ce1d@gmail.com> Date: Mon, 16 Oct 2023 08:33:03 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Big table and partition Content-Language: en-US To: pgsql-admin References: <1688850455.1480.1697404427434.JavaMail.zimbra@jppozzi.dyndns.org> From: Ron 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. --------------xbIOePEbvUz1C27gMdXeiGb5 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/16/23 08:09, Rajesh Kumar wrote: > As said, we have timetamp in jsonb, incase if we want to partition. That's not going to work.  The timestamp must be it's own column. > > How do I convince them that partition is not needed for this table? By demonstrating that DELETE WHERE id < nnnnnnn; is Fast Enough.  (And remember what your mother told you about not stuffing your mouth full of food: delete in bite-sized chunks.  There's no need to delete half the records in one statement.) > > On Mon, 16 Oct, 2023, 1:38 PM JP Pozzi, wrote: > > Hello, > > Partitionning is not so simple, it is better to have a key (or part of > key)  or a date to make > useful partitionning decision. > If you want to suppress (or archive and suppress) some data it is > simple to "drop" a whole > partition. Also if the partitionning key (or part ok key) is in most > queries it is a good solution > for better performance. > > Regards > > JP P > > ---------------------------------------------------------------------------- > *De: *"Ron" > *À: *"pgsql-admin" > *Envoyé: *Dimanche 15 Octobre 2023 23:03:49 > *Objet: *Re: Big table and partition > > On 10/15/23 15:46, Rajesh Kumar wrote: > > The columns are id, blocknumber and jsondata. > > > No date column? > > How much json data? > > > I have not been told anything ABT data retention and I will ask them. > > Problem I am trying to resolve is, 30gb is big > > > Big is relative.  30GB in 2023 is pretty darned small. > > and it will be every growing always. By doing this, i am making > the scan easier (despite having indexes). > >  Growth will be 10gb per year data. > > > That's less than a gigabyte per month. > > > Incase I am doing partition, Plz provide any script that does > partitioning automatically. We are using postgres version 15. > > > *Keep It Simple, Stanley* and just DELETE WHERE id < /nnnnnnn/;. Do > that every month. > > > On Sun, 15 Oct, 2023, 8:07 PM Christophe Courtois, > wrote: > > Hi, > > Le 14/10/2023 à 22:46, Rajesh Kumar a écrit : > > In my env, I have a table 30GB where data is getting > inserted directly > > whenever the new block is created in block chain... > > I have been asked to do partition, i said manager since we > are not at > > all using the past data, partition is not required. Instead > we can stop > > the sync (downtime not required), change the name of table , > create a > > new table with same structure with original name and keep > only tis month > > data. Then sync the blockchain so that new data will come to > newly > > created table and old data will be there simply as a storage > as we r not > > selecting any query from old data... > > In fact, you want to do manual partitioning. > Why reinvent the wheel? > > > Note: on a longer run, data will keep on coming in whenever > new block is > > created. > > Is that right ? Any suggestions ? Better options ? > > What problem do you have to solve? 30 GB is not so big, but > since when > do you store data? Will you purge the table? What is the main > access key > of your queries (critical for the partitioning key)? > > Without other information I'd say that if you never read the > former > months again, partition by month, perhaps by year. > > -- > Christophe Courtois > Consultant DALIBO > > > -- > Born in Arizona, moved to Babylonia. > -- Born in Arizona, moved to Babylonia. --------------xbIOePEbvUz1C27gMdXeiGb5 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 10/16/23 08:09, Rajesh Kumar wrote:
As said, we have timetamp in jsonb, incase if we want to partition.

That's not going to work.  The timestamp must be it's own column.


How do I convince them that partition is not needed for this table?

By demonstrating that DELETE WHERE id < nnnnnnn; is Fast Enough.  (And remember what your mother told you about not stuffing your mouth full of food: delete in bite-sized chunks.  There's no need to delete half the records in one statement.)


On Mon, 16 Oct, 2023, 1:38 PM JP Pozzi, <jpp@jppozzi.dyndns.org> wrote:
Hello,

Partitionning is not so simple, it is better to have a key (or part of key)  or a date to make
useful partitionning decision.
If you want to suppress (or archive and suppress) some data it is simple to "drop" a whole
partition. Also if the partitionning key (or part ok key) is in most queries it is a good solution
for better performance.

Regards

JP P

De: "Ron" <ronljohnsonjr@gmail.com>
À: "pgsql-admin" <pgsql-admin@lists.postgresql.org>
Envoyé: Dimanche 15 Octobre 2023 23:03:49
Objet: Re: Big table and partition

On 10/15/23 15:46, Rajesh Kumar wrote:
The columns are id, blocknumber and jsondata.

No date column?

How much json data?


I have not been told anything ABT data retention and I will ask them.

Problem I am trying to resolve is, 30gb is big

Big is relative.  30GB in 2023 is pretty darned small.

and it will be every growing always. By doing this, i am making the scan easier (despite having indexes).

 Growth will be 10gb per year data.

That's less than a gigabyte per month.


Incase I am doing partition, Plz provide any script that does partitioning automatically. We are using postgres version 15.

Keep It Simple, Stanley and just DELETE WHERE id < nnnnnnn;.  Do that every month.


On Sun, 15 Oct, 2023, 8:07 PM Christophe Courtois, <christophe.courtois@dalibo.com> wrote:
Hi,

Le 14/10/2023 à 22:46, Rajesh Kumar a écrit :
> In my env, I have a table 30GB where data is getting inserted directly
> whenever the new block is created in block chain...
> I have been asked to do partition, i said manager since we are not at
> all using the past data, partition is not required. Instead we can stop
> the sync (downtime not required), change the name of table , create a
> new table with same structure with original name and keep only tis month
> data. Then sync the blockchain so that new data will come to newly
> created table and old data will be there simply as a storage as we r not
> selecting any query from old data...

In fact, you want to do manual partitioning.
Why reinvent the wheel?

> Note: on a longer run, data will keep on coming in whenever new block is
> created.
> Is that right ? Any suggestions ? Better options ?

What problem do you have to solve? 30 GB is not so big, but since when
do you store data? Will you purge the table? What is the main access key
of your queries (critical for the partitioning key)?

Without other information I'd say that if you never read the former
months again, partition by month, perhaps by year.

--
Christophe Courtois
Consultant DALIBO

--
Born in Arizona, moved to Babylonia.


--
Born in Arizona, moved to Babylonia.
--------------xbIOePEbvUz1C27gMdXeiGb5--