Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B25579FC5DF for ; Tue, 30 Oct 2007 15:17:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 15827-01-7 for ; Tue, 30 Oct 2007 15:17:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from linus.numail.org (mail.numail.org [216.130.152.7]) by postgresql.org (Postfix) with ESMTP id 80AD79FC5EA for ; Tue, 30 Oct 2007 15:09:42 -0300 (ADT) Received: (qmail 5516 invoked by uid 0); 30 Oct 2007 18:09:41 -0000 Received: from 72.15.64.64 by linus.numail.org (envelope-from , uid 101) with qmail-scanner-1.23 (fsecure: 4.50/2111/fprot(2004-10-21)/avp(2004-10-21). Clear:RC:1(72.15.64.64):. Processed in 0.085414 secs); 30 Oct 2007 18:09:41 -0000 Received: from unknown (HELO ?24.25.1.100?) (72.15.64.64) by mail.numail.org with AES256-SHA encrypted SMTP; 30 Oct 2007 18:09:41 -0000 Message-ID: <47277341.7070704@laotraesquina.com.ar> Date: Tue, 30 Oct 2007 14:09:05 -0400 From: Pablo Alcaraz User-Agent: Thunderbird 2.0.0.6 (Windows/20070728) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: tables with 300+ partitions Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.315 tagged_above=0 required=5 tests=AWL=0.158, BAYES_50=0.001, SUBJECT_FUZZY_TION=0.156 X-Spam-Level: X-Archive-Number: 200710/364 X-Sequence-Number: 27386 Hi All! I had a big big big table. I tried to divide it in 300 partitions with 30M rows each one. The problem was when I used the table to insert information: the perfomance was LOW. I did some testing. I created a 300 partitioned empty table. Then, I inserted some rows on it and the perfomance was SLOW too. SLOW = 1% perfomance compared with a non partitioned table. That is too much. Then, I did a 10 partitioned table version with 30M rows each one and I inserted rows there. The performance was the same that the no partitioned table version. I suspect there is a lock problem there. I think every SQL command do a lock to ALL the partitions so the perfomance with concurrent inserts and updates are far worst than the no partitioned version. The perfomace degrade with the number of partitions. And it degrade fast: I have troubles with 40 partitions. Am I right? is there a workaround? Can I replace the partitioned version with another schema? any suggestion? I prefer to use something transparent for the program because it uses EJB3 = deep changes and testing on any change to the database layer. Regards Pablo Alcaraz