Re: Partitioning an existing table

On Mon, Apr 25, 2011 at 6:46 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

I could create a new parent table with child tables, and then INSERT
all these millions of rows to put them into the right partition. But
is that recommended?

I did this twice (several years ago).  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.

If you can, of course, try this out on a spare copy of that table.