Understanding Multi-Tenant Database Isolation

Introduction

A multi-tenant SaaS application serves multiple customers (tenants) from shared infrastructure. The critical challenge is tenant isolation: ensuring Customer A cannot accidentally or maliciously access Customer B’s data. How you implement this in the database layer has profound implications for security, cost, and operational complexity. There are three primary patterns, each with different tradeoffs.

flowchart LR
    P1["Pattern 1: shared DB, shared schema (tenant_id + RLS)"]
    P2["Pattern 2: shared DB, separate schemas"]
    P3["Pattern 3: separate database per tenant"]
    P1 -->|"more isolation, more cost"| P2
    P2 -->|"more isolation, more cost"| P3

Pattern 1: Shared Database, Shared Schema (Row-Level Security)

All tenants’ data lives in the same tables, distinguished by a tenant_id column:

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  customer_name TEXT NOT NULL,
  total_cents INT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- PostgreSQL Row-Level Security (RLS)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

-- Application sets tenant context at connection time
SET app.current_tenant_id = 'tenant-uuid-abc123';
SELECT * FROM orders;  -- Only returns rows for tenant-uuid-abc123

Pros: Cheapest, simplest operations, easy schema migrations (one schema to update) Cons: A missing WHERE clause exposes all tenants’ data (without RLS), regulatory compliance may require physical separation

Cost estimate: $100/month for 1,000 tenants

Pattern 2: Shared Database, Separate Schemas

Each tenant gets their own schema within one database:

-- Create schema per tenant
CREATE SCHEMA tenant_abc123;
CREATE TABLE tenant_abc123.orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_name TEXT NOT NULL,
  total_cents INT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Application sets search path to tenant schema
SET search_path = tenant_abc123;
SELECT * FROM orders;  -- Only accesses tenant_abc123.orders

-- Schema provisioning at tenant signup
CREATE OR REPLACE FUNCTION provision_tenant(tenant_slug TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE format('CREATE SCHEMA %I', tenant_slug);
  EXECUTE format('CREATE TABLE %I.orders (...)', tenant_slug);
  -- ... create all tables
END;
$$ LANGUAGE plpgsql;

Pros: Schema-level isolation (no cross-tenant leaks from search_path), still one database to manage, per-tenant backup possible Cons: Schema migrations require updating all tenant schemas (N migrations for N tenants), PostgreSQL has practical limits (~few hundred schemas before overhead)

Cost estimate: $200/month for 100 tenants,$500/month for 1,000 tenants (schema overhead)

Pattern 3: Separate Databases Per Tenant

Each tenant gets a dedicated database instance:

# Connection routing via database URL map
import asyncpg
from typing import Dict

class TenantConnectionPool:
    def __init__(self):
        self.pools: Dict[str, asyncpg.Pool] = {}
    
    async def get_connection(self, tenant_id: str) -> asyncpg.Connection:
        if tenant_id not in self.pools:
            # Fetch connection string from tenant registry
            db_url = await get_tenant_db_url(tenant_id)
            self.pools[tenant_id] = await asyncpg.create_pool(db_url)
        
        return await self.pools[tenant_id].acquire()

# Usage
pool = TenantConnectionPool()
async with await pool.get_connection("tenant-abc") as conn:
    orders = await conn.fetch("SELECT * FROM orders LIMIT 10")

Pros: Maximum isolation (physical separation satisfies HIPAA/SOC2/GDPR requirements), per-tenant performance tuning, simple schema migrations (one database at a time) Cons: Most expensive ($50-200/month per tenant), operational overhead (managing N databases), connection pool management complexity

Cost estimate: $50-200/month per tenant

Choosing the Right Pattern

Factor	Shared Schema	Separate Schema	Separate DB
Cost	$	$$	$$$
Compliance	Difficult	Partial	✅ Full isolation
Migration complexity	Simple	Medium (N migrations)	High (N DBs)
Max tenants	Millions	~1,000	~1,000 (managed)
Per-tenant backup	Hard	Possible	Easy

Most B2B SaaS platforms start with shared schema + RLS and migrate to separate databases for enterprise customers who have compliance requirements.

Key Takeaways

• Shared schema is the cheapest but requires PostgreSQL RLS or strict application-level WHERE tenant_id = on every query — a missing clause is a data breach.
• Separate schemas provide schema-level isolation without the cost of separate servers, but schema migrations must be applied to every tenant’s schema.
• Separate databases satisfy strict compliance requirements (HIPAA, PCI, GDPR data residency) but are expensive and operationally complex.
• Modern platforms use a tiered model: shared schema for SMB tenants, separate schema/DB for enterprise contracts.
• PostgreSQL Row-Level Security (RLS) with current_setting is the most production-proven pattern for shared-schema isolation at scale.