I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases.

We are just starting to develop a web app and have a similar problem to solve (lots of customers with their own isolated data).

What problems should I expect with using a database per customer? How can I solve them?

My Initial Thoughts

Advantages of a database per customer

  • Simpler database schema
  • Simpler backups - you can backup each customer in turn without it really impacting on other customers.
  • Makes it easy to export a given customers data.
  • Better cache performance - a write to one of the more active tables only impacts that single customer that performed the write.
  • Easier to scale across hardware. For example, when we need to go from 1 to 2 servers, we just move half our customers to the new server.


  • Can MySQL cope with 5,000 databases? Would performance suck?
  • Changes to the schema can be hard to replicate out across all the databases. We would really really have to have an automated plan for this, such as versioning the schema and a script that understands how to take a database from one version to another.
  • Doing anything that is common to all our customers might be awkward or impossible
  • Similar to above, but any analytics we want to perform across all our customers might be impossible. How should we track usage across all customers for example?
  • 3
    Remember that "database" means different things to different people. In the Oracle world, a database per user would be massive overkill. But in MySQL "database" is synonymous with "schema".
    – Gaius
    Commented Feb 3, 2011 at 11:14
  • I mean it in the mysql sense. USE CompanyData; Commented Feb 3, 2011 at 11:31
  • 1
    Microsoft has a detailed article on multi-tenant data architecture. Commented Aug 10, 2011 at 23:19
  • i wouldn't say versioning the schema is a disadvantage...more work, but better overall Commented Feb 17, 2013 at 22:43

6 Answers 6


This solution is called a multi-tenant design where each tenant (customer) has their own database. Given that, there are some other considerations to the alternative approach which is a single database:

  1. With a single database, everyone must be on the same version no matter what. It isn't possible to upgrade some customers and not others. This can be problematic if a customer wants a hotfix of an application that isn't ready for wide release.
  2. With a single database, when you do an upgrade, every client is down. If something goes wrong, every client is screwed.
  3. With a single database, it is much more difficult to throttle resources. I.e., if one client is hammering the database, it is harder to give them more resources separate from everyone else.
  4. It is much more difficult to allow users to host their own versions of your application. If you are building a solution that will be used by large enterprises, this is often a non-starter. Their IT department wants complete control over access to the system.
  5. It is probably cheaper to scale out databases rather than scale them up. I.e., having to invest in faster hardware to host one database to rule them all is probably more expensive than being able to scale customers out to smaller, less expensive database servers. I can't say this one definitively because it depends greatly on the server software. If you stick with MySQL, this is probably true because the licensing costs are negligible. However, if you move up to SQL Server for example, scaling out becomes much more expensive unless you use a VPS environment and the cost-benefit of scaling up vs. scaling out changes. I can say, however, that once your database gets very large, management requires ever-greater levels of expertise. Very large databases require playing around with multiple filegroups and pushing certain indexes to different spindles to get better performance. In short, they get can complicated very quickly.

Having separate databases does mean you have to build an update mechanism which matches the database version with the application/site version. However, separate databases do provide superior isolation of data and IMO have a lower cost of hosting. It isn't a solution for all scenarios. If your system were never going to be hosted outside of your hosting and needed to scale up in customers rapidly and having all users on the same version of the application and database schema was desirable, then certainly having a single database is a better approach.

  • 3
    I run web services with both the shared database and multi-tenant separate database setups. There's times where both are the right choice. On the app where I have a separate database per customer, I've run into the exact same 5 reasons it was the right choice for that app. Commented Feb 5, 2011 at 20:06
  • Amazon's recent Aurora serverless cloud DB supposedly automatically provisions more resources when needed for higher load, and they seem to encourage a single-database design. But I don't fully understand it. I think I will go with a single DB, though, with separate tables for each user. That might make it easier to split them off into separate DBs if I need to, and will make it easier to do aggregate queries against all user data. Commented Dec 14, 2018 at 0:48
  • 2
    Just something to watch out for: I have all my customers in one db and use a db code layer that ensures that every query includes customer specific criteria. The dangerous bit is when you have to step outside of the database layer to do something very specific - like a horrible big complicated query where data can leak in from somewhere unexpected. Commented Oct 30, 2019 at 9:54

In my experience you shouldn't create one database per customer. Let me give you an example:

Last year I worked with 70 databases (a lot less than 5000), each with the same schema and all. In theory, things would go as planned (as you mention in the advantages section), but in reality not so much. We had many problems with updating schemas, user support, software update, you name it. It was awful.

We used Firebird and I was hired way after the product was shipped, but this gave me the knowledge to never work with separated databases.

