Re: Partitioning an existing table

disclaimer : I didn't read the presentation paper Greg Smith talked
about yet, nor his partitioning chapter yet, so it might be about the
same trick.

Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> writes:
> How about doing this with existing massive tables? (Over 120 million rows)
>
> 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?

If you're partitioning by date, for example, then what I regularly do is
to consider the existing table to be the first partition with data from
origin to now.

Then what I do is to create a new parent table and is children, prepare
the trigger(s), etc. The switch is then a light transaction which only
renames the current table to say "name_past_201104", have it inherits
the parent table, and finally rename the new parent table to the "name".

Later on you still can rejigger your data around if you wish. With time
based partitioning it's best to wait until the old partition is not the
target of INSERTs or UPDATEs any more.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support