Re: Question on moving data to new partitions

Yes, I will be using table inheritance and inheriting the current table where the data resides.
I was wondering if it would be "kosher" to perform the insert on itself, but I guess since the rules engine takes over there should not be a problem.
The tables are not huge per se (a little over 50K records). The problem is that each record gets updated at least 500 times per day, so the row versions are quite extensive and need to be vacuumed often. Can't afford to take chances on the tables bloating because, from experience, it will slow down the system and create a snowball effect where data coming in gets backed up. By keeping the number of records in each partition small, I can ensure that autovacuum will always be able to run. As the need arises, I can always create additional partitions to accommodate for the growth.

As always, thank you very much Scott. You are always very helpful.

> -----Original Message-----
> From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
> Sent: Wednesday, January 13, 2010 5:58 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] 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.