The Concept of “Schema per Tenant”

In a multi-tenant architecture, a single instance of a software application serves multiple clients or “tenants.”

The term “tenant” refers to each individual client or customer that uses the shared instance of the application. Each tenant’s data is isolated and remains invisible to other tenants. In the context of databases, there are a few common strategies for data isolation in multi-tenant architectures:

1. Separate Databases: Each tenant has its own database, ensuring complete data isolation at the database level.
2. Shared Database, Separate Schemas: All tenants share the same database, but each tenant has its own schema. A schema in this context is a logical collection of database objects such as tables, views, indexes, and stored procedures that are separate from other schemas in the same database.
3. Shared Database, Shared Schema: All tenants share the same database and schema, with data co-mingling in the same tables. Data is typically separated by a tenant identifier column in each table.

The “schema per tenant” approach falls into the second category. In this model, while all tenants share the same underlying database, they have separate schemas. This strategy offers several benefits:

• Isolation: Each tenant’s data is logically separated from others, which can enhance security and simplify some aspects of database management.
• Customizability: It allows for some level of customization at the schema level. Different tenants might have different schema versions, depending on their particular needs or the stage of rollout of new features.
• Maintenance: With this approach, maintenance operations like backups, schema migrations, and performance tuning can be performed on a per-tenant basis, which can be both an advantage and a disadvantage, depending on the operations.
• Efficiency: It can be more efficient than the separate database approach in terms of resource utilization since the underlying database engine and resources are shared.

However, there are also downsides to consider:

• Complexity: Managing multiple schemas can become complex, especially as the number of tenants grows.
• Resource Sharing: Because the physical database resources are shared, a poorly performing query in one tenant’s schema could potentially affect the performance of the database for all tenants.
• Scaling: While schemas are logically separate, they still reside on the same physical server or cluster, which may limit the ability to scale out the system for individual tenants.

Primary concern with the concept of “schema per tenant” is how to manage schema updates and migrations across potentially hundreds or thousands of schemas, rather than a single schema.