Re: Partitioning an existing table

On Mon, Apr 25, 2011 at 6:46 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)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.