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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
