Re: How PostgreSQL handles multiple DDBB instances?

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