Re: How can I have 2 completely seperated databases in - Mailing list pgsql-hackers

On Wed, 11 Feb 2004 hong.ge@yale.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.