From: Scott Marlowe <scott.marlowe@gmail.com>
To: s.caillet@free.fr
Cc: pgsql-performance@postgresql.org
Subject: Re: Partitions number limitation ?
Date: Wed, 3 Sep 2008 10:43:46 -0600
Message-ID: <dcc563d10809030943o5f204511o15ba2c11d3df3c1d@mail.gmail.com> (raw)
In-Reply-To: <1220436022.48be603606137@imp.free.fr>
References: <1220436022.48be603606137@imp.free.fr>
On Wed, Sep 3, 2008 at 4:00 AM, <s.caillet@free.fr> wrote:
> Hi !
>
> To improve our software insert datas performance, we have decided to use
> partition architecture in our database. It works well for the following volume :
> 45 main tables and 288 inherited daughter tables for each, that is a total of
> 12960 partitions.
>
> I have some trouble now with another customer's database with the following
> volume : 87 main tables and 288 tables for each, that is 25056 partitions.
>
> Is there some kind of limit in postgresql about the number of partitions ? Do
> you know some tuning in the conf files to improve postgresql management of so
> many tables ? I have already used different tablespaces, one for each main table
> and its 288 partitions.
What do you mean PostgreSQL management of the partitions. Triggers,
rules, application based partitioning? Rules aren't fast enough with
this many partitions and if you can easily add partitioning in your
application and write directly to the right child table it might be
much faster.
The size of your disk array depends very much on how quickly you'll be
saturating your CPUS, either in the app layer or CPU layer to maintain
your partitioning. If an app needs to insert 100,000 rows into ONE
partition, and it knows which one, it's likely to be way faster to
have the app do it instead of pgsql. The app has to think once, the
database 100,000 times.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, s.caillet@free.fr
Subject: Re: Partitions number limitation ?
In-Reply-To: <dcc563d10809030943o5f204511o15ba2c11d3df3c1d@mail.gmail.com>
References: <1220436022.48be603606137@imp.free.fr> <dcc563d10809030943o5f204511o15ba2c11d3df3c1d@mail.gmail.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