On 5/29/07, Albe Laurenz <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
