From: | Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com> |
---|---|
To: | Christophe Courtois <christophe(dot)courtois(at)dalibo(dot)com> |
Cc: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Big table and partition |
Date: | 2023-10-15 20:46:24 |
Message-ID: | CAJk5AtbY+-viwdX5Gor7nwcY8QpEjGTW28uLBwMKLxSVtOxPfA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
The columns are id, blocknumber and jsondata.
I have not been told anything ABT data retention and I will ask them.
Problem I am trying to resolve is, 30gb is big 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.
Incase I am doing partition, Plz provide any script that does partitioning
automatically. We are using postgres version 15.
On Sun, 15 Oct, 2023, 8:07 PM Christophe Courtois, <
christophe(dot)courtois(at)dalibo(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2023-10-15 21:03:49 | Re: Big table and partition |
Previous Message | Christophe Courtois | 2023-10-15 14:37:08 | Re: Big table and partition |