Database.Middle.How do you design a database for multi-tenant architecture?

Designing a multi-tenant database means creating a structure where multiple customers (tenants) share the same application and database (or parts of it), while keeping their data isolated and secure.

There are 3 main strategies — each with trade-offs in isolation, scalability, and complexity:

---

🏗️ 1. Database per Tenant

Each tenant has its own database instance.

✅ Pros:

• Strong isolation (security, performance)
• Easy to scale/shard per tenant
• No risk of data leaks

❌ Cons:

• Harder to manage (schema changes must be replicated)
• Expensive for thousands of tenants
• More infrastructure overhead

🧠 Use when:

• Tenants are large and need isolation (e.g., enterprise customers)

🏢 2. Schema per Tenant

All tenants share one database, but each gets its own schema.

nginxCopyEditDatabase
 ├── tenant_a.users
 ├── tenant_b.users
 └── ...

✅ Pros:

• Good data isolation
• One DB to manage
• Easier schema versioning than per-DB

❌ Cons:

• Can hit database limits (PostgreSQL: 32K schemas)
• Still complex to migrate or update all schemas
• Cross-tenant queries are harder

🧠 Use when:

• Medium number of tenants (~100s)
• Need per-tenant isolation without full DBs

🧩 3. Shared Schema (Row-Level Multi-tenancy)

All tenants share the same tables, tenant is identified by a tenant_id column.

sqlCopyEditCREATE TABLE users (
    id UUID PRIMARY KEY,
    tenant_id UUID NOT NULL,
    name TEXT,
    email TEXT,
    ...
);

Every query must filter by tenant_id.

✅ Pros:

• Best performance & scalability (millions of tenants)
• Easy to onboard new tenants
• One schema to migrate

❌ Cons:

• High risk of data leakage if tenant_id filters are missed
• Requires careful app-level isolation and security
• No per-tenant customization

🧠 Use when:

• You serve many small tenants (SaaS-style)
• You need cost efficiency and scale

---

🔒 Security and Isolation Best Practices

• ✅ Always filter by tenant_id in every query
• ✅ Use row-level security (e.g., PostgreSQL RLS)
• ✅ Add database constraints on tenant_id
• ✅ Enforce access rules in app middleware
• ✅ Use scoped ORM sessions (e.g., Hibernate filters)

📦 Multi-Tenant Design Patterns (Shared Schema)

A. Composite Unique Indexes:

UNIQUE (tenant_id, email)

B. Query Scoping:

SELECT * FROM users WHERE tenant_id = :tenantId

C. ORM Filtering (JPA/Hibernate):

@Filter(name = "tenantFilter", condition = "tenant_id = :tenantId")

📈 Bonus: Hybrid Models

Some SaaS apps use hybrid strategies, like:

• Shared schema for free/low-tier users
• Separate schemas or databases for premium users

---

✅ Summary Table

Strategy	Isolation	Scalability	Complexity	Best For
DB per tenant	⭐⭐⭐⭐	⭐⭐	⭐⭐⭐⭐	Large tenants, strict isolation
Schema per tenant	⭐⭐⭐	⭐⭐	⭐⭐⭐	Medium tenants, moderate scale
Shared schema	⭐	⭐⭐⭐⭐	⭐	Small tenants, high volume SaaS