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 E4BD85E46E1 for ; Thu, 26 Aug 2004 15:32:59 -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 68409-02 for ; Thu, 26 Aug 2004 18:32:56 +0000 (GMT) Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) by svr1.postgresql.org (Postfix) with ESMTP id DC20E5E46EE for ; Thu, 26 Aug 2004 15:32:54 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6183409; Thu, 26 Aug 2004 11:34:11 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Bruce Momjian , "J. Andrew Rogers" Subject: Re: Equivalent praxis to CLUSTERED INDEX? Date: Thu, 26 Aug 2004 11:32:30 -0700 User-Agent: KMail/1.4.3 Cc: pgsql-performance@postgresql.org, mischa.sandberg@telus.net References: <200408261818.i7QIIrr09640@candle.pha.pa.us> In-Reply-To: <200408261818.i7QIIrr09640@candle.pha.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200408261132.30732.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/369 X-Sequence-Number: 8005 Bruce, > How do vendors actually implement auto-clustering? I assume they move > rows around during quiet periods or have lots of empty space in each > value bucket. That's how SQL Server does it. In old versions (6.5) you had to manually send commands to update the cluster, same as PG. Also, when you create a cluster (or an index or table for that matter) you can manually set an amount of "space" to be held open on each data page for updates. Also keep in mind that SQL Server, as a "single-user database" has a much easier time with this. They don't have to hold several versions of an index in memory and collapse it into a single version at commit time. All that being said, we could do a better job of "auto-balancing" clustered tables. I believe that someone was working on this in Hackers through what they called "B-Tree Tables". What happened to that? -- Josh Berkus Aglio Database Solutions San Francisco