Re: Big table and partition

As said, we have timetamp in jsonb, incase if we want to partition.

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

On Mon, 16 Oct, 2023, 1:38 PM JP Pozzi, <jpp(at)jppozzi(dot)dyndns(dot)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(at)gmail(dot)com>
> *À: *"pgsql-admin" <pgsql-admin(at)lists(dot)postgresql(dot)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(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
>>
>
> --
> Born in Arizona, moved to Babylonia.
>
>