Message-ID: <45BEEF56.60604@fuzzy.cz>
Date: Tue, 30 Jan 2007 08:10:14 +0100
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Thunderbird 1.5.0.8 (X11/20061119)
MIME-Version: 1.0
To: Gauri Kanekar <meetgaurikanekar@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Partitioning
References: <7e4ba9550701120340s4d8ea6b6jebf24eb67aeff7c6@mail.gmail.com>
In-Reply-To: <7e4ba9550701120340s4d8ea6b6jebf24eb67aeff7c6@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

> Can anybody help me out
>  
> I just wanted to knw what will be the configuraion settings for
> partitioning table so as to make inserts faster on the partitioned tables.

Well, that depends. Many questions are important here. Will you insert
into several partitions or only to a single one? Do you need to enforce
some constraint between several partitioned tables?

If you need to insert into several partitions, it can be faster as you
can place them on different drives. If you need to insert only into the
last one (that's usually the case with 'logging' tables) then this
probably won't give a huge performance benefit.

If you need to enforce some kind of constraint between multiple
partitions (possibly from several tables), you'll have to do that
manually using a plpgsql procedure (for example). This is the case with
UNIQUE constraint on a single table, FOREIGN KEY between multimple
partitioned tables, etc. This can mean a serious performance penalty,
esecially when you do mostly insert/update on that table.

This is mostly about application architecture - if you use partitions
incorrectly it's almost impossible to fix that by changing settings in
postgresql.conf.

Tomas