This is what I was thinking but I am worried about two things.
1. If there is a very large set of data in the table that needs to be moved this will be slow and might throw locks which would impact the performance of the inserts and the updates.
2. Constantly deleting large chunks of data might cause vacuum problems.
I don't think partitioning is a good idea in this case because the partitions will be for small time periods (5 to 15 minutes).
Actually, partitioning might be exactly what you want, but not in the way you might think. What you've run into is actually a pretty common usage pattern. How we solve problems like this where I work is to use table inheritance alone. Consider this:
CREATE TABLE my_table ( ... columns );
CREATE TABLE my_table_stable (INHERITS my_table);
Then you create a job that runs however often you want, and all that job does, is move old rows from my_table, to my_table_stable. Like so:
BEGIN;
INSERT INTO my_table_stable SELECT * FROM ONLY my_table WHERE date_col >= now() - INTERVAL '15 minutes';
DELETE FROM ONLY my_table WHERE date_col >= now() - INTERVAL '15 minutes';
COMMIT;
Or whatever. But you get the idea.
This way, you still get all the data by selecting from my_table, but the data is partitioned in such a way that you can put the high turnover table in another tablespace, or otherwise modify it for performance reasons.
-- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com
