Multi-Tenant Architecture: The Decisions You Can't Undo

The Decision That Shapes Everything

If you're building a SaaS product, the multi-tenancy decision is the most consequential architectural choice you'll make. Get it right, and scaling is straightforward. Get it wrong, and you'll spend years migrating — or hit a ceiling you can't break through.

The Three Models

Model 1: Shared Database, Shared Schema

All tenants in one database, one set of tables.
Every row has a tenant_id column.

  Table: users
  ┌─────────┬───────────┬──────────────┐
  │ tenant_id │ user_id  │ name         │
  ├─────────┼───────────┼──────────────┤
  │ acme     │ usr_001  │ Alice        │
  │ acme     │ usr_002  │ Bob          │
  │ globex   │ usr_003  │ Charlie      │
  │ globex   │ usr_004  │ Diana        │
  └─────────┴───────────┴──────────────┘

Pros:
  ✓ Simple to implement and maintain
  ✓ Efficient resource utilization
  ✓ Easy to deploy updates (one database)
  ✓ Cross-tenant analytics is trivial

Cons:
  ✗ One bad query affects ALL tenants (noisy neighbor)
  ✗ Data isolation depends on app-level WHERE clauses
  ✗ One missing tenant_id filter = data leak
  ✗ Hard to comply with data residency requirements
  ✗ Backup/restore is all-or-nothing

Model 2: Shared Database, Separate Schemas

One database, but each tenant gets their own schema (namespace).

  Schema: acme
    Table: users → Alice, Bob

  Schema: globex
    Table: users → Charlie, Diana

Pros:
  ✓ Better logical isolation than shared schema
  ✓ Per-tenant backup/restore possible
  ✓ Easier to reason about data boundaries
  ✓ Can customize schema per tenant if needed

Cons:
  ✗ Schema migrations must run N times (once per tenant)
  ✗ Connection pooling is more complex
  ✗ Still shares compute resources (noisy neighbor possible)
  ✗ Doesn't solve data residency (same physical database)
  ✗ Operational complexity grows linearly with tenants

Model 3: Database Per Tenant

Each tenant gets their own database instance.

  Database: acme-db
    Table: users → Alice, Bob

  Database: globex-db
    Table: users → Charlie, Diana

Pros:
  ✓ Complete data isolation (strongest security)
  ✓ No noisy neighbor problem
  ✓ Per-tenant performance tuning
  ✓ Easy data residency compliance (different regions)
  ✓ Simple backup/restore per tenant

Cons:
  ✗ Expensive (database per tenant × hundreds of tenants)
  ✗ Complex operations (schema migrations across N databases)
  ✗ Connection management is hard at scale
  ✗ Cross-tenant analytics requires aggregation layer
  ✗ Provisioning new tenants is slower

The Decision Framework

Choose shared schema when:
  ✓ You have hundreds or thousands of small tenants
  ✓ Data isolation requirements are standard (not regulated)
  ✓ You need to move fast with a small team
  ✓ Cross-tenant features are important (marketplace, benchmarks)
  ✓ Cost efficiency matters more than isolation

  Examples: Project management tools, CRM for SMBs,
  e-commerce platforms

Choose separate schemas when:
  ✓ You have dozens to hundreds of mid-size tenants
  ✓ Tenants need some customization
  ✓ You want logical isolation without full database separation
  ✓ Per-tenant backup/restore is required
  ✓ You're on PostgreSQL (great schema support)

  Examples: Multi-brand e-commerce, agency platforms

Choose database-per-tenant when:
  ✓ You have fewer, larger tenants (enterprise)
  ✓ Regulatory compliance requires physical data isolation
  ✓ Data residency laws require geographic separation
  ✓ Tenants have wildly different performance profiles
  ✓ Security incidents must be contained to one tenant

  Examples: Healthcare SaaS, financial services, government

The Shared Schema Playbook

If you choose the most common model (shared database, shared schema), here's how to not get burned:

Mandatory: Row-Level Security

-- PostgreSQL Row-Level Security (defense in depth)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.tenant_id'));
 
-- Set tenant context per request
SET app.tenant_id = 'acme';
SELECT * FROM users; -- Only returns acme's users
-- Even if app code forgets WHERE tenant_id = ...

Mandatory: Tenant-Scoped Queries

// Every database query MUST include tenant_id
// Use a base repository that enforces this
 
class TenantScopedRepository<T> {
  constructor(private tenantId: string) {}
 
  async findAll(filters: Partial<T>): Promise<T[]> {
    return db.query({
      ...filters,
      tenant_id: this.tenantId, // Always included, can't be forgotten
    });
  }
 
  async create(data: Omit<T, "tenant_id">): Promise<T> {
    return db.insert({
      ...data,
      tenant_id: this.tenantId, // Always set automatically
    });
  }
}
 
// Usage — tenant_id is always there
const users = new TenantScopedRepository<User>(req.tenantId);
await users.findAll({ role: "admin" });
// SQL: SELECT * FROM users WHERE role = 'admin' AND tenant_id = 'acme'

Mandatory: Tenant-Aware Indexes

-- Every index should include tenant_id for query performance
CREATE INDEX idx_users_tenant_email
  ON users (tenant_id, email);
 
CREATE INDEX idx_orders_tenant_created
  ON orders (tenant_id, created_at DESC);
 
-- Without tenant_id in the index, queries scan ALL tenants'
-- data to find one tenant's records. At scale, this kills
-- performance for everyone.

The Noisy Neighbor Problem

Tenant A: Normal usage, 100 queries/minute
Tenant B: Running a huge export, 50,000 queries/minute
Result: Tenant A's response time goes from 50ms to 2,000ms

Solutions (layer them):

1. Connection pooling per tenant
   Each tenant gets max 10 connections (can't monopolize)

2. Query timeouts
   Queries > 5 seconds are killed automatically

3. Rate limiting at the API level
   Max 1,000 requests/minute per tenant

4. Read replicas for heavy queries
   Reports/exports go to replica, not primary

Migration Between Models

Starting with shared schema and need to move to isolation?

Phase 1: Add tenant_id everywhere (if not already there)
Phase 2: Add row-level security as defense in depth
Phase 3: Build tenant routing layer in the application
Phase 4: For specific tenants that need isolation:
  - Create dedicated database
  - Migrate their data
  - Route their requests to dedicated database
  - Keep everyone else on shared

This hybrid approach lets you offer "dedicated" as a
premium tier without migrating everyone.

The Checklist

Before going to production with multi-tenancy:

□ Every table has tenant_id column (except truly global tables)
□ Every query is tenant-scoped (enforced at repository level)
□ Row-level security enabled as defense in depth
□ All indexes include tenant_id
□ Connection pooling limits per tenant configured
□ Query timeouts set
□ API rate limiting per tenant
□ Tenant provisioning is automated
□ Tenant data deletion is automated (for offboarding)
□ Backup strategy accounts for per-tenant restore needs
□ Monitoring shows per-tenant query performance
□ Load testing includes noisy neighbor scenarios

Multi-tenant architecture is a decision that's easy to make and expensive to change. Start with the simplest model that meets your security and compliance requirements. Add isolation later — for the tenants that need it — rather than over-engineering isolation you'll never use.