Re: Fast data, slow data

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.

On Fri, Jun 27, 2014 at 2:49 AM, Shaun Thomas <sthomas(at)optionshouse(dot)com>
wrote:

> 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
>