Database Multi tenancy

Let’s start by understanding what a tenant is, so a tenant is someone who rents land or property for their home or business.

The technical version of tenancy is an architecture in which an instance of an application and infrastructure serves a customer.

Single Tenant vs Multi-Tenant

Multi Tenancy :

Multi-tenancy is a reference to the mode of operation of software where multiple independent instances of one or multiple applications operate in a shared environment. The instances (tenants) are logically isolated, but physically integrated.

Importance :

• Multi-tenancy has seen a lot of could adoption and is used most with cloud computing.
• Multi-tenant architectures are found in both public cloud and private cloud environments, allowing each tenant’s data to be separated from each other.
• Multi-tenancy is also important for the scalability of public and private clouds and has helped make multi-tenancy a standard.
• The multi-tenant architecture can also aid in providing a better
  Return On Investment for organizations, as well as quickening the pace of maintenance and updates for tenants.

Example, in a multi-tenant public cloud, the same servers will be used in a hosted environment to host multiple users. Each user is given a separate and ideally secure space within those servers to store data.

Single database, shared schema :

Uses a single database to hold all the data for all tenants, and a Tenant ID associates each tenant with the rows that it owns. This shared schema approach has the lowest hardware and backup costs because it allows you to serve a large number of tenants/clients using one database.

The shared-schema approach is appropriate when it is important that the application can be capable of serving a large number of tenants/clients with a small number of servers, which exponentially reduces the maintenance and support requirements of applications architected in this manner.

Pros and cons :

Pros :

• Single database schema to maintain and a simple schema update rollout process so that the process needs to be applied once.
• Manage the High Availability/ Disaster Recovery/ Maintenance operation/ Monitoring strategy for a single database.
• Single schema, Single database to connect.

Cons :

• No tenant isolation (all tenants will be clubbed with the other tenants where tenant ID is the only differentiation).
• If a developer misses a WHERE clause to filter on the tenant id, there are chances that one tenant’s data could be exposed to others.
• Can’t easily restore a single tenant’s data.

Single database, Separate schema :

A Single database would hold the data for all tenants. Separate tables for each tenant where each table is set under a tenant-specific schema.

Pros and cons :

Pros :

• Tenant data has more isolation.
• Manage the High Availability/ Disaster Recovery/ Maintenance operation/ Monitoring strategy for a single database.
• Optimizations could be made at an individual tenant’s schema level.

Cons :

• Can’t easily restore a single tenant’s data (although it’s a slightly better process than (Single database, shared schema approach) due to isolation of tenant data).
• As the number of tenants grows, there will be a lot of database objects being created to manage and maintain.
• Schema updates are more involved, needing to be rolled out to n tenants.

Database Per Tenant :

Each tenant has its 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.

Pros and cons :

Pros :

• Highest level of tenant isolation, supporting options for a shared server and isolated servers.
• Can easily restore/relocate/clear down a tenant’s data.
• No added query complexity.

Cons :

• Potentially more servers to patch and keep secure.
• As the number of tenants grows, there will be a lot of database objects being created to manage and maintain.
• Adding new tenants is more involved, as new schemas need to be created.

Multiple Databases, Multiple Tenants Per Database, Shared Schema

It’s a combination of Single Database, shared schema, and Database per tenant. A pool of databases would be available. Tenants share a database and schema with other tenants but are spread over multiple databases.

Pros and cons :

Pros :

• Some tenant isolation is possible in general over Single Database Single schema.
• Possible to relocate a tenant’s data (although harder than approach Database per tenant)
• Choose to balance between cost (higher tenant density) and performance (lower tenant density).

Cons :

• Tenants still share a database and schema with others.
• More maintenance is required.

Conclusion :

That summarises the explanation of multi-tenancy, usage of multi-tenancy in an architecture level in Database with its respective advantage and disadvantage. The approach type required is decided based on the application type and its data size.