Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id CFBCD476F33
	for <pgsql-performance@postgresql.org>;
	Thu, 12 Dec 2002 17:55:52 -0500 (EST)
Received: from phaedrusdeinus.org (dsl092-130-239.chi1.dsl.speakeasy.net
	[66.92.130.239]) by postgresql.org (Postfix) with SMTP id 9F3DB476F43
	for <pgsql-performance@postgresql.org>;
	Thu, 12 Dec 2002 17:55:31 -0500 (EST)
Received: (qmail 8571 invoked by uid 1001); 12 Dec 2002 23:00:02 -0000
Date: Thu, 12 Dec 2002 17:00:02 -0600
From: johnnnnnn <john@phaedrusdeinus.org>
To: pgsql-performance@postgresql.org, pgsql-general@postgresql.org
Subject: Re: [GENERAL] CLUSTER command
Message-ID: <20021212230002.GC8278@performics.com>
References: <3DF90042.E5290B7F@nsd.ca>
	<20021212135913.Q11714-100000@megazone23.bigpanda.com>
	<20021212222641.GA8278@performics.com> <3DF91030.A372DF7C@nsd.ca>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3DF91030.A372DF7C@nsd.ca>
User-Agent: Mutt/1.5.1i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/138
X-Sequence-Number: 554

On Thu, Dec 12, 2002 at 05:39:44PM -0500, Jean-Luc Lachance wrote:
> Let's create a new command:
> 
> PARTITION <table> ON <attribute>
<snip>
> Because the table file is already extended (2G limit) using
> different files extension (.N)
> how complicated (modifying the code) would it be to have the table
> files split according to the cluster key?

I think the code changes would be complicated. Just at a 30-second
consideration, this would need to touch:
- all sql (selects, inserts, updates, deletes)
- vacuuming
- indexing
- statistics gathering
- existing clustering

That's not to say it's not worthwhile to look into, but it's big.

All of that aside, a view over unions is possible now:

create table u1 (...);
create table u2 (...);
create table u3 (...);

create view uv as (select "A" as partition_key, ... from u1
                   union all
                   select "B" as partition_key, ... from u2
                   union all
                   select "C" as partition_key, ... from u3);

That keeps the tables in different files on-disk while still allowing
you to query against all of them. You need to index them separately
and logic is necessary when changing data.

Hope that helps.

-johnnnnnnnnnn