Building SaaS with PostgreSQL - Multi-Tenancy Patterns Compared

-- Each tenant has their own database
-- Connection string per tenant:
-- postgresql://user:pass@host:5432/tenant_acme
-- postgresql://user:pass@host:5432/tenant_globex

-- Within tenant_acme database:
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    name TEXT
);

-- No tenant_id needed - isolation is physical

#!/bin/bash
# migrate_all.sh - Run migrations across all tenant databases

TENANTS=$(psql -h localhost -U admin -d master -t -c \
  "SELECT database_name FROM tenants WHERE active = true")

for tenant_db in $TENANTS; do
  echo "Migrating $tenant_db..."
  psql -h localhost -U admin -d "$tenant_db" -f migration_001.sql

  if [ $? -ne 0 ]; then
    echo "FAILED: $tenant_db"
    exit 1
  fi
done

-- Create schema for new tenant
CREATE SCHEMA tenant_acme;

-- Create tables within the schema
CREATE TABLE tenant_acme.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    name TEXT
);

CREATE TABLE tenant_acme.orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES tenant_acme.users(id),
    total NUMERIC(10,2)
);

-- Switch context with search_path
SET search_path TO tenant_acme, public;

-- Now queries automatically use tenant_acme schema
SELECT * FROM users; -- Reads from tenant_acme.users

-- At the start of each request, set the tenant context
-- Option 1: SET search_path
SET search_path TO tenant_acme, public;

-- Option 2: Use fully qualified names
SELECT * FROM tenant_acme.users WHERE id = $1;

-- Option 3: Set via connection parameter
-- postgresql://user:pass@host/saas_app?options=-c%20search_path%3Dtenant_acme

-- Check catalog size
SELECT
    nspname AS schema_name,
    COUNT(*) AS table_count
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname LIKE 'tenant_%'
GROUP BY nspname
ORDER BY table_count DESC;

-- Monitor catalog table sizes
SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('pg_class', 'pg_attribute', 'pg_index', 'pg_constraint')
ORDER BY pg_relation_size(oid) DESC;

-- migration_001.sql
-- Add column to all tenant schemas

DO $$
DECLARE
    schema_name TEXT;
BEGIN
    FOR schema_name IN
        SELECT nspname FROM pg_namespace
        WHERE nspname LIKE 'tenant_%'
    LOOP
        EXECUTE format(
            'ALTER TABLE %I.users ADD COLUMN IF NOT EXISTS avatar_url TEXT',
            schema_name
        );
    END LOOP;
END $$;

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    email TEXT NOT NULL,
    name TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),

    UNIQUE(tenant_id, email)
);

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    user_id UUID NOT NULL,
    total NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Critical: Index for tenant-scoped queries
CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE INDEX idx_orders_tenant ON orders(tenant_id);
CREATE INDEX idx_orders_tenant_user ON orders(tenant_id, user_id);

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy ensures queries are always tenant-scoped
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Now even if code forgets the filter, RLS enforces it
SET app.current_tenant = 'tenant-uuid-here';
SELECT * FROM orders WHERE total > 1000;
-- Only returns orders for the current tenant

-- Composite index for common query pattern
CREATE INDEX idx_orders_tenant_created
    ON orders(tenant_id, created_at DESC);

-- Query plan shows efficient index scan
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE tenant_id = 'abc-123'
ORDER BY created_at DESC
LIMIT 20;

-- Index Scan using idx_orders_tenant_created on orders
--   Index Cond: (tenant_id = 'abc-123'::uuid)
--   Rows Removed by Filter: 0
--   Planning Time: 0.1ms
--   Execution Time: 0.5ms

-- Revenue by tenant (easy!)
SELECT
    t.name AS tenant_name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS revenue
FROM tenants t
LEFT JOIN orders o ON o.tenant_id = t.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY t.id, t.name
ORDER BY revenue DESC;

-- Active users across all tenants
SELECT
    DATE_TRUNC('day', last_login) AS day,
    COUNT(DISTINCT id) AS active_users
FROM users
WHERE last_login >= NOW() - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1;

                    ┌─────────────────────────┐
                    │  How many tenants do    │
                    │  you expect at scale?   │
                    └───────────┬─────────────┘
                                │
            ┌───────────────────┼───────────────────┐
            │                   │                   │
            ▼                   ▼                   ▼
       < 500              500 - 10,000          > 10,000
            │                   │                   │
            ▼                   ▼                   ▼
   ┌────────────────┐  ┌────────────────┐  ┌────────────────┐
   │ Do enterprise  │  │ Need physical  │  │ Shared Tables  │
   │ customers need │  │ backup per     │  │ + RLS          │
   │ isolation?     │  │ tenant?        │  │                │
   └───────┬────────┘  └───────┬────────┘  └────────────────┘
           │                   │
     ┌─────┴─────┐       ┌─────┴─────┐
     │           │       │           │
     ▼           ▼       ▼           ▼
    Yes         No      Yes         No
     │           │       │           │
     ▼           ▼       ▼           ▼
  Database    Schema   Schema    Shared
  per-tenant  per-     per-      Tables
              tenant   tenant    + RLS

-- Tenants table tracks isolation level
CREATE TABLE tenants (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    plan TEXT NOT NULL, -- 'starter', 'pro', 'enterprise'
    isolation_level TEXT NOT NULL DEFAULT 'shared',
    -- 'shared' = shared tables
    -- 'schema' = dedicated schema
    -- 'database' = dedicated database
    dedicated_db_name TEXT, -- Only for 'database' level
    dedicated_schema TEXT   -- Only for 'schema' level
);

def get_connection(tenant_id: str):
    tenant = get_tenant(tenant_id)

    if tenant.isolation_level == 'database':
        return get_dedicated_db_connection(tenant.dedicated_db_name)
    elif tenant.isolation_level == 'schema':
        conn = get_shared_db_connection()
        conn.execute(f"SET search_path TO {tenant.dedicated_schema}, public")
        return conn
    else:
        conn = get_shared_db_connection()
        conn.execute(f"SET app.current_tenant = '{tenant_id}'")
        return conn