Architecting Multi-tenant Applications
In building a Saas (Software-as-a-service) product, an essential ingredient is the ability to have multiple tenants or customers espescially when the target fo the product is a team, group or organisation.
There are three options:
- One database per tenant
- One schema per tenant
- Have all tenants share the same tables
According to citrusdata your choice comes down to:
- If you are building for scale, have tenants share the same tables. (1000’s of tenants)
- If you are building for isolation, create one database/schema per tenant. (5 - 50 tenants)
When doing any job, always use the right tool for the job. You don’t use a hammer for a screw, so you should use a database for what it is for - storing large amounts of data.
When you split up the databases, the database shared buffers, operating system cache, connection count, background processes and logs may be an issue.
If you are using a single table, keep in mind that most transactins and joins will have the
tenant_id dimension, so you should shard your database on this key so all related records are co-located.
Ease of Maintenance
Another issue is changes to the schema and adding of indexes. If you have multiple schemas or databases, then you have to apply changes to all schemas and what if an error occurs mid way.
The difficulty lies in the ORM (Object Relational Mapper) of your framework of choice and how easily the tenant dimension can be included.
Google F1 Paper
This google paper explains some challenges faced when scaling adwords for multi-tenancy