Part 183: Database Multi-Tenancy: DB-per-Tenant vs Schema vs Shared
Architecting scalable multi-tenant SaaS backends with strict data isolation.
1. The 3 Multi-Tenant Patterns
| Pattern | Isolation Level | Resource Efficiency | Migration Complexity | Best For |
|---|---|---|---|---|
| Database-per-Tenant | Highest (Separate physical DBs) | Low (High memory/connection overhead) | High (Run migrations $N$ times) | Enterprise SaaS, Banking, Healthcare (HIPAA) |
| Schema-per-Tenant | Medium (Separate schemas inside 1 DB) | Medium | Medium (Search path switching) | Mid-market SaaS (100–1,000 tenants) |
| Shared Database + Shared Schema | Lowest (Enforced via tenant_id + RLS) |
Highest (All tenants pool connections & cache) | Lowest (1 migration runs once) | High-volume B2C or B2B SaaS (10,000+ tenants) |
2. Implementing Schema-per-Tenant with search_path
In PostgreSQL, each tenant has its own isolated schema:
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
-- When Acme logs in, set the connection search path:
SET search_path TO tenant_acme, public;
-- This query automatically queries tenant_acme.users:
SELECT * FROM users;
3. Shared Schema with tenant_id
CREATE TABLE orders (
tenant_id UUID NOT NULL,
order_id BIGSERIAL NOT NULL,
amount NUMERIC(12,2),
PRIMARY KEY (tenant_id, order_id)
);
Key Takeaways
- In shared schema models, every table must include
tenant_idas the leading column in primary and composite indexes.
In Part 184, we explore Row-Level Security (RLS) in Depth!