Re: Partitions number limitation ?

From: Thomas Finneid <tfinneid(at)student(dot)matnat(dot)uio(dot)no>
To: s(dot)caillet(at)free(dot)fr
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Partitions number limitation ?
Date: 2008-09-04 16:02:15
Message-ID: 48C00687.1010200@ifi.uio.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Guillaume Cottenceau 2008-09-04 16:08:11 Re: limit clause breaks query planner?
Previous Message Matthew Wakeling 2008-09-04 15:50:00 Re: limit clause breaks query planner?