🏢 The 3 Ways to Build Multi-Tenant PostgreSQL (And Which One Won't Bankrupt You)

Building SaaS? You need to separate customer data. Here's the real story on multitenancy.

The Three Approaches:
Option 1: Shared Everything (Row-Level Security)

-- All customers in same tables
CREATE TABLE projects (
    id UUID DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name TEXT,
    data JSONB
);

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

-- Magic: Users only see their data
CREATE POLICY tenant_isolation ON projects
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- In your app
SET LOCAL app.current_tenant = '123e4567-e89b-12d3-a456';
SELECT * FROM projects; -- Only tenant's projects!

Cost: $20/month for unlimited tenants
Good for: B2C, freemium, thousands of small customers

Option 2: Schema Per Tenant

-- Each customer gets their own schema
CREATE SCHEMA tenant_spotify;
CREATE SCHEMA tenant_netflix;

-- Same tables in each schema
CREATE TABLE tenant_spotify.projects (...);
CREATE TABLE tenant_netflix.projects (...);

-- In your app
SET search_path TO tenant_spotify;
SELECT * FROM projects; -- Isolated by schema

Cost: $20-100/month (depends on number of schemas)
Good for: B2B, 10-500 enterprise customers

Option 3: Database Per Tenant

-- Each customer gets own database
CREATE DATABASE customer_001;
CREATE DATABASE customer_002;

-- Complete isolation
-- Connect to specific database per request

Cost: $20/month per database (gets expensive FAST)
Good for: Enterprise, compliance requirements, <50 customers

🎯 The Solo Dev Reality Check:
You're not Salesforce. Start with Option 1 (RLS).

Real numbers from production:


5,000 tenants with RLS: Works perfectly
500 schemas: Starting to get painful
50+ databases: Nightmare maintenance

Complete RLS Implementation:

-- 1. Add tenant_id to EVERYTHING
ALTER TABLE users ADD COLUMN tenant_id UUID;
ALTER TABLE projects ADD COLUMN tenant_id UUID;
ALTER TABLE invoices ADD COLUMN tenant_id UUID;

-- 2. Create helper function
CREATE OR REPLACE FUNCTION set_current_tenant(p_tenant_id UUID)
RETURNS void AS $$
BEGIN
    PERFORM set_config('app.current_tenant', p_tenant_id::TEXT, true);
END;
$$ LANGUAGE plpgsql;

-- 3. Row-level security policies
CREATE POLICY users_tenant_policy ON users
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

CREATE POLICY projects_tenant_policy ON projects
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- 4. Enforce for all queries
ALTER TABLE users FORCE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

-- 5. In your Node.js app
async function executeQuery(tenantId, query, params) {
    await db.query('SELECT set_current_tenant($1)', [tenantId]);
    return db.query(query, params);
}

// Now EVERY query is automatically filtered!
const projects = await executeQuery(
    tenantId,
    'SELECT * FROM projects'  // No WHERE clause needed!
);

Performance Impact:

-- Without RLS: 0.8ms
SELECT * FROM projects WHERE tenant_id = '123';

-- With RLS: 0.9ms
SET LOCAL app.current_tenant = '123';
SELECT * FROM projects;

-- 12% overhead for bulletproof isolation? Worth it.

Migration Path as You Grow:
Stage 1 (0-100 customers): RLS
Stage 2 (100-1000): RLS with read replicas
Stage 3 (1000+): Consider schema separation for biggest customers
Stage 4 (Enterprise): Dedicated databases for whales

Common Pitfalls:
❌ Forgetting tenant_id on new tables
Solution: Create a base migration template

❌ Cross-tenant queries failing
Solution: Use SECURITY DEFINER functions for admin queries

❌ Performance degradation
Solution: Always index tenant_id FIRST in compound indexes

Your Multi-tenant Questions:
How many tenants are you planning for? What's your isolation requirement?

Tomorrow: Time-series data without TimescaleDB!

#PostgreSQL #Multitenancy #SaaS #RowLevelSecurity #Architecture

@postgres