RES: [GENERAL] Table partition - parent table use

Thanks for your tips!


Our usage allows us to insert all rows into the hot partition, since its a rare event to receive data that otherwise
wouldhave to be redirected to a "colder" partition. 

This way, its not a problem that the parent table would always be searched. In fact it would guarantee that these bits,
received"out of time", would get accounted.  

The number of partitions, especially the "cold" ones, is not a hard limit... we can expand it with time.

The idea includes schemas and tablespaces, along with its management benefits,  specifically for these partitioned
data.One of our current problems is exactly the time it takes for backup and restore operations. I did not mentioned it
beforebecause of the size of the original message. 


Luiz Hugo Ronqui


-----Mensagem original-----
De: pgsql-general-owner+m233282=lronqui=tce.sp.gov.br@postgresql.org
[mailto:pgsql-general-owner+m233282=lronqui=tce.sp.gov.br@postgresql.org]Em nome de Francisco Olarte 
Enviada em: sexta-feira, 15 de setembro de 2017 08:37
Para: Luiz Hugo Ronqui
Cc: pgsql-general@postgresql.org
Assunto: Re: [GENERAL] Table partition - parent table use

Hi Luiz:

On Thu, Sep 14, 2017 at 11:06 PM, Luiz Hugo Ronqui
<lronqui@tce.sp.gov.br> wrote:
...
> We have a database with data being inserted for almost 10 years and no
> policy defined to get rid of old records, even though we mostly use only the
> current and last year's data. Some etl processes run on older data from time
> to time.
> After this time, some tables have grown to a point where even their indexes
> are bigger than the server's available RAM. Because some queries were
> getting slower, despite optimizations, we started experimenting with table
> partitioning.
> The idea was creating 3 partitions for each table of interest: the "hot",
> the "warm" and the "cold". The first would have the last 2 years. The
> second, data from 3 to 5 years and the third, all the rest.

I would consider using more than one cold partition, and maybe moving
them AND warm to a different schema. Maybe 5 years in each, something
like cold-2000-2009, cold-2010-2019. You can update the constraints
adequately, but the thing is you periodically update your constraints
in the hot, warm and last cold, moving data among them appropiately,
then do a really good backup of warm and colds and you can forget
about them in daily backups, and also if you want to drop "stale" in
the future, or un-inherit them to speed up queries, it is easier to
do.

...
> Then one thing came to mind: Why not to use the "parent" table as the hot
> one, without doing any redirection at all? That way we could:
> 1)      Keep the referential integrity of the current model untouched;
> 2)      Dismiss the trigger redirection along with the hybernate issue;
> 3)      Have a much smaller dataset to use in most of our queries;
> 4)      Have all the historic data when needed

You can do it, but remember parent normally does not have constraints,
so it is always scanned ( fastly as it is known empty ). Also select
from only parent is useful to detect when you are missing partitions,
won't work in this case. But you can test it.

...
> I have run some basic tests and all seemed to work as expected, but since I
> couldn't find any use of  the parent table besides  being the head of the
> hierarchy, I am affraid of doing something that could stop  because it wasnt
> designed to work like that to begin with...

Seems fine to me. Never used that because y normally use special
insertion programs for my partitiones tables ( my usage allows thats
), so I insert directly in the appropiate partition always ( so I just
use inheritance, no triggers or rules ).

Francisco Olarte.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general