Re: Question on moving data to new partitions

Scott Marlowe wrote:
> 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.
>
>
Hi,
I am currently looking into partitioning a table of which 90% of the
lookups are for the prior week. It has about 9 million rows and
selects are a bit slow, since the table is joined to two other
tables. I am planning on doing a range partition ie each year starting
from 2005 will be its own partition. So the check constraints will be
year based. I have run tests and what I see is that the optimizer can
find the correct table when I search by year, but when I search by say
recid (PK), it does a seq scan on every single child table.
To have the optimizer recognize the recid, do I need to include that in
the check constraint?

2. When you say you wrote a trigger, was it instead of the insert rule?

This is pretty new stuff to me and any insight into this would be helpful.

Thanks,
Radhika