Re: Fast data, slow data

On 06/26/2014 04:29 AM, Tim Uckun wrote:

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

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email