When you build a SaaS or some other multi-tenant application, the question of tenant separation comes up fairly early. So you go ahead and Google “how do I separate tenants?”.
Multiple threads come up with different model recommendations, each confidently stating that if you do it any differently, you’ll regret it down the road. For example, one might say something like “I’d actively steer you away from the schema-per-tenant idea.” But… why exactly?
This plurality of opinions almost feels like it’s a matter of taste. It might be quite hard to decipher whether commenters just disagree with each other or are addressing different situations. The truth is much closer to the latter: different models all have their tradeoffs; it’s almost impossible for you to make the right choice without understanding your particular constraints and requirements.
What’s out there?
Let’s look at the options that you are likely to find as recommended on the Internet. Largely, we can group them into three buckets:
- Shared. Single schema, all tables are shared across all tenants, each table has the
tenant_id(or similarly named) field to identify the tenant a particular row belongs to. - Schema-per-tenant. Each tenant gets a copy of the schema with all the tables storing only their own data. Plus a “system” schema shared across tenants with common reference data (countries, timezones, zip codes, etc.).
- Database-per-tenant. Each tenant gets its own database.
It’s important to think of this as a ladder, where each step improves the tenant isolation, but also increases complexity and operational costs. If you are a small team or a solo developer, operational capacity is an incredibly valuable and very scarce resource. You don’t want to spend hours managing multiple databases with complex schema migrations unless you’re at scale that actually demands that.
With that in mind, here is my recommendation.
Choosing the right model
By default, always start with the shared model. It’s the simplest of the bunch and basically consists of three steps:
- Add
tenant_id(or similar) field to each table that has tenant-specific data (skip common tables). - To ensure that data never leaks across different tenants, set up basic row-level security policies.
- At the start of each request, set the current tenant using
SET LOCAL, so that every query is automatically scoped to that tenant.
This way you will guarantee that no tenant will ever get access to another tenant’s data, and you will do so by fully reusing the existing schema, with minimal upfront and further maintenance overhead.
You should only climb the ladder when a specific need forces you to. Here are the most common ones.
Blast-radius minimization
There are cases when you might want different tenants to have different versions of the schema. An example use case for this is cohort-based rollout: you have a group of high-risk tenants, for which changes should appear later than for others. At the application level, you likely have some kind of a feature flag system. But what about the database?
The schema-per-tenant model is useful here, because it allows you to roll out schema changes at the tenant level. Whenever something changes, you initially update the schema only for non-risky accounts and delay doing so for the risky ones. Remember, however, that a schema is not a security boundary, so make sure to apply appropriate permissions.
This approach comes with the price of complexity. Every time you update your application and data model, all changes have to be appropriately applied to the correct tenants. Different versions of the schema must be consistent with the feature-flag-guarded application logic. Moving a tenant from one rollout group to another is not free either, as you have to make sure to run schema migration for that tenant.
Data residency and compliance
Throughout my career, I’ve worked a lot with large enterprises. One thing that always stands out with them is a ridiculously long list of data security requirements. For example, they can sometimes demand that their data is on a separate dedicated server, or that it resides in a particular region of a particular cloud provider.
If you’re in this situation, the database-per-tenant model is your savior. Just put each tenant’s data into a separate database, and you now have the full flexibility of where data resides. You can move a specific tenant’s database to any cloud and region, or in some cases even put it on a dedicated server in the customer’s on-premise environment.
Of course, this adds another layer of complexity. Not only do you have to manage per-tenant schema changes like with the schema-per-tenant model, you also need to manage the underlying infrastructure for each of those databases. You also have to pay the tax for the reference data, as it now has to be replicated across all the databases and kept in sync.
Whale tenants
There’s one caveat to what we just discussed. In reality, only a handful of big “whale” tenants actually demand that kind of isolation — the vast majority of your customers are smaller, with much more relaxed requirements. Those whales are obviously important, but they don’t justify forcing database-per-tenant on everyone.
Here is your secret weapon: the hybrid model. You can combine the database-per-tenant approach with schema-per-tenant or even shared. Each whale goes into its own isolated database. The rest share a single database, with or without multiple schemas.
The biggest overhead with this model is that your application logic has to recognize different database discovery paths for different tenants. E.g., for an isolated tenant it’s a dedicated endpoint with a default schema, while for a regular tenant it’s a shared endpoint with a dedicated schema name.
Conclusion
Let’s circle back to the original question: “How do I separate tenants?”. With everything we’ve just learned, you can answer it with a fairly simple decision tree:
- If you’re just starting, or if there are no specific constraints that you’re aware of, go with the shared model.
- Need schema versioning? Use the schema-per-tenant model.
- Have data residency requirements varying across tenants? Choose the database-per-tenant model.
And always consider utilizing hybrid model techniques, so that you don’t have to re-architect entirely every time a customer has a new requirement.
Good luck!