Introduction
This post aims to help the developer / architect to choose the database strategy for a multi-tenant application
In a multi-tenant application, there are many areas or concerns that require attention like the cost, data isolation and protection, maintenance, and disaster recovery.
Multi-tenant solutions can opt any strategy fromone database per tenant (fully isolated) toone row per tenant (shared database) or tohave separate schema per tenant in the same database(Shared server, separate schemas).
Strategy 1: Fully Isolated / database per tenant
- This approach is the most expensive option. (Higher the # of tenants, higher the # of servers)
- Highest degree of data isolation.
- Simplified Disaster recovery as there are separate backups and separate ALB's for the database.
- The point of view of maintenance is concerning because of the task of keeping the updates to the database in sync with the rest of the tenants. With the right DevOps plans, these can be tackled, but require effort in the automation, maintenance of the versions, upgrades across all the tenants.
- Given that "fully isolated" looks like easily customizable, can raise more maintenance issues. For example, A tenant might have a different pattern of application usage, which might suggest database tuning and this might not be applicable to the rest of the tenant's.
- Since we have only one tenant data, there will be lesser number of rows per table there by increasing the performance of the queries.
Strategy 2: Shared database / Shared Schema
- This model is having lower expenses because we have a single server.
- There might be questions on data isolation, however employing the right strategy can help tackle this problem.
- In this approach, each table has a TenantId column that identifies which tenant this row of data belongs to. Caution to be exercised to apply the TenantId filter always
- Disaster recovery in this case is simple because there might be a case where the database is to be restored and upon restoring all the tenant's data will be restored. There is a single universal backup and restoration process
- The schema (table structures) can be easy maintained because its a single database and all tenant's use it. There are pain points in upgrades because the upgrades are global and need consent from every tenant and also there is lower chances of having a tenant specific customization.
- The volume of the data per table is higher, which might impact the queries performance.
Strategy 3: Separate schema / Tenant
- All the Tenants share a single database, but each tenant has it's own named schema. In this case, there are points to consider like the number of schemas per database and the partitioning of the schemas across multiple servers and identification of the servers for the choice of the schemas.
- This scheme has better isolation than using a "shared database", we have sql server level permissions the the schemas through GRANT, REVOKE etc
- The disaster recovery system is a bit complex depending upon the database server used because we need to restore the database schema alone and not the full database as such.
- The aspect of maintenance is similar to that of the "fully isolated" because we might have separate schemas and need some effort from the automation process point of view for deploying the upgrades.
- There are chances for performance degrade as the same server will be on load for all the tenant's.
It will be nice to have a table that consolidates all these points, let me build and update that to this post.
Comments
Post a Comment