I'm not saying you can't pull it off, I'm saying things can go very wrong and to be honest, your advantage list didn't sound appealing enough to take the risk. Most of them can be accomplished with a single database.

  • We implemented a Multiple Listings Database that services several customers. We wound up in a situation where customers started wanting custom results. To solve this issue we cloned the stored procs and gave them unique customer name prefixes and then called them from within the application. On the other hand we sold 150 webstores each with their own separate database (97% the same). So both can be done it depends on the situation. Commented May 12, 2011 at 19:38
  • Nice. I'm not saying it can't be done, just that its not as easy as it sounds, good for you Gunny.
    – eiefai
    Commented May 12, 2011 at 20:17
  • 3
    Would be nice if you could give examples of what exactly went wrong. Sure it is harder to keep all databases up-to-date, but to decide we have to be able to measure pro vs cons. Commented Aug 8, 2016 at 11:34

You'd likely want to keep another database to track what version each customer is at, so you could keep track of which ones have or haven't undergone the last round of modifications.

Scripting the upgrades wouldn't be that difficult ... you could write something that looks at the catalog of databases and applied the necessary changes to get each database to the latest version, possibly skipping those that shouldn't be upgraded for some reason.

As mysql 'databases' are just schemas, as Gaius pointed out, if it's all running from the same server instance, you can just qualify the name of the tables you're trying to modify, or get information out of:

alter schema.table ...
select ... from schema.table


If you start breaking things up across multiple servers, you can still script something that makes connections to multiple servers so you can apply all of the changes; for the analytics, again, you could set a bunch of database links using federated tables in your master database to access the data from one place, as you'd just be reading from the tables.


Also, be aware that they're not using mySQL for stack exchange, they're using SQL Server.

And I have no idea what sort of performance overhead there'd be in mysql at that scale, I don't think I've ever gotten past 30 'databases' in mysql.

  • Why not keep a version info table in your db itself? Commented Aug 8, 2016 at 11:34
  • @Boris : because's much more of a pain in the ass to connect to each database to ask it for its version when you have dozens or hundreds of databases. It's not a bad idea for each one to track itself, but it's also worth having a master list for the DBA
    – Joe
    Commented Aug 8, 2016 at 14:26

I have a Web/DB Hosting client that has 750+ customer databases with the same number of tables (162) and same table structures. Combined, all of my client's customer data total 524GB (95% InnoDB)

Imagine all of these databases competing for 13G of innodb buffer pool on nine DB servers via circular replication. Scaling out with that hardware configuration was not enough. Immediately, we recommended to the client to scale up.

We recently migrated this client to 3 DB servers with far more horsepower (At all costs, stay away from SSD in high-write environments, ALWAYS !!!). We upgraded them from MySQL 5.0.90 to MySQL 5.5.9. Dramatic Differences were seen almost instantly.

Scaling out must also be considered because if you have hundreds of clients hitting the same memory and disk resources, scaling out reduces their usage linearly (O(n)) where n is based on the number of DB servers in a multimaster environment.

In the case of my client, my company is reducing him from 9 DB servers (Quad Code, 32GB RAM, 824G RAID10) to faster DB servers (Dual HexaCore [that's right 12 CPUs], 192GB RAM,1.7TB RAID10) of MySQL 5.5.9 (to table take advantage of the multiple CPUs). In addition, imagine 150GB innodb buffer pool in 50 partitions of 3GB each (Multiple InnoDB buffer pools is a new feature in MySQL 5.5). A smaller scale out, but massive scale up, had worked for my client's unique infrastructure.

MORAL OF THE STORY : Scaling up or out is not always the solution if you have badly designed tables. What I mean is this: If index pages have lopsided key population for multicolumn indexes, querying keys from the lopsided parts of indexes leads to table scan after table scan, or at least indexes that never get used due to being ruled out by the MySQL Query Optimizer. There simply is no substitute for proper design.

  • 2
    I know this is really old, but I'm wondering what the reasoning is behind your comment about SSDs in high-write environments. Can you enlighten me?
    – elixenide
    Commented Oct 29, 2015 at 15:34
  • 4
    @EdCottrell My guess is this was a warning about SSDs limited writes. At some point this wears the drive to the point that it can no longer be used, I believe over the last few years the TRIM and other technology has been baked into the SSD controller chips to alleviate those problems for the most part so the SSD write isn't as much of a problem though I'm sure it can still be an issue. Commented Jan 26, 2016 at 0:30

MySQL creates databases in separate directories so a lot depends on the underlying operating system and how many folders/file handles it can handle. Shouldn't be an issue with modern operating systems but that's where a lot of the bottleneck will come from.


There isn't anything saying you have to host different versions of the database or app. Whats wrong with simply isolating the data by doing one db per customer and having one version of the database and app? Of course each customer db would have to be cloned from a template of the current working version. From a security and data isolation standpoint, I think this is ideal.

The only downside I can see is that you would have to manually update each database when creating a new version. This could be easily automated though.

Not the answer you're looking for? Browse other questions tagged or ask your own question.