Re: Schema boggle...

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
>