Case-Design-Data-SaaS-Multi-tenant

Case Study 02 — Data Layer cho SaaS Multi-tenant

“Design B2B SaaS với 500 enterprise + 50K self-serve tenants. Hybrid isolation, RLS, noisy neighbor mitigation, billing, per-tenant migrations.”

Tags: case-study saas multi-tenancy rls Liên quan: Tuan-16-ORM-CQRS-Multi-Tenancy · Tuan-Bonus-Multi-Tenancy-SaaS-Patterns (SD course)

---

1. Requirements

Functional

• B2B project management SaaS (Notion/Linear-like)
• Tenant = company workspace
• Per-tenant: users, projects, tasks, docs, comments
• Free tier: 10 users, 100 tasks
• Paid tier: unlimited
• Enterprise: dedicated infrastructure option

Non-functional

• 50K small tenants (avg 5 users, 1K tasks)
• 500 enterprise tenants (avg 200 users, 100K tasks)
• 10 mega-tenants (10K+ users, millions of tasks)
• Strict tenant data isolation
• Per-tenant compliance (some need HIPAA, SOX)
• Per-tenant SLA (enterprise 99.99%, free 99%)

---

2. Tenancy Strategy: Hybrid

graph TB
    subgraph "Pool A: Small tenants 50K"
        DBA[(Postgres pool 1<br/>RLS by tenant_id)]
        DBA2[(Postgres pool 2)]
        DBA3[(Postgres pool 3)]
    end

    subgraph "Pool B: Enterprise 500"
        DBB1[(Postgres - tenant Acme)]
        DBB2[(Postgres - tenant Globex)]
    end

    subgraph "Mega tenants 10"
        DBC[(Aurora cluster - tenant MegaCorp)]
    end

    LB[Tenant router]
    LB --> DBA
    LB --> DBA2
    LB --> DBA3
    LB --> DBB1
    LB --> DBB2
    LB --> DBC

    Catalog[(Tenant catalog)] -.routing info.-> LB

Tier	Strategy	Cost
Small (50K)	Shared pool with RLS, ~10K tenants per DB	$$
Enterprise (500)	Dedicated DB per tenant	$$$$
Mega (10)	Dedicated cluster per tenant	$$$$$

---

3. Tenant Catalog

-- In master DB (not in tenant DBs)
CREATE TABLE tenants (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug text UNIQUE NOT NULL,  -- 'acme', 'globex'
    name text NOT NULL,
    tier text CHECK (tier IN ('free', 'paid', 'enterprise', 'mega')),
    status text CHECK (status IN ('active', 'suspended', 'deleted')),
 
    -- Routing
    db_cluster text NOT NULL,  -- 'pool-1', 'dedicated-acme', etc
    db_host text,
    db_name text,
    schema_name text,
 
    -- Compliance
    region text NOT NULL DEFAULT 'us-east-1',
    compliance_flags jsonb DEFAULT '[]',  -- ['HIPAA', 'SOX']
    encryption_key_id text,
 
    -- Billing
    stripe_customer_id text,
    plan text,
    seats int,
 
    created_at timestamptz DEFAULT now(),
    upgraded_at timestamptz
);

App boots → load all tenants in memory → route requests.

Update via push (config change → restart) or LISTEN/NOTIFY for hot-reload.

---

4. Schema for Shared Pool (RLS)

-- Every table has tenant_id
CREATE TABLE projects (
    id bigserial,
    tenant_id bigint NOT NULL,
    name text NOT NULL,
    created_by bigint NOT NULL,
    created_at timestamptz DEFAULT now(),
    PRIMARY KEY (tenant_id, id)  -- composite, tenant first for partitioning + locality
);
 
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
 
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::bigint);
 
-- Same for tasks, comments, users, etc
CREATE TABLE tasks (
    id bigserial,
    tenant_id bigint NOT NULL,
    project_id bigint NOT NULL,
    title text NOT NULL,
    status text,
    assignee_id bigint,
    created_at timestamptz DEFAULT now(),
    PRIMARY KEY (tenant_id, id),
    FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id)
);
 
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);
CREATE INDEX idx_tasks_assignee ON tasks (tenant_id, assignee_id) WHERE status != 'done';

Key insight: composite key (tenant_id, id) keeps tenant data physically clustered → cache-friendly + can shard by tenant_id.

---

5. Request Lifecycle

sequenceDiagram
    Client->>API: HTTP request<br/>X-Tenant-Slug: acme
    API->>Catalog: lookup tenant by slug
    Catalog-->>API: tenant_id=42, db_pool='pool-1'
    API->>Pool1: acquire connection
    API->>DB: SET LOCAL app.current_tenant_id = 42
    API->>DB: SELECT * FROM tasks LIMIT 10
    DB-->>API: filtered by tenant_id (RLS)
    API-->>Client: response

