Re: Partitions number limitation ?

On Wed, Sep 3, 2008 at 4:00 AM, <s(dot)caillet(at)free(dot)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.