Re: How PostgreSQL handles multiple DDBB instances?

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