Code:

async def handle_request(request):
    tenant = await get_tenant_from_request(request)
    if not tenant: return 404
    if tenant.status == 'suspended': return 403
 
    pool = get_pool(tenant.db_cluster)
    async with pool.acquire() as conn:
        async with conn.transaction():
            await conn.execute("SET LOCAL app.current_tenant_id = $1", str(tenant.id))
            return await handler(conn, request)

---

6. Tenant Migration (small → enterprise)

When tenant outgrows shared pool, migrate to dedicated:

sequenceDiagram
    Note over Tenant: Tenant grows to 5K users
    Admin->>Catalog: provision dedicated DB
    Note over Catalog: New DB ready
    Admin->>OldDB: snapshot tenant data
    OldDB-->>NewDB: copy data
    Note over Admin: Verify
    Admin->>Catalog: route tenant 'acme' to new DB
    Note over Tenant: Brief downtime ~30s
    Catalog-->>API: routing updated
    API->>NewDB: all subsequent requests
    Admin->>OldDB: delete tenant data (after grace)

Implementation:

async def migrate_tenant(tenant_slug):
    tenant = await catalog.get(tenant_slug)
    new_db = await provision_dedicated_db(tenant)
 
    # Set tenant read-only in catalog
    await catalog.update(tenant_slug, status='maintenance')
 
    # Wait for in-flight requests to finish
    await asyncio.sleep(5)
 
    # Export from old, import to new
    await pg_dump_tenant(tenant.id, old_db, new_db)
 
    # Update catalog routing
    await catalog.update(tenant_slug, db_cluster=new_db.cluster, db_host=new_db.host, status='active')
 
    # Cache invalidate
    await broadcast_catalog_refresh()
 
    # Wait grace period
    await asyncio.sleep(3600)
 
    # Delete from old DB
    await delete_tenant_from_pool(tenant.id, old_db)

---

7. Noisy Neighbor Mitigation

7.1 Resource quotas

-- Per-tenant role
CREATE ROLE tenant_role_42 IN ROLE app_user LOGIN;
ALTER ROLE tenant_role_42 SET statement_timeout = '5s';
ALTER ROLE tenant_role_42 CONNECTION LIMIT 50;

7.2 Connection pool per tier

pools = {
    'pool-1-free': ConnectionPool(min=2, max=20),
    'pool-1-paid': ConnectionPool(min=5, max=50),
    'pool-1-enterprise': ConnectionPool(min=10, max=100),
}

7.3 Rate limiting per tenant

Redis token bucket per tenant:

async def check_rate_limit(tenant_id, cost=1):
    return await redis.eval(rate_limiter_lua, 1,
        f"rl:{tenant_id}", now_ms, refill_rate, max_tokens, cost)

7.4 Query throttling

Long queries kill:

-- Monitor + kill
SELECT pid, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active' AND (now() - query_start) > '30s';
 
-- Auto kill in cron

7.5 Backup-pressure per tenant

Track DB usage per tenant (storage, queries, write volume):

CREATE TABLE tenant_usage (
    tenant_id bigint, date date,
    storage_mb int, query_count int, write_count int,
    PRIMARY KEY (tenant_id, date)
);

Bill / throttle by usage. Alert when tenant > 10x typical.

---

8. Per-tenant Compliance

8.1 Compliance flags

// In tenants.compliance_flags
["HIPAA"]
["SOX"]
["GDPR"]

8.2 HIPAA tenant specifics

• Dedicated encryption key (per-tenant KMS key)
• Audit log to immutable storage (Glacier with Object Lock)
• BAA signed
• Restricted admin access

async def encrypt_phi(tenant, value):
    key = await kms.get_data_key(tenant.encryption_key_id)
    return encrypt(value, key)

8.3 SOX tenant specifics

7-year retention. Backup to immutable storage.

8.4 GDPR

EU tenants → data residency in EU region.

-- Tenant catalog
region='eu-west-1'

→ Route to EU DB cluster.

---

9. Per-tenant Migrations

9.1 Shared pool — easy

One migration script, run once. RLS ensures isolation.

9.2 Dedicated DBs — coordination

500 dedicated DBs × monthly migration = 500 migration runs.

Tooling:

# Apply migration to all tenant DBs
for tenant in $(catalog.list_dedicated_tenants); do
    migrate -path migrations -database $tenant.db_url up
done

In CI/CD: parallel apply, retry failed individually.

Risk: partial state if some succeed, some fail. Idempotent migrations crucial.

9.3 Rolling out per-tenant

Feature flag per tenant:

INSERT INTO tenant_features (tenant_id, feature, enabled) VALUES (42, 'new_kanban', true);

