X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 82C90D1BB5D
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Thu,  6 Nov 2003 05:34:37 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 09495-01
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Thu,  6 Nov 2003 01:34:06 -0400 (AST)
Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194])
	by svr1.postgresql.org (Postfix) with ESMTP id 864E2D1BB54
	for <pgsql-hackers@postgresql.org>;
	Thu,  6 Nov 2003 01:34:05 -0400 (AST)
Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162])
	by smtp.istop.com (Postfix) with ESMTP
	id E287536A09; Thu,  6 Nov 2003 00:34:04 -0500 (EST)
Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar)
	by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian))
	id 1AHcmS-0005xW-00; Thu, 06 Nov 2003 00:34:04 -0500
To: pgsql-hackers@postgresql.org
Subject: Re: Schema boggle...
References: <E1AHVFF-0002Up-75@noon.pghoster.com>
	<200311051358.34826.josh@agliodbs.com>
	<20031105214322.Q11434@ganymede.hub.org>
In-Reply-To: <20031105214322.Q11434@ganymede.hub.org>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 06 Nov 2003 00:34:04 -0500
Message-ID: <87ad7a12nn.fsf@stark.dyndns.tv>
Lines: 37
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/266
X-Sequence-Number: 46555


"Marc G. Fournier" <scrappy@postgresql.org> writes:

> Actually, the use of schema's was my idea, to speed up some dreadfully
> slow queries dealing with traffic stats from a table that was growing
> painfully monolithic ... the idea is/was that it would be easier to
> backup/remove all data pertaining to a specific client if they decided to
> close their account ...

I would add an <aol> me too </aol> to the comment that this is a horrible idea
and will be an enormous pain. You're denormalizing your data in a way that
will cause you great pain. 

What you're trying to implement is called "partitioned tables". And you're
right, bulk loading and deleting is one of the big advantages of partitioned
tables. But Postgres doesn't support partitioned tables, so trying to roll
your own is hard.

Using schemas seems like a bad impedance match here too, as you've found out
with the search_path. Inherited tables is a closer match, still a lot of
things won't be automatic, and primary keys may be a problem.

But what most people use as the closest approximation to partitioned tables in
Postgres is partial indexes. It lets you keep your indexes to a reasonable
size but still accelerate the poor-selectivity client_id column in parallel.

But you still would be storing all the records in a single table and would
have to do some big vacuuming whenever you delete a whole client. vacuum full
may be your friend.

I don't understand why the queries should be any slower dealing with the
normalized data in a single table versus the dernormalized tables in multiple
tables. The one big exception is any query doing "where client_id = ?" where
the latter allows the use of a sequential scan instead of an index scan.

-- 
greg