s.caillet@xxxxxxx 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
