Skip site navigation (1) Skip section navigation (2)

Re: Question on moving data to new partitions

From: Radhika Sambamurti <rs1(at)speakeasy(dot)net>
To: Postgres-Admin <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Question on moving data to new partitions
Date: 2010-01-14 02:30:49
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin

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.
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.


In response to


pgsql-admin by date

Next:From: Scott MarloweDate: 2010-01-14 03:53:58
Subject: Re: Question on moving data to new partitions
Previous:From: Scott MarloweDate: 2010-01-14 01:22:21
Subject: Re: Question on moving data to new partitions

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group