Re: PostgreSQL server architecture

On 21/12/2011 4:08 AM, Bill Moran wrote:
> In response to "James B. Byrne"<byrnejb(at)harte-lyne(dot)ca>:
>> We run a small in-house data centre for our various
>> operations. Currently, we are moving applications from
>> dedicated boxes to kvm based CentOS-6.1 virtual machines
>> on a single CentOS-6.1 host. At issue is the question on
>> how to handle the PostgreSQL instances that we currently
>> have running on different machines.
>>
>> As we see it presently we have the option of having one VM
>> host a centralized PostgreSQL server with multiple
>> databases or continue with each application specific VM
>> running its own copy of PostgreSQL with just the dedicated
>> application database.
>>
>> Since whatever we chose we are likely to be using five
>> years from now I am soliciting informed option over which
>> option is considered a better choice for the long term.
> In my experience, you'll be better off using a single DB for all the
> databases. With proper roles, database permissions, and pg_hba.conf,
> you'll have acceptable security. The advantage to doing this is
> better utilization of hardware, since you don't have all the overhead
> of multiple VMs using up memory, CPU, and IO load. You also have
> less instances to monitor.
While I tend to agree with this, there are some important downsides too.
Perhaps the most important is that you can't currently use streaming or
WAL-shipping replication to replicate only *one* database out of a
cluster. You have to replicate all databases in the cluster. If you have
some DBs that are small or low traffic but very important, and other DBs
that're big or high traffic but less important, this can be a problem.

As you noted, it's also harder to isolate performance between DBs and
protect more important DBs from response time drops caused by less
important but heavily loaded DBs, big reporting queries on other DBs, etc.

--
Craig Ringer