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.