Multi-tenant PostgreSQL: row-level security vs schema-per-tenant & when to use which

If you're building a multi-tenant SaaS, this is the first real architecture

decision that will haunt you if you get it wrong.
I've implemented both approaches in production. Here's the honest trade-off.

Option A: Shared schema with row-level security (RLS)

Every tenant's data lives in the same tables. A tenant_id column on every
row. PostgreSQL RLS policies enforce that queries only ever return rows
belonging to the current tenant.

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: users only see their tenant's rows
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

# Set the tenant context before every query
async def set_tenant(conn, tenant_id: str):
    await conn.execute(
        "SELECT set_config('app.current_tenant_id', $1, true)",
        tenant_id
    )

Works well when: You have many small tenants. Hundreds or thousands.
Schema-per-tenant at that scale is unmanageable — migrations alone would take hours.

Breaks when: A noisy tenant runs heavy queries and degrades performance for others. You can't easily move one tenant's data to a separate DB. You need different retention policies per tenant.

Option B: Schema per tenant

Each tenant gets their own PostgreSQL schema — effectively a namespace.
tenant_abc.orders, tenant_xyz.orders. Same tables, different schema.

-- Create schema for a new tenant
CREATE SCHEMA tenant_abc;

-- Set search path at connection time
SET search_path TO tenant_abc, public;

# Alembic migration across all tenant schemas
from alembic import command
from alembic.config import Config

def migrate_all_tenants(tenant_schemas: list[str]):
    for schema in tenant_schemas:
        alembic_cfg = Config("alembic.ini")
        alembic_cfg.set_main_option("sqlalchemy.url", db_url)
        alembic_cfg.set_section_option("alembic", "version_table_schema", schema)
        command.upgrade(alembic_cfg, "head")

Works well when: You have fewer, larger tenants. Enterprise customers
who need data isolation guarantees, custom retention, or the ability to
export their entire dataset cleanly.
Breaks when: You have 500+ tenants. Running migrations across 500
schemas sequentially is slow. Connection pool overhead grows.

What I actually use
For most SaaS products at early stage: start with RLS. It's simpler to
operate, migrations are trivial, and you can always move to schema-per-tenant
for specific large customers later by routing them to a dedicated schema
or even a dedicated database.
The hybrid approach — RLS for SMB tenants, dedicated schema for enterprise —
is what I've settled on. Your connection string is the router.

def get_db_url(tenant: Tenant) -> str:
    if tenant.tier == "enterprise":
        return tenant.dedicated_db_url
    return f"{shared_db_url}?options=-csearch_path={tenant.schema}"

One thing nobody tells you: test your RLS policies with a superuser disabled.
PostgreSQL superusers bypass RLS by default. Your staging environment running
as a superuser will never catch a broken policy. Use a restricted role in tests.