Message-ID: <13093.217.77.161.17.1213179341.squirrel@sq.gransy.com>
In-Reply-To: <484FA134.7030807@cybertec.at>
References: <484FA134.7030807@cybertec.at>
Date: Wed, 11 Jun 2008 12:15:41 +0200 (CEST)
Subject: Re: Scalability question
From: tv@fuzzy.cz
To: "Zoltan Boszormenyi" <zb@cybertec.at>
Cc: "PostgreSQL Performance" <pgsql-performance@postgresql.org>,
	"Hans-Juergen Schoenig" <hs@cybertec.at>
User-Agent: SquirrelMail/1.4.10a
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-2
Content-Transfer-Encoding: 8bit
Importance: Normal

> Hi,
>
> I got a question about scalability in high volume insert situation
> where the table has a primary key and several non-unique indexes
> on other columns of the table. How does PostgreSQL behave
> in terms of scalability? The high volume of inserts comes from
> multiple transactions.
>
> Best regards,
> Zoltán Böszörményi

Well, that's a difficult question as it depends on hardware and software,
but with a proper tunning the results may be very good. Just do the basic
PostgreSQL tuning and then tune it for the INSERT performance if needed.
It's difficult to give any other recommendations without a more detailed
knowledge of the problem, but consider these hints:

1) move the pg_xlog to a separate drive (so it's linear)
2) move the table with large amount of inserts to a separate tablespace
3) minimize the amount of indexes etc.

The basic rule is that each index adds some overhead to the insert, but it
depends on datatype, etc. Just prepare some data to import, and run the
insert with and without the indexes and compare the time.

Tomas