Skip site navigation (1) Skip section navigation (2)

Re: Partitioning an existing table

From: Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>
To: Greg Smith <greg(at)2ndquadrant(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Partitioning an existing table
Date: 2011-04-28 04:12:34
Message-ID: BANLkTinYZROgu3ZM-t0KTpEgbuJkhFd+6w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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!

In response to

Responses

pgsql-general by date

Next:From: Pavel StehuleDate: 2011-04-28 04:34:20
Subject: Re: Best way to construct PostgreSQL ArrayType (_int4) from C int array
Previous:From: David CifuentesDate: 2011-04-28 00:37:15
Subject: Re: [ANNOUNCE] [HACKERS] PostgreSQL Core Team

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group