X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4983FD6E69 for ; Tue, 13 Sep 2005 14:21:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 14206-09 for ; Tue, 13 Sep 2005 17:21:27 +0000 (GMT) Received: from mx1.surnet.cl (mx2.surnet.cl [216.155.73.181]) by svr1.postgresql.org (Postfix) with ESMTP id 1D025D8D5E for ; Tue, 13 Sep 2005 14:21:26 -0300 (ADT) Received: from unknown (HELO cluster.surnet.cl) ([216.155.73.165]) by mx1.surnet.cl with ESMTP; 13 Sep 2005 13:21:12 -0400 X-IronPort-AV: i="3.97,106,1125892800"; d="scan'208"; a="9169193:sNHT29812920" Received: from alvh.no-ip.org (216.155.86.232) by cluster.surnet.cl (7.0.043) (authenticated as alvherre@surnet.cl) id 4322D7BF002CE23C; Tue, 13 Sep 2005 13:21:26 -0400 Received: by alvh.no-ip.org (Postfix, from userid 1000) id 6DBBDC2F862; Tue, 13 Sep 2005 13:22:43 -0400 (CLT) Date: Tue, 13 Sep 2005 13:22:43 -0400 From: Alvaro Herrera To: Ian Westmacott Cc: blblack@gmail.com, pgsql-performance@postgresql.org Subject: Re: Performance considerations for very heavy INSERT Message-ID: <20050913172243.GA18107@surnet.cl> Mail-Followup-To: Ian Westmacott , blblack@gmail.com, pgsql-performance@postgresql.org References: <84621a6050912140464c380c3@mail.gmail.com> <59d991c40509122039773cac17@mail.gmail.com> <84621a6050913083079ef0559@mail.gmail.com> <1126628214.11675.55.camel@spectre.intellivid.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <1126628214.11675.55.camel@spectre.intellivid.com> User-Agent: Mutt/1.5.10i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.669 required=5 tests=[AWL=-0.728, DNS_FROM_RFC_ABUSE=0.374, DNS_FROM_RFC_POST=1.376, FORGED_RCVD_HELO=0.05, RCVD_IN_SORBS_MISC=1.597] X-Spam-Level: ** X-Archive-Number: 200509/171 X-Sequence-Number: 14479 On Tue, Sep 13, 2005 at 12:16:55PM -0400, Ian Westmacott wrote: > On Tue, 2005-09-13 at 11:30, Brandon Black wrote: > > I started looking closer at my options for partitioning (inheritance, > > union all), and at Bizgres today. Bizgres partitioning appears to be > > basically the same kind of inheritance partitioning one can do in > > mainline PostgreSQL. Am I correct in thinking that the main > > difference is that they've coded support for > > "enable_constraint_exclusion=true" so that the query planner can be > > more effective at taking advantage of the partitioning when you've > > specified CHECK constraints on the child tables? I may go for 8.1 > > instead in that case, as the main win I'm looking for is that with > > inheritance I'll be doing inserts into smaller tables instead of ones > > that grow to unmanageable sizes (and that I can drop old child tables > > instead of delete/vacuum). > > Perhaps I missed something in this thread, but don't forget > you still need vacuum to reclaim XIDs. Yes, but if you are going to drop the partition before 1 billion transactions, you can skip vacuuming it completely. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "Es filósofo el que disfruta con los enigmas" (G. Coli)