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!
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
