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 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 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 before because of the size of the original message. Luiz Hugo Ronqui -----Mensagem original----- De: pgsql-general-owner+m233282=lronqui=tce.sp.gov.br@xxxxxxxxxxxxxx [mailto:pgsql-general-owner+m233282=lronqui=tce.sp.gov.br@xxxxxxxxxxxxxx] Em nome de Francisco Olarte Enviada em: sexta-feira, 15 de setembro de 2017 08:37 Para: Luiz Hugo Ronqui Cc: pgsql-general@xxxxxxxxxxxxxx Assunto: Re: Table partition - parent table use Hi Luiz: On Thu, Sep 14, 2017 at 11:06 PM, Luiz Hugo Ronqui <lronqui@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
