Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 7C17164FD0D for ; Thu, 4 Sep 2008 13:02:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 99375-07 for ; Thu, 4 Sep 2008 13:02:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-out1.uio.no (mail-out1.uio.no [129.240.10.57]) by postgresql.org (Postfix) with ESMTP id 3A9E264FCEF for ; Thu, 4 Sep 2008 13:02:06 -0300 (ADT) Received: from mail-mx6.uio.no ([129.240.10.47]) by mail-out1.uio.no with esmtp (Exim 4.69) (envelope-from ) id 1KbHHY-0001wx-QH; Thu, 04 Sep 2008 18:02:04 +0200 Received: from 165.84-49-232.nextgentel.com ([84.49.232.165] helo=[10.0.0.10]) by mail-mx6.uio.no with esmtpsa (TLSv1:AES256-SHA:256) user tfinneid (Exim 4.69) (envelope-from ) id 1KbHHY-0001Mu-EF; Thu, 04 Sep 2008 18:02:04 +0200 Message-ID: <48C00687.1010200@ifi.uio.no> Date: Thu, 04 Sep 2008 18:02:15 +0200 From: Thomas Finneid User-Agent: Thunderbird 2.0.0.16 (X11/20080724) MIME-Version: 1.0 To: s.caillet@free.fr CC: pgsql-performance@postgresql.org Subject: Re: Partitions number limitation ? References: <1220436022.48be603606137@imp.free.fr> In-Reply-To: <1220436022.48be603606137@imp.free.fr> Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit X-UiO-SPF-Received: X-UiO-Spam-info: not spam, SpamAssassin (score=-3.4, required=5.0, autolearn=disabled, SUBJECT_FUZZY_TION=1.1, TVD_RCVD_IP=0.502, UIO_MAIL_IS_INTERNAL=-5, uiobl=NO, uiouri=NO) X-UiO-Scanned: CDA5E58A599C17C48E91ADCFC4848227E742B549 X-UiO-SPAM-Test: remote_host: 84.49.232.165 spam_score: -33 maxlevel 200 minaction 2 bait 0 mail/h: 1 total 71 max/h 6 blacklist 0 greylist 0 ratelimit 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.41 tagged_above=0 required=5 tests=SUBJECT_FUZZY_TION=0.41 X-Spam-Level: X-Archive-Number: 200809/52 X-Sequence-Number: 31365 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