Re: How can I have 2 completely seperated databases in

On Wed, 11 Feb 2004 hong(dot)ge(at)yale(dot)edu wrote:

> Thank you very much for your reply.
>
> Yes, that's true. But it seems not a good idea if I have many databases
> and I want them totally seperated with each other.
>
> What's your opinion? Thanks.

OK, here's the issue. Postgresql uses certain resources in a shared
manner, and other resources are completely seperate. For instance, the
shared memory buffers are shared within a single instance or cluster, by
all the databases. One data directory, and one collation, and one set of
logs are also shared by one instance.

The individual databases within a cluster share a set of global users.
I.e if I create a user in one database, he can then be granted access to
the other databases (or denied access) with a simple change to
pg_hba.conf. So, it's very easy to add / remove people's access to
individual databases.

If you seperate out each database into it's own instance, you now have two
(or more) postgresql instances, each with a different data directory,
shared memory buffers, user list and passwords. I.e. now nothing passes
between them, at all.

If you would have allocated 128 megs of shared buffer memory for a single
cluster which contained 4 databases, and you split those out into
individual instances, then you'd need to give each of the four cluster /
instances 32 megs of shared buffer or you'd risk using up more memory than
before.

With the single cluster, if one database has a lot of data to sling
around, and the others are handling a few k at a time, it has 128 Megs to
work in. With four clusters, no matter how little the other three are
working, you'd only have 32 meg to play in.

Taking this to the logical extreme of having n databases, where n is
fairly good size, say 20, 40, or 100, then you have issues that if you set
up each database with enough shared memory to do its job when it needed
to, you risk starting a swap storm should a couple dozen of those
databases have a few large result sets open, thus using up all the shared
memory they'd have alloted.

Lowering the shared memory for each database low enough to prevent this
would result in individual databases that each had very small amounts of
shared memory.

Also, maintainance gets harder. You now have to vacuum multiple seperate
clusters, and you need to schedule it so that you don't have two or three
running at once and swamping your storage subsystem.

For certain setups, multiple clusters are a great thing. I've used them
as "hot backups" where I put a secondary instance online, placed its
storage on a NAS, backed up to it, and then shut it down to have a cold
live spare, or used it for testing administrative procedures one shouldn't
practice on a live database.

But for seperating out applications from each other, there's really
nothing to be gained by putting each seperate database application into
it's own cluster.