PostgreSQL Schema-Per-Tenant: The Right Way to Build Multi-Tenant SaaS

Why Schema-Per-Tenant?

In multi-tenant SaaS applications, data isolation is paramount. After building several multi-tenant systems including CorgiCafe and a HoReCa time-tracking platform, I have found that PostgreSQL's schema-per-tenant architecture offers the best balance of isolation, performance, and operational simplicity.

The Three Approaches Compared

# 1. Shared Schema (tenant_id column)
# + Simple, no special middleware
# - Risk of data leaks if you forget WHERE tenant_id=X

# 2. Schema-Per-Tenant (this article)
# + True data isolation
# + Single database, single connection pool
# + Easy backup per tenant
# - Schema migrations run per tenant

# 3. Database-Per-Tenant
# + Maximum isolation
# - Connection pool explosion
# - Operational nightmare at scale

Creating Tenant Schemas

When a new tenant signs up, create their schema and tables:

-- Create schema
CREATE SCHEMA IF NOT EXISTS tenant_acme;
SET search_path TO tenant_acme;

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    role VARCHAR(20) DEFAULT 'member',
    hourly_rate DECIMAL(8,2) DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE work_logs (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id) ON DELETE CASCADE,
    check_in TIMESTAMP WITH TIME ZONE NOT NULL,
    check_out TIMESTAMP WITH TIME ZONE,
    notes TEXT DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_work_logs_employee ON work_logs(employee_id);
CREATE INDEX idx_work_logs_checkin ON work_logs(check_in);

Django Middleware for Schema Routing

from django.db import connection

class TenantMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        tenant = self.get_tenant(request)
        if tenant:
            schema_name = f'tenant_{tenant.slug}'
            with connection.cursor() as cursor:
                cursor.execute(
                    "SET search_path TO %s, public", [schema_name]
                )
            request.tenant = tenant

        response = self.get_response(request)

        with connection.cursor() as cursor:
            cursor.execute("SET search_path TO public")
        return response

    def get_tenant(self, request):
        host = request.get_host().split(':')[0]
        subdomain = host.split('.')[0]
        try:
            return Tenant.objects.get(subdomain=subdomain, is_active=True)
        except Tenant.DoesNotExist:
            return None

Flask Version

from flask import g, request
from sqlalchemy import text

@app.before_request
def set_tenant():
    subdomain = request.host.split('.')[0]
    tenant = Tenant.query.filter_by(
        subdomain=subdomain, is_active=True
    ).first()
    if tenant:
        g.tenant = tenant
        db.session.execute(
            text(f"SET search_path TO tenant_{tenant.slug}, public")
        )

Running Migrations Per Tenant

from django.core.management.base import BaseCommand
from django.db import connection

class Command(BaseCommand):
    help = 'Run migrations for all tenant schemas'

    def handle(self, *args, **options):
        tenants = Tenant.objects.filter(is_active=True)
        for tenant in tenants:
            schema = f'tenant_{tenant.slug}'
            self.stdout.write(f'Migrating {schema}...')
            with connection.cursor() as cursor:
                cursor.execute("SET search_path TO %s", [schema])
            call_command('migrate', schema=schema)

        self.stdout.write(
            self.style.SUCCESS(f'Migrated {tenants.count()} tenants')
        )

Performance Considerations

• Connection pooling — SET search_path does not open new connections. Use PgBouncer for 50+ tenants.
• Indexes are per-schema — each tenant gets independent indexes, which is actually good for performance.
• Backup granularity — you can dump individual schemas: pg_dump --schema=tenant_acme
• Monitoring — track schema sizes with pg_total_relation_size queries

When NOT to Use Schema-Per-Tenant

• 1000+ tenants — too many schemas becomes unwieldy. Switch to shared schema with tenant_id.
• Cross-tenant queries — if you need aggregate analytics across all tenants, schema isolation makes this harder.
• Rapid tenant creation — creating schemas and running migrations takes seconds, not milliseconds.

For most B2B SaaS products with 5-500 tenants, schema-per-tenant is the sweet spot. Read about how I applied this pattern in practice for CorgiCafe's 5-location time tracker, or reach out if you are planning a multi-tenant system.