Craig Kerstiens: Designing Your Postgres Database for Multi-tenancy

If you're building a B2B app chances are it's multi-tenant, meaning one customer data is separated and doesn't intermingle with other customer data. When building the app itself you'll typically have some URL route defining the specific tenant. When it comes to your database there are a number of design patterns that you can use that offer different advantages, trade-offs, and scaling opportunities. When you begin building your app, time to market is essential, but you don't want to make decisions that are hard to reverse. We're first going to take a look at the most common design patterns for multi-tenant database design in Postgres, followed by some guidance on how to design your data model so that you're prepared from day 1 for massive scale (should you need it). Three approaches to multi-tenant database design One database per customer The first option is simply to have a separate database per customer. In this case you could have complete copies of your web application running for them as well. This gives you the maximum amount of isolation, you can encrypt your databases with different keys, scale them independently of each other, upgrade them at different times, leave old versions of your app and migrations on one while still updating other customers. If this all sounds great - slow down and beware. Maintaining different versions of your database and different versions of your schema, figuring out how to run migrations across multiple different databases (a seemingly solved problem for most frameworks when dealing with a single database) can become a significant pain at larger scale. What is a larger scale? Managing 5 databases is fine, managing 10 you're probably okay, but if you anticipate 50 customers or more steer clear from this approach. Schema per customer The second approach is to use schemas. In many ways this approach is not dissimilar from the one database per customer. But there are both some pros and cons of this approach... Pros: Can easily join acro[...]