Re: Question on moving data to new partitions

On Wed, Jan 13, 2010 at 5:51 PM, Benjamin Krajmalnik <kraj(at)illumen(dot)com> wrote:
> I have some tables which have an extremely high amount of update activity on
> them.  I have changed autovacuum parameters (cost delay and limit), and
> whereas before they would never be vacuumed and bloat they are running fine.
>
> However, as the platform scales, I am afraid I will reach the same
> situation.
>
> As a result, I have decided to partition the table and add to each record a
> partition id, which can be used to route it to the correct partition.
>
> Presently, all of the records reside on what will ultimately become the
> parent partition.

Are you using table inheritance to do this? or are they all independent tables?

> What would be the best way of moving the data to the pertinent partitions?
>
> I was thinking of copying the data to another table and then performing a
> insert into partitionedtableparent select * from temporary table, and then
> performing a delete from only partitionedtableparent.
>
> Does this sound like a reasonable way of doing this?  Is there a more
> efficient way of doing this?

You can probably skip a few steps there if you copy straight to the
destination table.

At work, where we have partitioned out some tables, I made a trigger
based inherited table setup, and basically did something like:

insert into master_table select * from master_table where id between 1
and 100000;
delete from only master_table where id between 1 and 100000;

Then incremented the between values until all the tuples had been moved, then I

truncate only master_table;

and it worked like a charm.