From: Thomas Finneid <tfinneid@student.matnat.uio.no>
To: s.caillet@free.fr
Cc: pgsql-performance@postgresql.org
Subject: Re: Partitions number limitation ?
Date: Thu, 04 Sep 2008 18:02:15 +0200
Message-ID: <48C00687.1010200@ifi.uio.no> (raw)
In-Reply-To: <1220436022.48be603606137@imp.free.fr>
References: <1220436022.48be603606137@imp.free.fr>
s.caillet@free.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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tfinneid@student.matnat.uio.no, s.caillet@free.fr
Subject: Re: Partitions number limitation ?
In-Reply-To: <48C00687.1010200@ifi.uio.no>
* 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