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(at)optionshouse(dot)com

______________________________________________

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