Re: Partitioning an existing table

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 
is still one of the best case studies of how to do this sort of 
migration around.  I was inspired by several of the ideas there when 
doing the partitioning chapter of my book, which is the only place I'm 
aware of covering this in even more detail than his case study.

Cedric's idea for how to do this even more aggressively (multiple 
workers) is what you want if this is a one-time operation you're taking 
the system down for.  In other situations, the gradual migration 
strategy Vick is suggesting is more appropriate.

Some of the other ideas suggested in this thread won't work at all, so 
be careful who you listen to here.  You can't leave copies of the data 
in the parent and put it into the child partition without all sorts of 
potential downsides.  And you really, really want to do this as a proper 
database transaction, which is easiest to express using INSERT instead 
of COPY.  If any step of the migration goes wrong, being able to do 
ROLLBACK and undo the recent bad steps is vital.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books