Multi-Tenant Database Architecture: Database per Tenant

Intro

This guide describes how to utilize Atlas to manage database schemas in "Database per Tenant" architectures, a common pattern for deploying multi-tenant applications.

What is a Database per Tenant Architecture?

In a "Database per Tenant" architecture, each tenant has its own dedicated database (or a schema), while the application and compute are shared across tenants. This differs from full isolation, where each tenant also gets its own application deployment.

It is one of three common multi-tenant database architectures. They differ in where the tenant boundary sits and how a schema change reaches every tenant:

Architecture	Tenant boundary	A schema change runs	Fits when
Shared schema	A tenant ID column on every table	Once, for all tenants	Many small tenants share one data model, and cross-tenant queries are common
Schema per tenant	One schema per tenant in a shared database	Once per tenant schema	Tenants need their own tables but can share one database
Database per tenant	One database per tenant	Once per tenant database	Tenants need strict isolation, per-tenant backup and restore, data residency, or per-tenant database sizing

In a shared schema, every query must filter by tenant ID; PostgreSQL row-level security policies can enforce that filter in the database. In MySQL, CREATE SCHEMA is a synonym for CREATE DATABASE, so schema per tenant and database per tenant are the same layout. In PostgreSQL, a connection can access only one database, so queries across tenant databases need an extension such as postgres_fdw.

Atlas manages schema per tenant and database per tenant the same way: each tenant's schema or database is a target in a target group, and the rest of this guide applies to both.

Advantages

Database per Tenant architectures offer several advantages over other multi-tenant architectures:

1. Data Isolation: Each tenant has its own dedicated database, providing strong data isolation between tenants. By isolating data on the database level, you can ensure that data from one tenant is not accessible to another tenant, providing better security and privacy guarantees.
2. Data Compliance: By isolating tenants on the database level, you can ensure that each tenant's data is stored in compliance with data regulations and privacy laws relevant to that tenant. For example, you can host the specific database in a region that complies with the tenant's data residency requirements or even in your customer's own infrastructure.
3. Performance: By isolating tenants on the database level, you can ensure that the performance of one tenant's database operations does not impact the performance of other tenants - eliminating the "noisy neighbor" problem. Additionally, you can utilize different instance types or configurations for each tenant's database to meet their specific performance requirements.
4. Simplified Development (compared to shared database architectures): Codebases for multi-tenant applications are notoriously complex. Every action against the database must be made "tenant-aware" to ensure that data is correctly isolated. By isolating tenants on the database level, applications become effectively single-tenant, simplifying development, reducing the mental overhead of handling multi-tenancy, and reducing the risk of data leakage between tenants.
5. Simplified Deployment (compared to full isolation): In a full isolation architecture, each tenant has its own deployment, requiring separate infrastructure, monitoring, and maintenance. By isolating tenants on the database level, you can share infrastructure, monitoring, and maintenance across tenants, reducing operational overhead.
6. Simplified Maintenance: Running tenants on different databases simplifies maintenance tasks: Deleting, exporting, and loading tenant data do not require special toolings as most databases offer these functionalities.

Challenges

This architecture decisions is one of my biggest regrets, and we are currently in the process of rebuilding into a single database model.

HN Thread on Database per Tenant Architecture

Despite their numerous advantages, Database per Tenant architectures present unique challenges, mostly around managing database schema migrations:

• Migration Duration - As the number of tenants grows, deployment time increases linearly with the number of tenants. This is especially problematic when deploying schema migrations that take time, such as data migrations or index rebuilds.
• Schema Consistency - Ensuring that all tenant databases have consistent schema versions can be complex. Any schema change needs to be applied across all tenant databases, which can be time-consuming and error-prone.
• Detecting Drift - Migrations can fail due to various reasons, such as network issues, database downtime, or data dependent issues (such as constraint violations). Such inconsistencies can cause application downtime for users, as new application versions are rolled out and expect database changes to already have been applied. Detecting these inconsistencies (schema-drift) across a large fleet of databases can be challenging.
• Rollbacks - Rolling back schema changes across multiple databases can be difficult. In a shared database architecture, rolling back a schema change is a single operation. In a database per tenant architecture, rolling back a schema change requires rolling back the change on each tenant's database, which needs to be orchestrated somehow.

Database per Tenant Architectures with Atlas

Atlas was built from the ground up to handle database-per-tenant architectures. In fact, Atlas Cloud itself is a multi-tenant application that uses a database-per-tenant architecture to manage its own schema.

Atlas supports database-per-tenant architectures both on the CLI and in the Cloud control plane.

In the following sections, we'll describe how to use Atlas to manage database schemas in a database-per-tenant architecture.