From: tv@fuzzy.cz
To: Zoltan Boszormenyi <zb@cybertec.at>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Cc: Hans-Juergen Schoenig <hs@cybertec.at>
Subject: Re: Scalability question
Date: Wed, 11 Jun 2008 12:15:41 +0200 (CEST)
Message-ID: <13093.217.77.161.17.1213179341.squirrel@sq.gransy.com> (raw)
In-Reply-To: <484FA134.7030807@cybertec.at>
References: <484FA134.7030807@cybertec.at>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, zb@cybertec.at, hs@cybertec.at
Subject: Re: Scalability question
In-Reply-To: <13093.217.77.161.17.1213179341.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox