X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4C182565AF for ; Wed, 23 Mar 2005 01:25:14 +0000 (GMT) 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 53280-09 for ; Wed, 23 Mar 2005 01:25:04 +0000 (GMT) Received: from candle.pha.pa.us (candle.pha.pa.us [64.139.89.126]) by svr1.postgresql.org (Postfix) with ESMTP id 27E72565A6 for ; Wed, 23 Mar 2005 01:25:03 +0000 (GMT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id j2N1P5C18372; Tue, 22 Mar 2005 20:25:05 -0500 (EST) From: Bruce Momjian Message-Id: <200503230125.j2N1P5C18372@candle.pha.pa.us> Subject: Re: What needs to be done for real Partitioning? In-Reply-To: <200503220901.23481.josh@agliodbs.com> To: Josh Berkus Date: Tue, 22 Mar 2005 20:25:05 -0500 (EST) Cc: Hannu Krosing , pgsql-performance@postgresql.org X-Mailer: ELM [version 2.4ME+ PL121 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.012 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/482 X-Sequence-Number: 11187 Added to TODO: * Support table partitioning that allows a single table to be stored in subtables that are partitioned based on the primary key or a WHERE clause --------------------------------------------------------------------------- Josh Berkus wrote: > Hannu, > > > If you don't get it, contact me as there is a small possibility that I > > know a company interested enough to fund (some) of it :) > > Enough people have been interested in this that if we get our acts together, > we may do it as multi-funded. Easier on our budget ... > > > As these are already discussed in this thread, I'll try to outline a > > method of providing a global index (unique or not) in a way that will > > still make it possible to quickly remove (and not-quite-so-quickly add) > > a partition. > > > To repeat - the global index over partitioned table should have te same > > structure as our current b-tree index, only with added map of 128k index > > partitions to 1G subfiles of (possibly different) tables. This map will > > be quite small - for 1Tb of data it will be only 1k entries - this will > > fit in cache on all modern processors and thus should add only tiny > > slowdown from current direct tid.page/128k method > > I think this is a cool idea. It would need to be linked to clustering, so > that each partition can be an iteration of the clustered index instead of a > specifc # of bytes. But it would give us the "fully automated partitioning" > which is one fork of the two we want. > > Plus I'm keen on any idea that presents an alternative to aping Oracle. > > How difficult would your proposal be to code? > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073