Date: Wed, 5 Nov 2003 21:45:39 -0400 (AST)
From: "Marc G. Fournier" <scrappy@postgresql.org>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-hackers@postgresql.org, chris@pgsql.com
Subject: Re: Schema boggle...
In-Reply-To: <200311051358.34826.josh@agliodbs.com>
Message-ID: <20031105214322.Q11434@ganymede.hub.org>
References: <E1AHVFF-0002Up-75@noon.pghoster.com>
	<200311051358.34826.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


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 ...

On Wed, 5 Nov 2003, Josh Berkus wrote:

> Chris,
>
> >  I work with Marc Fournier, for those who don't know, and have been
> > working extensively with the schemas feature for Hub.Org's new Account
> > Management package. Each client's data is stored in a seperate schema
> > set asside just for them (though they will never have direct access to
> > it, it helps us "balance" out the data. Each schema has the same set of
> > tables, with which different data is stored, depending on the client.
>
> While this is an interesting use of Schema, it is not, IMHO, a good way to
> partition client data.    Far better to have monolithic tables with a
> "client_id" column and then construct *views* which are available in each
> schema only to that client.   Otherwise, as you've discovered, top-level
> management becomes a royal pain.
>
> I'd be happy to discuss this further on PGSQL-SQL, which is really the
> appropriate mailing list.
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>