On 12/05/2013 02:42 AM, Max 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.
In addition to the excellent advice from others, I'll speak from experience:
The best model here, if you can implement it, is to implement shared tables for all customers, but have a way you can "break out" customers to their own database(s). This allows you to start with a single database, but to shard out your larger customers as they grow. The small customers will always stay on the same DB.
That means you'll also treat the different customers as different DB connections from day 1. That way, when you move the large customers out to separate servers, you don't have to change the way the app connects to the database.
If you can't implement shared tables, I'm going to say go for separate databases. This will mean lots of additional storage space -- the per-DB overhead by itself will be 100GB -- but otherwise you'll be grappling with the issues involved in having a million tables, which Joe Conway outlined. But if you don't have shared tables, your huge schema is always going to cause you to waste resources on the smaller customers.