Re: Question on moving data to new partitions

On Wed, Jan 13, 2010 at 6:11 PM, Benjamin Krajmalnik <kraj(at)illumen(dot)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 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.

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.