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 <lron...@tce.sp.gov.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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
