Re: hundreds of schema vs hundreds of databases

["Merlin Moncure" <mmoncure(at)gmail(dot)com>]

On 5/29/07, Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at> wrote:
> > I have an application with some hundreds users, each one
> > having the same
> > data definitions, and each one storing up to 2 GB of data.
> > A user have just access to his own data. His data will have its own
> > tablespace.
> >
> > Therefore, it seems to me I have a choice between "one database per
> > user" and "one schema per user in the same database".
> >
> > What is the best practice here ? Which solution will be the
> > easiest to manage ?
>
> Advantages of many databases:
> - Each database is smaller.
> - No danger of one user accessing another user's data (because of
>   misconfigured permissions and similar).
> - Guaranteed independence of each user's data.
> - More scalable: If you decide that one machine or one cluster
>   is not enough to handle the load, you can easily transfer some
>   of the databases somewhere else.
>
> Advantages of one database with many schemata:
> - Fewer databases to administrate.
>
> I'd probably go for many databases.

you missed one possible advantage of schemas...database structures can
be more easily shared.   For example, you can join one of the user's
private tables with a shared central table.  With multiple databases,
you have to resort to other strategies to do that, for example dblink.

Schemas are designed to the effect of giving a private data area in a
large shared database.  Separate databases would be preferred if the
databases are backing difrferent applications and completely
unrelated.

merlin