Hello everybody! I have searched quite a bit, but haven't found a setup like the ours, so I decided to ask if I'm missing something: 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. Our current production server is 9.4 and the main application uses hybernate as persistence mechanism to access the database. Some of the main points of our environment are: 1) There are a lot of inserts (millions, some days) but no updates or deletes; 2) There are a lot of foreign keys between most tables; 3) There are some very long select statements; 4) We can't touch any point of the application. Taking into account those, the "usual" partitioning path would bring more problems than solutions because: 1) The issue between trigger redirection and hybernate; The foreign keys dont propagate down the hierarchy 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 Sure, we would have to move data between partitions and update the constraints of the tables every year but, it doesn't look like that much of a burden, since there will be only a few tables. The question is: is there something wrong that I am not seeing here? 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... Thanks for your time and sorry for such a big bunch of words Luiz Hugo Ronqui -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
