From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Albe Laurenz" <all(at)adv(dot)magwien(dot)gv(dot)at> |
Cc: | "olivier *EXTERN*" <om(at)oliviermigeon(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: hundreds of schema vs hundreds of databases |
Date: | 2007-05-29 13:39:04 |
Message-ID: | b42b73150705290639u891791fge166c9d9f072146a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2007-05-29 13:50:04 | Re: application online upgrade strategy |
Previous Message | Lew | 2007-05-29 13:38:09 | Re: optimisation for a table with frequently used query |