Re: multi-tenant vs. multi-cluster

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Ben Chobot
> Sent: Friday, March 18, 2011 3:10 PM
> To: Ivan Voras
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] multi-tenant vs. multi-cluster
>
> On Mar 18, 2011, at 11:47 AM, Ivan Voras wrote:
>
> > On 18/03/2011 19:17, Ben Chobot wrote:
> >
> >> if we're talking an extra 50MB of memory per cluster, that will
> start to add up.
> >
> > Consider this: each such cluster will have:
> >
> > a) its own database files on the drives (WAL, data - increasing IO)
>
> Oh, I hadn't thought about WAL. Good point.
> But data files are a function of tables and indexes, right? Having them
> in different schemas or different clusters isn't going to change that.
> I guess there are system tables but those are relatively trivial - I
> think?

Correct, but with different clusters you are going to have different back ends handling writes without regard to each other. How this unfolds will depend on your underlying disk structure and filsystems. I've had bad experiences in the past having multiple Postgres instances fighting for the same disk.

> > b) its own postgresql processes (many of them) running in memory
>
> I believe this is entirely a function of client connections.

With a single instance, you can use connection pooling to reduce the overall number of backend connections which will reduce your memory footprint.
>
> > c) its own shared_buffers in memory.
>
> Given that each application will be independent, I don't see a
> different between clusters and schemas here either.

The difference is that in a single cluster, a single instance is going to make decisions about what data to cache or not. This is an overly simplified example - but illustrates the point. Say you have 4GB of RAM available to dedicate to a shared buffers on a server, and two databases (DB A and DB B) to run. You either set up a single instance with a 4GB pool, or two instances with 2GB pools each. Let's say that DB A gets really busy, and DB B is not. In the shared instance approach, the instance can evict buffers cached for DB B in order to load buffers needed for DB A. In the split instance, you can't.

Brad.