Re: Partitioning an existing table

On Tue, Apr 26, 2011 at 8:28 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> On 04/25/2011 10:10 AM, Vick Khera wrote:
>>
>> Basically, you create your partitions and set up the necessary triggers
>> you want (I re-write the app to insert directly into the correct partition).
>>  Then all new data starts going into the partitions.  Next, write a program
>> that loops over the current master table, and moves the data into each
>> partition some small hunk at a time, in a transaction.  This can take a long
>> time.  For us, it took about 7 days to move O(100m) rows.  Then, when you're
>> done, truncate the master table, and enforce that no new data is allowed to
>> be inserted into it.
>
> Vick's presentation at
> http://cdn.mailermailer.com/documents/PGCon2008TablePartitioningCaseStudy.pdf

Thanks everyone for the excellent suggestions.

Vick/Greg, thanks in particular for this reference. The doc gives me
ideas for other things too!