DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=CAzNfvatJtE0pE5FRJgEqoLHcZL1s5wqHpiHhEtC4Cmk6CnqFwSgkJpf2ddZysyaNJ3aD5gS8CaqZjm2UGHcLftgqeYhkjrBcY0iqDWHMHHaCk9qkvH2DOEnEY6p2xKXjB1KvuXeJb/OrS9mqlEMUFoXoBEjJ4fs5hO7R4OFSqg=
Message-ID: <b42b73150605260753x4969e11dua10c7dad562f3411@mail.gmail.com>
Date: Fri, 26 May 2006 10:53:38 -0400
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "Alban Hertroys" <alban@magproductions.nl>
Subject: Re: [SQL] (Ab)Using schemas and inheritance
Cc: "Jorge Godoy" <jgodoy@gmail.com>, "Jim C. Nasby" <jnasby@pervasive.com>,
	pgsql-general@postgresql.org
In-Reply-To: <4476C991.5060707@magproductions.nl>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <200605231716.58784.jgodoy@gmail.com>
	<200605241221.55060.jgodoy@gmail.com>
	<44748493.5090107@magproductions.nl>
	<200605241410.46202.jgodoy@gmail.com>
	<4476C991.5060707@magproductions.nl>

to all:

maybe I can be of some help here.  I think schemas are definately the
way to go.  I've used them quite a bit, previously with an accounting
application which used schemas to separate company.  Right now I am in
development of a major project which will use them even more
extensively, following an evalution of other methods of isloating data
for scalability reasons.

The mammoth table approach (company id in every table) is very clean
and the best approach from a academic perspective but unfortunately
you can run into scalability problems if your tables are very large.
An ideal SQL database would automagically do everything for you
without having to worry about it (no such database exists AFAIK).

The major downsides of using schemas in an application can be
mitigated...for example clever use of dynamic sql functions and
scripts can go a long way in helping with updates that have to run
across mutiple schemas.

The benifits of schemas are pretty nice, you get the performance
advantages of partitioning and you can manipulate the search path for
easy switching...just make sure to force a reconnect to get all those
stored procedures to relearn all the tables they use if you keep your
functions in a shared area (usually a good idea).

Table partitioning and inheritance look good on the surface but there
are a number of 'gotchas' that can hold you back from using them
effectively.  Foreign keys are a mess, and compelex queries can be
problematic...the new constraint exclusion feature helps, but there is
a way to go IMO.

by the way, the \d query in psql maps to a fairly complex sql
statement, you can beat it by going right to the pg catalogs if
performance is an issue, or materialize your catalogs to an indexed
table.  If you have a lot of tables (1000s) you will definately want
to prepare everything, meaning use views, procedures, and prepared
statements liberally.  I would also take careful note of Jim's FSM
comments and keep a careful eye on that.

Merlin