Re: Partitions number limitation ?

s(dot)caillet(at)free(dot)fr wrote:
> 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.

Postgres is not really designed for performance of partitions, so you
have to manage that yourself. I am working on a project with a similar
design and found that the super table has its limitations. At some point
the db just aborts a query if there are to many partitions. I seem to
remeber I have worked with up to 100K partitions, but managed them
individually instead of through the super table.

Just a tip: if the table gets data inserted once and then mainly read
after that, its faster to create the index for the partition after the
insert.
Another tip: use COPY to insert data instead of INSERT, its about 3-5
times faster, it is supported by the C driver and a patched JDBC driver

regards

tom