06
February 2018
Database Multi-Tenancy in the Cloud and Beyond
In today’s wave of Enterprise Cloud applications, having trust in a data store behind your software-as-a-service (SaaS) application is a must. Thus, multi-tenancy support is a critical feature for any enterprise-grade database. This blog post will cover the ways to implement multi-tenancy, and best practices for doing so in MemSQL.
As customer table sizes grow, you will need to scale out your multi-tenant database across dozens of machines. To support rich analytics about your customers or as a feature for your end customers, you will want a solution that scales without bogging down reporting capabilities. Successful multi-tenant platforms require massive scalability, online patching/upgrading, the ability to process high volumes of data ingestion, and deliver high performing complex analytics.
MemSQL delivers the requirements for a multi-tenant platform through the rich analytical and transactions capabilities of SQL. MemSQL also scales to hundreds of nodes in a cluster leveraging shared nothing commodity hardware. Combining these multi-tenant capabilities with extreme data ingestion makes MemSQL a truly unique product to deliver SaaS data.
Most database architectural patterns for multi-tenant applications follow one of three approaches:
- Separated Database
- Separate Schema
- Shared Schema
If isolation requirements are not accounted for early in application development, retrofitting them can be even more costly. In our conversations with customers, scale is often the driver. Let’s delve into the three models:
1. Separated Database
Instantiating each tenant as its own database in MemSQL is a practical idea for data isolation guarantees. However, if you plan to scale past 50-100 tenants, spinning up a new database instance for each tenant will increase maintenance cost.
While former SaaS applications make good use of separate database instances, scaling a software business in the cloud necessitates alternative solutions. With the database-per-tenant model, there are costs related to increased cloud resource sharing as well as maintaining and managing many databases. For example, you would probably need different backup strategies per tenant. SaaS application developers often struggle when making such trade-offs. Lastly, performing analytics across your customer base would result in relatively complex multi-database queries.
2. Separate Schema
Keeping your data isolated logically, but in different table schemas, can be a beneficial strategy. With the “separate schema” approach, your tenant’s’ data is just as separated logically, but within the same database for less maintenance headaches or overhead. Backups are done in full, for all tenants at once.
3. Shared Schema
In the Shared Schema model, everything is shared – server, database, and tables. All data for your tenants are within the same table(s) in one logical database. A “TenantId” column can be used on the table to differentiate each row of data, and you can use this as your shard key in MemSQL to ensure that all data for a given tenant lies on the same physical machine.
MemSQL delivers what multitenant platforms require: massive scalability, online patching/upgrading, the ability to process high volumes of data ingestion and deliver high performing complex analytics on rapidly changing data.
Source: http://blog.memsql.com/database-multi-tenancy-in-the-cloud-and-beyond/