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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
