Re: Table partition - parent table use

Luiz:

1st thing, do not top-quote. It's hard to read and I, personally,
consider it insulting ( not the first time it's done, and for obvious
reasons ).

On Fri, Sep 15, 2017 at 4:24 PM, Luiz Hugo Ronqui <lronqui(at)tce(dot)sp(dot)gov(dot)br> wrote:
> Our usage allows us to insert all rows into the hot partition, since its a rare event to receive data that otherwise would have 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 problem of always being searched is not for recent rows, but for
historic. Imagine hot=2016-7, warm=2013-5 and cold=rest

If hot=parent and you make a query for 2014 data it's going to search
hot and warm, not just warm. If hot!=parent it is going to search
parent and warm ( and use a seq-scan in parent in the normal case, as
stats show it as empty , and it will be if things are going well ).

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

I know, my recomendation was to made them in such a way that once a
row lands in an historic partition it never moves if you use more than
one ( i.e., use things as cold-200x, cold-201x, not cold-prev-decade,
cold-two-decades-ago )

> 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 before because of the size of the original message.

We normally do the schema trick, and as 90% of data is in historic
schema, we skip most of it.

Francisco Olarte.