From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Big table and partition
Date: Mon, 16 Oct 2023 08:33:03 -0500
Message-ID: <474fd0b8-2dde-4947-b464-b697daa0ce1d@gmail.com> (raw)
In-Reply-To: <CAJk5AtZWJfm_E5sdOdAQxaeuGTTZcOuJ3fPjDh55zxU5WnF_EQ@mail.gmail.com>
References: <CAJk5AtYv5x9_j9HtRtt_7JE-p-Fs2sA_kb2u4fbNcwwsqNVqMQ@mail.gmail.com>
<c2dcb0ce-9172-438d-a754-557bfd55f0f2@dalibo.com>
<CAJk5AtbY+-viwdX5Gor7nwcY8QpEjGTW28uLBwMKLxSVtOxPfA@mail.gmail.com>
<b161e000-3b9f-4097-9094-ff1a301ed99a@gmail.com>
<1688850455.1480.1697404427434.JavaMail.zimbra@jppozzi.dyndns.org>
<CAJk5AtZWJfm_E5sdOdAQxaeuGTTZcOuJ3fPjDh55zxU5WnF_EQ@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Big table and partition
In-Reply-To: <474fd0b8-2dde-4947-b464-b697daa0ce1d@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox