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