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 1qsIeG-007U4b-1n for pgsql-admin@arkaria.postgresql.org; Mon, 16 Oct 2023 08:08:23 +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 1qsIeD-00Gq4k-OH for pgsql-admin@arkaria.postgresql.org; Mon, 16 Oct 2023 08:08:22 +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 1qs8Qt-00CpD3-0B for pgsql-admin@lists.postgresql.org; Sun, 15 Oct 2023 21:13:55 +0000 Received: from mail-cy.bbox.fr ([194.158.98.48]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qs8Qo-000qdX-3R for pgsql-admin@lists.postgresql.org; Sun, 15 Oct 2023 21:13:54 +0000 Received: from mail.jppozzi.dyndns.org (unknown [176.187.84.182]) by mail-cy.bbox.fr (Postfix) with ESMTP id D5D8138; Sun, 15 Oct 2023 23:13:48 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by mail.jppozzi.dyndns.org (Postfix) with ESMTP id 1E7BB300572; Sun, 15 Oct 2023 23:13:48 +0200 (CEST) Received: from mail.jppozzi.dyndns.org ([127.0.0.1]) by localhost (mail.jppozzi.dyndns.org [127.0.0.1]) (amavis, port 10032) with ESMTP id iKC4OHdon263; Sun, 15 Oct 2023 23:13:47 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by mail.jppozzi.dyndns.org (Postfix) with ESMTP id AE46530058F; Sun, 15 Oct 2023 23:13:47 +0200 (CEST) X-Virus-Scanned: amavis at mail.jppozzi.dyndns.org Received: from mail.jppozzi.dyndns.org ([127.0.0.1]) by localhost (mail.jppozzi.dyndns.org [127.0.0.1]) (amavis, port 10026) with ESMTP id zneqb1lmU6cm; Sun, 15 Oct 2023 23:13:47 +0200 (CEST) Received: from mail.jppozzi.dyndns.org (mail.jppozzi.dyndns.org [192.168.2.21]) by mail.jppozzi.dyndns.org (Postfix) with ESMTP id 8017C300572; Sun, 15 Oct 2023 23:13:47 +0200 (CEST) Date: Sun, 15 Oct 2023 23:13:47 +0200 (CEST) From: JP Pozzi To: Ron Cc: pgsql-admin Message-ID: <1688850455.1480.1697404427434.JavaMail.zimbra@jppozzi.dyndns.org> In-Reply-To: References: Subject: Re: Big table and partition MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_2836fac5-c879-4bcc-8fae-5c74e98e5b5d" X-Originating-IP: [192.168.2.8] X-Mailer: Zimbra 8.8.15_GA_4562 (ZimbraWebClient - FF115 (Linux)/8.8.15_GA_3) Thread-Topic: Big table and partition Thread-Index: U2FbTfwnFebj49ndbSptpV+9/7eDQw== X-VADE-SPAMSTATE: clean X-VADE-SPAMSCORE: 0 X-VADE-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgedvkedrieekgdduheefucetufdoteggodetrfdotffvucfrrhhofhhilhgvmecuuefqfggjifgfgffupdcuqfgfvfdpucggtfgfnhhsuhgsshgtrhhisggvnecuuegrihhlohhuthemuceftddtnecunecujfgurhepfffhvfevkfgjfhfugggtihfothesrgdttgerredtjeenucfhrhhomheplffrucfrohiiiihiuceojhhpphesjhhpphhoiiiiihdrugihnhgunhhsrdhorhhgqeenucggtffrrghtthgvrhhnpefgtedvhffgffdtvedvueehueeiudejffeggfffhfejlefgjeeihedtieejuddvffenucfkphepudejiedrudekjedrkeegrddukedvpdduledvrdduieekrddvrdeknecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehinhgvthepudejiedrudekjedrkeegrddukedvpdhhvghlohepmhgrihhlrdhjphhpohiiiihirdguhihnughnshdrohhrghdpmhgrihhlfhhrohhmpehjphhpsehjphhpohiiiihirdguhihnughnshdrohhrghdpnhgspghrtghpthhtohepvddprhgtphhtthhopehrohhnlhhjohhhnhhsohhnjhhrsehgmhgrihhlrdgtohhmpdhrtghpthhtohepphhgshhqlhdqrggumhhinheslhhishhtshdrphhoshhtghhrvghsqhhlrdhorhhg List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_2836fac5-c879-4bcc-8fae-5c74e98e5b5d Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hello,=20 Partitionning is not so simple, it is better to have a key (or part of key)= or a date to make=20 useful partitionning decision.=20 If you want to suppress (or archive and suppress) some data it is simple to= "drop" a whole=20 partition. Also if the partitionning key (or part ok key) is in most querie= s it is a good solution=20 for better performance.=20 Regards=20 JP P=20 De: "Ron" =20 =C3=80: "pgsql-admin" =20 Envoy=C3=A9: Dimanche 15 Octobre 2023 23:03:49=20 Objet: Re: Big table and partition=20 On 10/15/23 15:46, Rajesh Kumar wrote:=20 The columns are id, blocknumber and jsondata.=20 No date column?=20 How much json data?=20 BQ_BEGIN I have not been told anything ABT data retention and I will ask them.=20 Problem I am trying to resolve is, 30gb is big=20 BQ_END Big is relative. 30GB in 2023 is pretty darned small.=20 BQ_BEGIN and it will be every growing always. By doing this, i am making the scan ea= sier (despite having indexes).=20 Growth will be 10gb per year data.=20 BQ_END That's less than a gigabyte per month.=20 BQ_BEGIN Incase I am doing partition, Plz provide any script that does partitioning = automatically. We are using postgres version 15.=20 BQ_END Keep It Simple, Stanley and just DELETE WHERE id < nnnnnnn ; . Do that ever= y month.=20 BQ_BEGIN On Sun, 15 Oct, 2023, 8:07 PM Christophe Courtois, < [ mailto:christophe.co= urtois@dalibo.com | christophe.courtois@dalibo.com ] > wrote:=20 BQ_BEGIN Hi,=20 Le 14/10/2023 =C3=A0 22:46, Rajesh Kumar a =C3=A9crit :=20 > In my env, I have a table 30GB where data is getting inserted directly=20 > whenever the new block is created in block chain...=20 > I have been asked to do partition, i said manager since we are not at=20 > all using the past data, partition is not required. Instead we can stop= =20 > the sync (downtime not required), change the name of table , create a=20 > new table with same structure with original name and keep only tis month= =20 > data. Then sync the blockchain so that new data will come to newly=20 > created table and old data will be there simply as a storage as we r not= =20 > selecting any query from old data...=20 In fact, you want to do manual partitioning.=20 Why reinvent the wheel?=20 > Note: on a longer run, data will keep on coming in whenever new block is= =20 > created.=20 > Is that right ? Any suggestions ? Better options ?=20 What problem do you have to solve? 30 GB is not so big, but since when=20 do you store data? Will you purge the table? What is the main access key=20 of your queries (critical for the partitioning key)?=20 Without other information I'd say that if you never read the former=20 months again, partition by month, perhaps by year.=20 --=20 Christophe Courtois=20 Consultant DALIBO=20 BQ_END BQ_END --=20 Born in Arizona, moved to Babylonia.=20 --=_2836fac5-c879-4bcc-8fae-5c74e98e5b5d Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hello,

Partitionning is not so simple, it is better to have a key (o= r part of key)  or a date to make
u= seful partitionning decision.
If you wan= t 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) i= s in most queries it is a good solution
= for better performance.

Regards

JP P

<= hr id=3D"zwchr" data-marker=3D"__DIVIDER__">
De: "Ron" <ronljohnsonjr@gmail.com>
=C3=80: "pgsql= -admin" <pgsql-admin@lists.postgresql.org>
Envoy=C3=A9: Dim= anche 15 Octobre 2023 23:03:49
Objet: Re: Big table and partition=

On 10/15/23 1= 5:46, Rajesh Kumar wrote:
=20
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:0= 7 PM Christophe Courtois, <chr= istophe.courtois@dalibo.com> wrote:
Hi,

Le 14/10/2023 =C3=A0 22:46, Rajesh Kumar a =C3=A9crit :<= br> > 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.

--=_2836fac5-c879-4bcc-8fae-5c74e98e5b5d--