Multi-Tenant Database Design: Schema, Row-Level, or Per-Tenant DB?

The Three Strategies

Strategy 1: Shared Schema with tenant_id

Every table has tenant_id as the first column of every composite index:

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    title TEXT NOT NULL,
    body TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_docs_tenant_created ON documents (tenant_id, created_at DESC); CREATE INDEX idx_docs_tenant_id ON documents (tenant_id, id); ```

Enforce isolation with Row-Level Security (RLS):

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- App sets tenant per session SET app.tenant_id = '550e8400-e29b-41d4-a716-446655440000'; ```

Strategy 2: Schema-Per-Tenant

CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.documents (id BIGSERIAL, ...);

-- Switch context per request SET search_path TO tenant_acme; ```

Pros: clean separation, easy backups per tenant. Cons: schema migrations × N tenants — fast but operationally heavier.

Strategy 3: DB-Per-Tenant

One PostgreSQL database per tenant. Best isolation, worst operational overhead.

def db_for_tenant(tenant_id: str) -> AsyncConnection:
    config = TENANT_DBS[tenant_id]
    return asyncpg.connect(
        host=config.host, database=f"tenant_{tenant_id}", ...
    )

• Regulatory mandate (HIPAA covered entity per tenant)
• Tenant size varies wildly (one tenant 1000× others)
• Customers demand it contractually

Migration Paths

The good news: shared schema → schema-per-tenant → DB-per-tenant is a one-way migration that gets harder, but is always possible:

# Pump tenant out of shared schema into new schema
async def extract_tenant(tenant_id: str):
    async with shared_db.transaction():
        for table in TENANT_TABLES:
            await shared_db.execute(f"""
                INSERT INTO tenant_{tenant_id}.{table}
                SELECT * FROM public.{table} WHERE tenant_id = $1
            """, tenant_id)
    await shared_db.execute("UPDATE tenants SET schema = $1 WHERE id = $2",
                             f"tenant_{tenant_id}", tenant_id)

Common Mistakes

1. 1Forgetting tenant_id in indexes — Queries that scan all tenants kill performance
2. 2Trusting application-only filtering — Use RLS; the DB is your last line of defense
3. 3No tenant_id in cache keys — Catastrophic data leak waiting to happen
4. 4Premature isolation — Don't start with DB-per-tenant. You'll pay 10× more in ops

Start with shared schema + RLS. Migrate individual tenants out only when needed.