Re: hundreds of schema vs hundreds of databases

On 05/29/07 04:02, Albe Laurenz 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

You could always dump a schema then drop it and restore it in a new
database. At 2GB, that should be quick.

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

But since they all have to have the same schema, you'd still have
the same DDL overhead whether it's one DB or many.

Does PG set up buffers at the postmaster level or the database level?

If at the database level, then you'll be allocating memory to
databases that might not be in use at any one time, thus wasting it.
One database buffer pool would make more efficient use of RAM.

--
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!