A beginner’s guide to database multitenancy

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

In software terminology, multitenancy is an architectural pattern which allows you to isolate customers even if they are using the same hardware or software components. Multitenancy has become even more attractive with the widespread adoption of cloud computing.

A relational database system provides a hierarchy structure of objects which, typically, looks like this: catalog -> schema -> table. In this article, we are going to see how we can use each of these database object structures to accommodate a multitenancy architecture.

Catalog-based multitenancy

In a catalog-based multitenancy architecture, each customer uses its own database catalog. Therefore, the tenant identifier is the database catalog itself.

Since each customer will only be granted access to its own catalog, it’s very easy to achieve customer isolation. More, the data access layer is not even aware of the multitenancy architecture, meaning that the data access code can focus on business requirements only.

This strategy is useful relational database systems like MySQL where there is no distinction between a catalog and a schema.

The disadvantage of this strategy is that it requires more work on the Ops side: monitoring, replication, backups. However, with automation in place, this problem could be mitigated.

For more details about catalog-based multitenancy, check out this article.

Schema-based multitenancy

In a schema-based multitenancy architecture, each custom uses its own database schema. Therefore, the tenant identifier is the database schema itself.

Since each customer will only be granted access to its own schema, it’s very easy to achieve customer isolation. Also, the data access layer is not even aware of the multitenancy architecture, meaning that, just like for catalog-based multitenancy, the data access code can focus on business requirements only.

This strategy is useful for relational database systems like PostgreSQL which support multiple schemas per database (catalog). Replication, backing up, and monitoring can be set up on the catalog-level, hence all schemas could benefit from it.

However, if schemas are colocated on the same hardware, one tenant which runs a resource-intensive job might incur latency spikes in other tenants. Therefore, although data is isolated, sharing resources might make it difficult to honor the Service-Level Agreement.

For more details about schema-based multitenancy, check out this article.

Table-based multitenancy

In a table-based multitenancy architecture, multiple customers reside in the same database catalog and/or schema. To provide isolation, a tenant identifier column must be added to all tables that are shared between multiple clients.

While on the Ops side, this strategy requires no additional work, the data access layer needs extra logic to make sure that each customer is allowed to see only its data and to prevent data leaking from one tenant to the other. Also, since multiple customers are stored together, tables and indexes might grow larger, putting pressure on SQL statement performance.

I'm running an online workshopk on the 14th of May about The Best Way to Fetch Data with Java Persistence and Hibernate.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

As you can see, there are multiple strategies to implement a multitenancy architecture on the database side. However, each one has its own advantages and disadvantages, so you must make sure you choose the right strategy according to your project DevOps requirements.

Transactions and Concurrency Control eBook

4 Comments on “A beginner’s guide to database multitenancy

  1. Does Hibernate supports a multi tenancy scheme where the table names can be used as a discriminator? that is, in your example app there would exists tables users_america, users_asia, users_africa, etc. in the same database/schema.
    This design could provide better concurrent access compared to table-based multitenancy.

    • Only database and schema-based multitenancy schemes are supported. The one you described is not supported.

      • Great article.
        I use Hibernate multitenancy using the table prefixes in my multi company application. Each company has its own prefix for tables. This helps to aggregate multiple companies and also simplifies backups, all tables being in the same schema. Works fine on MySQL with more than 300 tables per company, tens of companies and some tables with millions of records with no special hardware needed. For that in Hibernate I use a custom naming strategy for table names.
        Because I use JOOQ (only for native SQL generation), I configured JOOQ class generation to use tables prefix multitenancy.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.