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