Skip site navigation (1) Skip section navigation (2)

Re: Which gives good performance? separate database vs separate schema

From: Robert Klemme <shortcutter(at)googlemail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Which gives good performance? separate database vs separate schema
Date: 2010-11-26 11:38:44
Message-ID: AANLkTi=LB-FfF91RG0Ahgc24q3K2iAcRAOz9-EVuvK9Z@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Nov 25, 2010 at 4:46 PM,  <tv(at)fuzzy(dot)cz> wrote:
>> I am not facing any issues, but yes I want to have optimal performance for
>> SELECT and INSERT, especially when I am doing these ops repeatedly.
>> Actually I am porting from Oracle to PG. Oracle starts a lot of processes
>> when
>> it needs to run many schemas. I do not think PG would need much more
>> resources
>> (mem, cpu) if I go for different database for each process..? Also, is
>> there any
>> limit on number of databases I can start using a PG server?
>
> Hm, I would try to run that using single cluster, and only if that does
> not perform well I'd try multiple clusters. Yes, Oracle starts a lot of
> processes for an instance, and then some processes for each connection.
>
> But again - in PostgreSQL, you do not start databases. You start a
> cluster, containing databases and then there are connections. This is
> similar to Oracle where you start instances (something like cluster in
> PostgreSQL) containing schemas (something like databases in PostgreSQL).
> And then you create connections, which is the object consuming processes
> and memory.
>
> PostgreSQL will create one process for each connection (roughly the same
> as Oracle in case of dedicated server). And yes, the number of connections
> is limited - see max_connections parameter in postgresql.conf.

I think this is a pretty common trade off that is frequently made:
basically the question is whether one wants to reserve resources or
share resources.  In this case resources would be memory and maybe
also disk IO.  With two separate clusters each one has its own memory.
 Which means that if one instance is idle and the other one has high
load then the idle instance's memory cannot be used by the other one.
With a single cluster all the memory is shared which has the downside
that high load of one instance can affect the other instance's memory.

It depends on the usage patterns (load) and the user's policy which
way to go.  Since the OP mentioned "many instances" the aspect of
overhead of many instances (even if idle) may come into play as well.
Plus, a single cluster is likely easier to administer than multiple.
But of course the more DB there are in a single cluster the higher the
likeliness of bottlenecks (see the other thread "Performance under
contention").

Kind regards

robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

In response to

pgsql-performance by date

Next:From: felixDate: 2010-11-26 14:22:21
Subject: Update problem on large table
Previous:From: Pierre CDate: 2010-11-26 09:46:11
Subject: Re: Optimizing query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group