Re: Question on moving data to new partitions

On Wed, Jan 13, 2010 at 6:11 PM, Benjamin Krajmalnik <kraj@illumen.com> wrote:
> 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
overthere 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
500times per day, so the row versions are quite extensive and need to be vacuumed often.  Can't afford to take chances
onthe tables bloating because, from experience, it will slow down the system and create a snowball effect where data
comingin gets backed up.  By keeping the number of records in each partition small, I can ensure that autovacuum will
alwaysbe 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.

My one recommendation would be to look at using triggers over rules.
I have a simple cronjob written in php that creates new partitions and
triggers each night at midnight.  Triggers are MUCH faster than rules
for partitioning, but making them fancy is a giant pain in plpgsql.  I
just write a big trigger with an if/elseif/else tree that handles each
situation.  It runs very fast.