Building Multi-Tenant SaaS Databases: Isolation, Performance, and PostgreSQL Strategies

I've burned myself on multi-tenancy decisions twice—once choosing wrong at the start, once changing my mind halfway through CitizenApp. Both were expensive. The database architecture you pick shapes your API contracts, your security model, and eventually your scaling ceiling. Get it wrong and you'll either leak data between customers or run out of database connections at 50 customers.

There are three main approaches: shared database with row-level security (RLS), separate schemas per tenant, or separate databases entirely. I'll explain why I settled on shared database + RLS + application-level tenant context for CitizenApp, and when you'd choose differently.

Why Most Teams Choose Wrong Initially

The appeal of "one database, one schema, multiple customers" is obvious: simple deployment, easy backups, cheap. But RLS is misunderstood. People think "just add a WHERE clause" solves multi-tenancy. It doesn't.

The real cost isn't implementation—it's cognitive overhead and the blast radius when you mess up. One misconfigured policy, one rogue query without tenant context, and you've silently leaked customer data. I prefer explicit isolation at the application layer with RLS as a failsafe, not the primary mechanism.

The Shared Database + RLS Approach (What I Use Now)

Here's the mental model: your application is responsible for tenant context. PostgreSQL's RLS is the safety net.

-- Schema setup
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    email TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(tenant_id, email)
);

CREATE TABLE workspaces (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE workspaces ENABLE ROW LEVEL SECURITY;

-- The critical policy: tenant context via JWT
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.tenant_id')::UUID);

CREATE POLICY tenant_isolation ON workspaces
    USING (tenant_id = current_setting('app.tenant_id')::UUID);

The current_setting('app.tenant_id') is your tenant context. You set it per request in your application:

// FastAPI middleware (Python)
from fastapi import Request, HTTPException
from sqlalchemy import text
import jwt

@app.middleware("http")
async def set_tenant_context(request: Request, call_next):
    # Extract tenant from JWT
    auth_header = request.headers.get("Authorization")
    if not auth_header:
        raise HTTPException(status_code=401)

    token = auth_header.split(" ")[1]
    payload = jwt.decode(token, SECRET_KEY, algorithms=["HS256"])
    tenant_id = payload.get("tenant_id")

    if not tenant_id:
        raise HTTPException(status_code=403, detail="No tenant in token")

    # Set PostgreSQL context variable
    async with db.begin() as conn:
        await conn.execute(
            text("SET app.tenant_id = :tenant_id"),
            {"tenant_id": str(tenant_id)}
        )

    response = await call_next(request)
    return response

Now any query automatically filters by tenant:

# SQLAlchemy query
from sqlalchemy import select

stmt = select(User).where(User.email == "[e-mail]")
# RLS automatically adds: AND tenant_id = current_setting('app.tenant_id')::UUID
result = await db.execute(stmt)

Why this works: Your application controls the JWT payload, so you control which tenant context gets set. If a developer forgets to pass the token, RLS blocks the query. If someone SQL-injects a tenant_id, the RLS policy still enforces isolation.

Schema-Per-Tenant When You Need It

I wouldn't use this for CitizenApp at 9 features. But for highly regulated customers (healthcare, finance) who demand complete isolation, it's worth it:

-- Dynamic schema creation
CREATE SCHEMA AUTHORIZATION tenant_${tenant_id};

-- Tenant-specific tables
CREATE TABLE tenant_${tenant_id}.users (
    id UUID PRIMARY KEY,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

Trade-offs: You get bulletproof isolation. Backups are simpler (one schema = one customer's data). But now you're managing schema migrations across N schemas. Connection pooling gets weird. Your queries need dynamic schema prefixing. I'd only pick this if:

1. You have <50 tenants and they're high-value
2. Data residency laws demand it (GDPR, data sovereignty)
3. Your customers audit their own databases

For everyone else, it's overkill that creates operational friction.

Performance Doesn't Care About Tenancy (Usually)

Here's what surprised me: the bottleneck isn't tenant isolation, it's working set size. If your shared database has 100GB of data across 1,000 tenants, queries are slower than if you had 100MB per tenant in separate databases.

This matters for analytics queries. If a tenant runs a "sum all invoices" query, it'll scan fewer rows with schema separation. But for transactional queries (OLTP), modern PostgreSQL with proper indexes doesn't care if you have 1 tenant or 1,000 in the same table.

-- Proper indexing makes RLS queries fast
CREATE INDEX idx_workspaces_tenant_id ON workspaces(tenant_id);
CREATE INDEX idx_workspaces_tenant_created ON workspaces(tenant_id, created_at DESC);

-- Query planner will use tenant_id index first

I monitor this in CitizenApp. Tenant isolation adds <1ms to query time with proper indexes.

Gotcha: RLS Doesn't Work With Prepared Statements Everywhere

This one burned me. Some PostgreSQL drivers cache prepared statements before the tenant context is set:

// DANGEROUS - prepared statement might be cached without context
const query = db.prepare("SELECT * FROM users WHERE id = $1");
query.run(userId); // Might not have tenant_id context!

I solved it by disabling statement caching for sensitive queries:

# SQLAlchemy - don't cache sensitive queries
from sqlalchemy import text

stmt = text("SELECT * FROM users WHERE id = :id").execution_options(
    compiled_cache=None
)
result = await db.execute(stmt, {"id": user_id})

Or use ORM queries, which don't suffer from this:

# SQLAlchemy ORM - RLS applies consistently
user = await db.get(User, user_id)

The Real Decision: What's Your Risk?

Use shared database + RLS if:

• You have <10,000 tenants
• Data leakage is recoverable (you'll catch it in logs)
• You want deployment simplicity

Use schema-per-tenant if:

• You have <100 high-value tenants
• Data leakage is a regulatory violation
• Your customers demand audit trails per schema

Use separate databases if:

• You're a platform (Stripe, Vercel) with thousands of autonomous customers
• You need per-tenant backups and recovery
• You accept operational complexity as the cost of isolation

I chose shared database + RLS because CitizenApp's security model relies on application authentication anyway. Adding schema separation would've delayed launch and created deployment headaches I didn't need. But if I'd been building a regulated product, I'd schema-separate from day one.

Pick the simplest approach that your risk tolerance allows. You can always migrate later (I did).