Most of the contents in this post is taking from Multi-Tenant Data Architecture. This is just a quick reference.
Designing single-instance, multi-tenant architecture is not that easy. It required more work to do. SaaS application is distinguished by three qualities: scalability, configurability, and multi-tenant efficiency.
Three approaches for multi-tenant:
- Easy to implement.
- Isolation which lead to security
- Ability to customize to each tenant.
- Easy to backup and restore in case of failure
- cost of maintenance and backup equipments.
- The number of tenants that can be housed on a given database server is limited by the number of databases that the server can support.
Suitable: For customers that are willing to pay extra for added security and customizability. For example: banking or medical records.
Shared Database, Separate Schemas:
- Easy to implement.
- Moderate degree of logical data isolation for security-conscious tenants.
- More tenants per server than the separate-database approach, which leads to low cost.
- Tenant data is harder to restore in the event of a failure. Therefore, to restore a single customer’s data, the database administrator may have to restore the database to a temporary server, and then import the customer’s tables into the production server–a complicated and potentially time-consuming task.
Suitable: For applications that use a relatively small number of database tables, on the order of about 100 tables per tenant or fewer.
Shared Database, Shared Schema
- Lowest hardware and backup equipment costs.
- Additional development effort in the area of security.
- More harder to restore. Restoring data for a tenant is similar to that for the Separate-schema approach, with the additional complication that individual rows in the production database must be deleted and then reinserted from the temporary database.
Suitable: when it is important that the application be capable of serving a large number of tenants with a small number of servers.
For more details read this topic Choosing an Approach
Scalability of database:
Two main tools to use when scaling out a database out are replication and partitioning.
Replication involves copying all or part of a database to another location, and then keeping the copy or copies synchronized with the original.
Partitioning involves pruning subsets of the data from a database and moving the pruned data to other databases or other tables in the same database.
Horizontal partitioning means that the database is divided into two or more smaller databases using the same schema and structure, but with fewer rows in each table. The simplest way to scaleout a shared database is through horizontal (row-based) partitioning based on tenant ID. SaaS shared databases are well-suited to horizontal partitioning because each tenant has its own set of data, so you can easily target individual tenant data and move it.
Vertical partitioning means that one or more individual tables are divided into smaller tables with the same number of rows, but with each table containing a subset of the columns from the original.
Database clustur: deplicate databases on multible servers and load balance connection to these servers.