It is much easier to make API/code multi-tenant, but it is a different story for databases. This problem exists at pretty much all SaaS based solutions, intending to provide data and service level isolation for many users/customers while trying to keep the code + database scalable. However, here are a few strategies that can be employed, based on the pros/cons and the freedom/constraints driving the solution.
Use a tenant_id for each record
- Single database running on single instance
- Every record has a
tenant_idas a part of the composite primary key.
- Application will need to provide this
tenant_idfor every query as a part of the where clause
- One database, one instance
- Relatively easy to run cross-tenant queries
- Relatively easier to manage and apply schema updates
- No db level isolation of tenant data
- No option to support schema changes specific to a tenant
- Imbalance of tenants can adversely affect the DB performance, creating hotspots
- The sheer size and operation frequency increases the chances of db file corruption
- Will hit the file system size limits
- Trust the developer to provide the additional where condition
Put every tenant in a separate db, same instance
- Every tenant gets his schema, but runs under the same physical database server
tenant_idbecomes the db name, so the queries do not change, but the connection string changes.
- Managable dataset size per tenant
- Better control of any schema changes specific to a tenant, if needed.
- DB level isolation of tenant data
- Easier to roll out tenant specific code release as other tenants wont be affected by schema changes if the app servers are deployed separately per tenant.
- Will need to apply schema changes to all such DBs, making coordination of code changes harder if dedicated app servers talk to the db.
- Will hit the system file size limits, as it is still 1 physical file
- The fact that there is only 1 physical file, there could be IO contention just like 1 db per instance
Put every tenant in its own instance
- Every tenant gets a provisioned hardware mysql instance
- The code will need to use separate connection strings, no impact to SQL
- Complete physical isolation of tenant data
- Control of the DB instance size based on the load exerted by the tenant
- Predictable Disk IO performance, due to isolation of data usage patterns
- Can have customized DB schema changes to a tenant
- Easier to roll out tenant specific code release (if dedicated app servers talk to a specific db)
- Each tenant DB instance is $$
- The cost quickly adds up with secondaries (standby) and read replicas if needed
- Same challenge with DB schema updates across all tenants