I have a client that wants me to build a SaaS application with a multi-tenant architecture, where different clients will go in a separate schema on the same db server. I have seen this architecture before, but never really used it, so I am quite new to the concept.

I am strongly against this as I think it would add a level of complexity to the app. Their biggest concern is security.

The only security improvement that I can see from using this is protecting the users from developer mistakes:

  • SQL injections
  • Invalid or missing customer filters on the SQL queries.

Both of these concerns are handled pretty well by modern development frameworks (I plan to use Django). Besides this, I can't really see any advantage.

  • 1
    A separate schema more or less creates a wall that guarantees that there will never be any cross-client leakage of data. May 11 '16 at 23:56
  • I'd suggest using different databases instead of one database with multiple schemas. Some benefits: You can 'roll out' changes; you can backup separately; you can "scale out" vs "scale up" (because you could move databases onto another server if needed). May 12 '16 at 4:22
  • @RobertPaulsen: I would first clarify which DBMS the OP intends to use.
    – Doc Brown
    May 12 '16 at 6:08
  • 1
    The shared schema approach is fail-open, with bugs causing data leaks. The separate schema approach is fail-closed, with bugs causing lack of access. You're going to have bugs, the only question is how you want your app to fail when you do. Feb 16 '17 at 15:27

"I think it would add a level of complexity to the app"

... opposed to what - using just one schema for all tenants?

Then typically the opposite is true. Implementing multi-tenancy in one schema adds the complexity to each and every table where the data might belong to different tenants, which means this kind of complexity will go also into your app. Especially when there are lot of tables, having separate schemas actually reduces the complexity of your app. Make the schema a run-time configurable parameter, then you can develop your app almost as if there is only one tenant.

Moreover, it makes individual administrative tasks like individual backup/restore per tenant a hell lot easier. You could even allow different tenants having different versions of the schema in production at the same time.

Having one schema for all tenants is only beneficial if a big portion of the data in the schema is the same for all tenants, and that data is not individually managed by the tenants.

Of course, what you should avoid in a multiple schema approach is different schema customizations for individual tenants - that would indeed add a kind of complexity you want to avoid. Make sure schema changes are always implemented by versioned scripts (and not "on the fly"), so you can run those scripts against all schemas automatically (you need those either, even when using a single schema, since you typically have at least a dev database, a test database and a production database to manage).

Depending on the DBMS, you could also consider to use different database instances, which might give you a better scaling experience. For a system like Oracle, different schemas might be the better option because of the administrative overhead of managing different DB instances. For a system like MySql, different database instances might be a valid, maybe better approach (depends on your case).

  • implementing row level permissions is simpler in the framework that I plan to use (Django), as opposed to implementing multi-tenancy. Even if I go with the multi-tenant aproach, I also need to implement row level permission within one customer May 12 '16 at 22:09
  • 1
    @MartinTaleski: I don't get your comparison. You want "either use Django or implement multi-tenancy"? I thought you want to use Django to implement an application, and the alternatives are "single schema" vs. "multiple schemas" (both approaches for multi-tenancy, and surely both implementable using Django). And if that framework provides you with row level permissions, fine, might be suitable for both alternatives.
    – Doc Brown
    May 13 '16 at 6:09
  • I back this answer 100% separating schemes between clients, especially if those schemas are likely to be large reduces complexity, and moreso (eliminates) data leakage massively May 13 '16 at 17:18

A multitenant architecture has the benefit of scalability as well as security. When you have all the client data in one table, all customers has access to all customer data, not calculating the code you write to restrict this access. In a multitenant architecture you can set up different users in the db and have a much simpler design of the authentication process.

From a scalabiliy perspective you no longer need huge indexes to search through that will slow down your queries. The data has no relation, at least from a customer pov, because they belong to different customers. It makes sense to separate the data for this reason.

If you set up the architecture in different instances as well, then you can assign resources where it's needed, as well as an added level of security.

Using different instances can be very good, as well as complicating things depending on the features of the application.

I don't agree that this architecture will add complexity to the application. If anything, it should simplify.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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