Multi-tenant Application Database Design
Everyone is always talking about developing a SAAS app. The basis for SAAS is a Multi-tenancy architecture that allows multiple tenants (organizations) access to multiple instances of the software hosted on a single server. The tenants of the software share the server resources and memory.
Designing a database structure that can accommodate this type of design depends depends on several factors among them:
● Security
● Client data isolation
● Database scalability
● Database management (backup and restoration)
● Operational complexities such as schema and tenant management
● Speed
There are a few different ways to design your database depending on your requirements Ill explain those below.
- Database-per-tenant
- Multi-tenant database
- Single multi-tenant database
- Sharded multi-tenant database
Database-per-tenant
As the name implies, a tenant (organization) has its own database. Each time a new tenant is added to the system, a new database is generated for the user. Every time a new tenant is added, a new schema is generated that creates a separate database for the tenant.
This structure facilitates customization on tenant level and proper data isolation. The query speed of this design is relatively okay as the search path to the tenant database is set before queries are run. Easy data backup, restoration and migration are among the benefits of this design.
While this architecture provides data isolation and speed, it does not scale so well. When the number of tenants/clients on the app is small, this design is effective but when tenants are larger, resources compromisation is bound to occur. The number of tables increases, the number of queries increase, so is the size of these tables. Thus, there is a need for continued scaling of resources as more tenants are added.
Multi-tenant database
This design facilitates many tenants to access a multi-tenant database ( of any number).
Because data isolation is not meet by storing several tenants in a multi-tenant database, The database schema contains a tenant identifier column that is used to identifier each tenant in the database. This is the technique used in separating tenant-related data and data retrieval.
System resources are better managed with this design because the multi-tenant database shares compute resources and storage resources across all its tenants. A drawback of sharing resources among different tenants is that there is no way to monitor the usage of these resources and workload of each tenant and this can lead to crippling the server.
Single Multi-tenant database
This design facilitates the usage of a single database for all tenants. As more tenants are added, compute and storage resources are increased.
The major drawback of this design is that the database becomes complex to manage quickly. Management operations for each tenant become extremely challenging to perform. Also, because all tenants access the database and running queries in the same database, the speed of query operation might be affected.
Sharded multi-tenant database
This design facilitates tenant data to be distributed across multiple databases (shards), with all the data for a particular tenant is all contained in a single shard. A sharding key/tenant identifier is managed and imposed by the database schema.
This design provides high scalability and also by distributing tenants across several small databases, the databases can be easily managed. It is also cost-effective
Designing of the shard architecture can be complex due to the need to maintain a mapping between tenants and databases. Also, the application has to maintain a catalogue of the shards and respective tenants.
In addition to the database multi-tenancy design, the structure of the database is also considered in multi-tenancy applications.
Catalogue-based multitenancy — A tenant has its own database catalogue with the tenant identifier to facilitate data isolation. Operation and maintenance could be expensive.
Schema-based multitenancy — A tenant has its own database schema with the tenant identifier to facilitate data isolation. This can be computation expensive if the database resides on a single physical server and Neighbor noise can be a challenge.
Table-based multitenancy — Multiple tenants are clustered on the same database with tenant identifier specified on an identifier column which is added to all tables to facilitate data isolation. The database can be managed like any other single tenancy database but the query traffic to the database is intensive and management operations are difficult.
Scaling and Virtualization for Multi-tenant Application
Once you have your database designed and you can should put some thought into Scaling. I'm not saying build out some complex network structures before you have a proof of concept, but its good to have an understanding and a plan in place to increase computing resources for the multi-tenant application to meet an increase in demand and traffic of the application as more tenants are added. Scaling can be achieved by either scaling vertically or horizontally.
Vertical scalability — It involves increasing resources on a single node in a system. The resources could be additional CPUs, memory or other components that can increase the speed of the system. This is considered as the first step that should be taken in scaling. To fully benefit from vertical scaling, It is required that the architecture of the multi-tenant app is well designed to make use of the available resources optimally and maintain a asynchronicity among all components of the application.
Horizontal scalability — This is considered when the application is deployed in a distributed architecture with multiple instances of the service running on many nodes. Horizontal scaling simply increases the number of instances and nodes by adding more machines into the pool of resources. Increase in the number of instances leads to load balancing of future needs. There is no restriction to horizontal scaling and facilitate fault toleration and data isolation.
Some strategies have been implemented to manage multi-tenant application deployment. Physical separation can be used to give each tenant his own dedicated hardware resources, or virtualization to create virtual hosting environments for each client but on the same physical resources or design the application to automatically adjust to different tenants at runtime.
Virtualization involves using software to create application hosting environments that provide logical boundaries between each tenant with tenants sharing computing resources with virtual separation. This can be achieved by:
Virtual machine technology — it provides an emulator on a hardware to run multiple operating systems on it while sharing the same physical hardware
Virtual service — it reduces the amount of exclusive computing resource used in virtual machines; multiple virtual service instances shares a single instance of the base OS kernel stack.
User session virtualization — dynamically allocates computing resources to user session within a multi-user operating system.
Application pools — they are server-side sandboxes for isolating application processes. Each pool consists of a set of operating systems processes.
Client-side application sandboxing — applications can be loaded into a client-side execution sandbox and executes within a virtual application runtime environment.
So there you have it. If you liked this post, please follow me on the web https://buildingbettersoftware.io/contact/
Resources
https://docs.microsoft.com/en-us/azure/sql-database/saas-tenancy-app-design-patterns
https://rubygarage.org/blog/three-database-architectures-for-a-multi-tenant-rails-based-saas-app
https://www.jitterbit.com/blog/tech-talk-architecting-for-scale-in-your-multi-tenant-cloud/
https://docs.microsoft.com/en-us/archive/blogs/fred_chong/multi-tenancy-and-virtualization