Roll out 10% tenants → monitor → 50% → 100%.

---

10. Audit Log

10.1 Per-tenant audit

CREATE TABLE audit_log (
    id bigserial,
    tenant_id bigint NOT NULL,
    user_id bigint,
    action text,
    target_type text,
    target_id bigint,
    metadata jsonb,
    occurred_at timestamptz DEFAULT now(),
    PRIMARY KEY (tenant_id, id)
) PARTITION BY HASH (tenant_id);
 
-- 16 hash partitions
CREATE TABLE audit_log_p0 PARTITION OF audit_log FOR VALUES WITH (MODULUS 16, REMAINDER 0);
-- ... etc

Hash partitioning spreads writes evenly.

10.2 Compliance audit retention

-- Move old audit to cold storage (S3 Glacier)
COPY (SELECT * FROM audit_log WHERE occurred_at < '2024-01-01' AND tenant_id IN (SELECT id FROM tenants WHERE 'SOX' = ANY(compliance_flags)))
TO PROGRAM 'aws s3 cp - s3://audit-archive/sox-2023.gz' WITH (FORMAT CSV, HEADER);
 
-- Then delete from primary
DELETE FROM audit_log WHERE occurred_at < '2024-01-01';

---

11. Billing

CREATE TABLE usage_events (
    tenant_id bigint, user_id bigint,
    event_type text,  -- 'api_call', 'storage_gb_hour', 'seat_active'
    quantity numeric, occurred_at timestamptz
);
 
-- Aggregate hourly
CREATE MATERIALIZED VIEW usage_hourly AS
SELECT tenant_id, event_type, date_trunc('hour', occurred_at) AS hour, sum(quantity) AS total
FROM usage_events
GROUP BY tenant_id, event_type, date_trunc('hour', occurred_at);
 
-- Bill end of month
INSERT INTO invoices (tenant_id, month, line_items, total)
SELECT
    tenant_id,
    '2026-05',
    jsonb_agg(jsonb_build_object('type', event_type, 'qty', total, 'unit_price', unit_price)),
    sum(total * unit_price)
FROM usage_hourly h JOIN pricing p ON ...
GROUP BY tenant_id;

Push to Stripe for invoicing.

---

12. Backup & DR

12.1 Shared pool

Backup whole DB. Snapshot includes all tenants in that pool.

12.2 Dedicated tenants

Backup per-tenant. Allows per-tenant restore (compliance friendly — restore one tenant without affecting others).

12.3 Per-tenant restore drill

# Quarterly: pick a random tenant, restore to test cluster, verify
tenant=$(catalog.random_tenant)
pgbackrest restore --stanza=tenant-$tenant --pg1-path=/tmp/test-restore
psql -p 5433 -c "SELECT count(*) FROM projects;"

---

13. Search & Cache Per Tenant

13.1 ES index per large tenant

Enterprise tenants: dedicated ES index for speed.

indices:
  acme_projects, acme_tasks
  globex_projects, globex_tasks

Small tenants: shared index with filter:

indices:
  pool_a_tasks  (filter: tenant_id = ?)

13.2 Cache key namespace

SET tenant:42:user:1:profile {...}
SET tenant:43:user:1:profile {...}

Never share cache keys across tenants → prevents accidental leak.

---

14. Migration: From v0 monolith to v2 multi-tenant

graph LR
    V0[v0: single Postgres,<br/>no tenant_id] --> V1[v1: add tenant_id column,<br/>backfill from URL prefix]
    V1 --> V2[v2: enable RLS,<br/>add catalog]
    V2 --> V3[v3: partition by tenant,<br/>move enterprises to dedicated]

Each step incremental with feature flag.

---

15. Failure Modes

Failure	Impact	Mitigation
Shared pool DB down	10K tenants down	HA replica, fast failover
Dedicated tenant DB down	1 tenant down	Per-tenant HA
Catalog DB down	All routing fails	Catalog cached aggressive
RLS misconfigured	Cross-tenant leak	CI test enforces isolation
Wrong tenant_id in SET LOCAL	See other tenant’s data	App test asserts
Pool exhausted (noisy tenant)	All tenants in pool affected	Per-tenant quota

---

16. Observability

Per-tenant metrics:

• Active users
• Storage usage
• Query rate
• Error rate
• Latency

Top-tenant dashboard: identify problematic tenants.

-- Top 10 query-heavy tenants
SELECT tenant_id, count(*) AS queries
FROM pg_stat_statements_history  -- custom collected
WHERE collected_at > now() - interval '1 hour'
GROUP BY tenant_id ORDER BY queries DESC LIMIT 10;

---

17. Tiếp theo

Case-Design-Data-Realtime-Analytics

Cập nhật: 2026-05-16