maxabbr wrote
> Hello,
>
> We are starting a new project to deploy a solution in cloud with the
> possibility to be used for 2.000+ clients. Each of this clients will use
> several tables to store their information (our model has about 500+ tables
> but there's less than 100 core table with heavy use). Also the projected
> ammout of information per client could be from small (few hundreds
> tuples/MB) to huge (few millions tuples/GB).
>
> One of the many questions we have is about performance of the db if we
> work with only one (using a ClientID to separete de clients info) or
> thousands of separate dbs. The management of the dbs is not a huge concert
> as we have an automated tool.
>
> At Google there's lots of cases about this subject but none have a
> scenario that matchs with the one I presented above, so I would like to
> know if anyone here has a similar situation or knowledgement and could
> share some thoughts.
>
>
> Thanks
>
> Max
My untested thoughts here is a hybrid approach. Allow any one database to
contain any number of stores on a common schema with a controlling clientId
column. But allow for multiple databases. Furthermore, any non-client
shared data could belong to a separate database of reference with the
possibility of caching said data in each of the client databases where
applicable.
Though until your needs dictate that level of complexity you can have just
one data and schema set for all clients.
While row-level-security will make this more tenable generally this model
works best if all client access is made via middleware. You mitigate that
by using separate databases for any clients with a higher risk profile
(i.e., larger datasets, direct access to the DB, etc...)
Adding in clientId overhead will degrade performance somewhat but increase
your flexibility considerably. That is often a worthwhile trade-off to make
even if you decided to create separate schemas/databases.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/One-huge-db-vs-many-small-dbs-tp5781827p5781924.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
