Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 39A8B133655C for ; Mon, 4 Oct 2010 15:44:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 37888-06 for ; Mon, 4 Oct 2010 18:44:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from spinlock.commandprompt.com (host-215.commandprompt.net [207.173.203.215]) by mail.postgresql.org (Postfix) with ESMTP id C5DAE133616B for ; Mon, 4 Oct 2010 15:44:46 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by spinlock.commandprompt.com (Postfix) with ESMTP id 1255A348015; Mon, 4 Oct 2010 11:44:38 -0700 (PDT) X-Virus-Scanned: Debian amavisd-new at commandprompt.com Received: from spinlock.commandprompt.com ([127.0.0.1]) by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 54nz88eZTQxT; Mon, 4 Oct 2010 11:44:35 -0700 (PDT) Received: from [192.168.1.105] (unknown [75.142.155.118]) by spinlock.commandprompt.com (Postfix) with ESMTPSA id 44E9F348012; Mon, 4 Oct 2010 11:44:35 -0700 (PDT) Subject: Re: Issue for partitioning with extra check constriants From: "Joshua D. Drake" Reply-To: jd@commandprompt.com To: Josh Berkus Cc: Tom Lane , pgsql-performance@postgresql.org In-Reply-To: <4CAA1E1B.8080505@agliodbs.com> References: <4CA672D7.3070405@agliodbs.com> <1548.1285977377@sss.pgh.pa.us> <4CA67580.7070003@agliodbs.com> <6813.1285978581@sss.pgh.pa.us> <4CAA1E1B.8080505@agliodbs.com> Content-Type: text/plain; charset="UTF-8" Organization: Command Prompt, Inc. Date: Mon, 04 Oct 2010 11:44:26 -0700 Message-ID: <1286217866.28987.14.camel@jd-desktop.unknown.charter.com> Mime-Version: 1.0 X-Mailer: Evolution 2.28.3 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201010/26 X-Sequence-Number: 40445 On Mon, 2010-10-04 at 11:34 -0700, Josh Berkus wrote: > > And your point is? The design center for the current setup is maybe 5 > > or 10 partitions. We didn't intend it to be used for more partitions > > than you might have spindles to spread the data across. > > Where did that come from? Yeah that is a bit odd. I don't recall any discussion in regards to such a weird limitation. > It certainly wasn't anywhere when the feature > was introduced. Simon intended for this version of partitioning to > scale to 100-200 partitions (and it does, provided that you dump all > other table constraints), and partitioning has nothing to do with > spindles. I think you're getting it mixed up with tablespaces. Great! that would be an excellent addition. > > The main reason for partitioning is ease of maintenance (VACUUM, > dropping partitions, etc.) not any kind of I/O optimization. Well that is certainly "a" main reason but it is not "the" main reason. We have lots of customers using it to manage very large amounts of data using the constraint exclusion features (and gaining from the smaller index sizes). Jd -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt