Message-ID: <465719D5.9040107@andromeiberica.com>
Date: Fri, 25 May 2007 19:16:05 +0200
From: Arnau <arnaulist@andromeiberica.com>
Reply-To: arnaulist@andromeiberica.com
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: How PostgreSQL handles multiple DDBB instances?
References: <4656A3BE.1070409@andromeiberica.com>
	<8684.1180103080@sss.pgh.pa.us>
In-Reply-To: <8684.1180103080@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Tom,

> Arnau <arnaulist@andromeiberica.com> writes:
>>    I have an application that works with multiple customers. Thinking in 
>> scalability we are thinking in applying the following approaches:
> 
>>    - Create a separate database instance for each customer.
>>    - We think that customer's DB will be quite small, about 200MB as 
>> average.
>>    - The number of clients, then DDBB, can be significant(thousands).
>>    - Have as many customers as possible on the same server, so a single 
>> server could have more than 300 DDBB instances.
> 
> This is probably a bad idea, unless each customer's performance demands
> are so low that you can afford to use very small shared-memory settings
> for each instance.  But even small settings will probably eat ~10MB per
> instance --- can you afford to build these machines with multiple GB of
> RAM?
> 
> Can you instead run things with one postmaster per machine and one
> database per customer within that instance?  From a performance
> perspective this is likely to work much better.

   What I meant is just have only one postmaster per server and a lot of 
databases running in it. Something like that:

   template1=# \l
             List of databases
        Name        |   Owner   | Encoding
-------------------+-----------+----------
  alertwdv2         | gguridi   | LATIN1
  postgres          | postgres  | LATIN1
  template0         | postgres  | LATIN1
  template1         | postgres  | LATIN1
  voicexml          | root      | LATIN1
  wikidb            | root      | LATIN1
(6 rows)

   Here I just have 6 databases, so my doubt is if instead having 6 
databases have 300/600 bases running on the same postmaster how this 
will impact the performance e.g.

   template1=# \l
             List of databases
        Name        |   Owner   | Encoding
-------------------+-----------+----------
  template0         | postgres  | LATIN1
  template1         | postgres  | LATIN1
  customers_group_1 | root      | LATIN1
(3 rows)

Instead of:

   template1=# \l
             List of databases
        Name        |   Owner   | Encoding
-------------------+-----------+----------
  template0         | postgres  | LATIN1
  template1         | postgres  | LATIN1
  customers_1       | root      | LATIN1
  customers_2       | root      | LATIN1
  customers_3       | root      | LATIN1
  ...
  customers_500     | root      | LATIN1
(502 rows)


> If you desire to give the customers database-superuser capability then
> this probably won't do, but if they are restricted users it might be OK.

   The users won't have superuser access just execute plain queries.

Thank you very much
-- 
Arnau