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

Re: Question on moving data to new partitions

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Benjamin Krajmalnik <kraj(at)illumen(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Question on moving data to new partitions
Date: 2010-01-14 01:22:21
Message-ID: dcc563d11001131722k2e7bb513hbd58b5db83900b4c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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.

In response to

Responses

pgsql-admin by date

Next:From: Radhika SambamurtiDate: 2010-01-14 02:30:49
Subject: Re: Question on moving data to new partitions
Previous:From: Benjamin KrajmalnikDate: 2010-01-14 01:11:54
Subject: Re: Question on moving data to new partitions

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