Re: Recommendations for partitioning?

On Thu, Dec 19, 2013 at 9:53 AM, Dave Johansen <davejohansen(at)gmail(dot)com> wrote:
>>
> On Sat, Dec 7, 2013 at 1:37 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> wrote:
>> I'll add that you can use assymetric partitioning if you tend to do a
>> lot of more fine grained queries on recent data and more big roll up
>> on older ones. I.e. partition by month except for the last 30 days, do
>> it by day etc. Then at the end of the month roll all the days into a
>> month partition and delete them.
>
> This sounds like a great solution for us. Is there some trick to roll the
> records from one partition to another? Or is the only way just a SELECT INTO
> followed by a DELETE?

That's pretty much it. What I did was to create the new month table
and day tables, alter my triggers to reflect this, then move the data
with insert into / select from query for each old day partition. Then
once their data is moved you can just drop them. Since you changed the
triggers first those tables are no long taking input so it's usually
safe to drop them now.