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

Re: How PostgreSQL handles multiple DDBB instances?

From: Arnau <arnaulist(at)andromeiberica(dot)com>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: How PostgreSQL handles multiple DDBB instances?
Date: 2007-05-25 17:16:05
Message-ID: 465719D5.9040107@andromeiberica.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Tom,

> Arnau <arnaulist(at)andromeiberica(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2007-05-25 17:29:16
Subject: Re: LIKE search and performance
Previous:From: Richard HuxtonDate: 2007-05-25 17:08:53
Subject: Re: LIKE search and performance

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