Multi-Tenancy Patterns in 2026 — Schema, Row, Database, and the Tradeoffs

Multi-tenancy decisions compound. Pick the wrong isolation model, and migration to a stricter one is months of work. Pick too strict, and ops costs are 10× what they need to be. This post is the working tradeoff guide.

The three models

1. Shared DB, shared tables, tenant_id column

CREATE TABLE users (
    id bigserial PRIMARY KEY,
    tenant_id bigint NOT NULL,
    email text NOT NULL,
    UNIQUE (tenant_id, email)
);
CREATE INDEX ON users (tenant_id);

Every query: WHERE tenant_id = $1.

Pros: Cheapest. Easiest to develop. Most flexible. Cons: Bug → cross-tenant leak. No physical isolation.

2. Shared DB, schema per tenant

CREATE SCHEMA tenant_42;
CREATE TABLE tenant_42.users (...);

-- App connects with search_path
SET search_path TO tenant_42, public;

Each tenant: its own schema.

Pros: Stronger isolation. Easier per-tenant backup / migration. Cons: Schema migrations × N tenants. Connection pool complexity.

3. Database per tenant

Each tenant: its own DB instance or its own database within a cluster.

Pros: Strongest isolation. Full per-tenant control. Compliance-friendly. Cons: Most expensive. Operational complexity grows linearly.

When each fits

Most SaaS companies start with shared-with-tenant-id and never graduate.

Row-level security (RLS)

For shared-DB, RLS adds a safety net:

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON users
USING (tenant_id = current_setting('app.tenant_id')::bigint);

# Per-request: set the tenant_id
async with pool.acquire() as conn:
    await conn.execute("SET app.tenant_id = $1", tenant_id)
    rows = await conn.fetch("SELECT * FROM users")  # auto-filtered

Even if your app forgets WHERE tenant_id = $1, RLS prevents leak. Critical defense.

RLS gotchas

• Superuser bypasses RLS. Don’t connect as postgres / RDS master from the app.
• BYPASSRLS role attribute also bypasses. Avoid for app roles.
• FORCE ROW LEVEL SECURITY applies RLS even to the table owner.
• Test bypass scenarios in CI — write a test that queries as a different tenant and confirms 0 rows.

Query patterns

# Always scope tenant
async def list_users(tenant_id):
    return await db.fetch("SELECT * FROM users WHERE tenant_id = $1", tenant_id)

# Helper if you must use raw queries
class TenantDB:
    def __init__(self, db, tenant_id):
        self.db = db
        self.tenant_id = tenant_id
    
    async def fetch(self, sql, *args):
        # Prepend tenant filter; or rely on RLS
        return await self.db.fetch(sql, *args, self.tenant_id)

The discipline: tenant_id is part of every key. Composite primary keys in some cases.

Cross-tenant analytics

Sometimes you need cross-tenant data (admin dashboards, billing).

# Use a "system" connection that bypasses RLS
async with system_pool.acquire() as conn:
    total = await conn.fetchval("SELECT COUNT(*) FROM users")

Separate connection pool with elevated privileges, used only by admin paths. Audit every use.

Schema-per-tenant patterns

async def get_db_for_tenant(tenant_id):
    schema = f"tenant_{tenant_id}"
    pool = pools.get(tenant_id) or await create_pool(schema=schema)
    return pool

Cache pools per tenant; lazy-create.

Migrations:

async def migrate_all_tenants():
    schemas = await list_tenant_schemas()
    for schema in schemas:
        await alembic.upgrade(schema=schema)

100 tenants × migration time. Plan for it.

Per-tenant DB

async def get_db(tenant_id):
    creds = await secrets.get(f"tenant_db/{tenant_id}")
    return await asyncpg.create_pool(creds.dsn)

Each tenant’s connection string fetched at runtime.

For enterprise customers wanting their data in their region / cloud account: DB-per-tenant is sometimes the only option.

Tenant lookup

# Subdomain
acme.myapp.com → tenant 'acme'

# Path
myapp.com/acme/dashboard → tenant 'acme'

# Header
X-Tenant: acme

# JWT claim
{"tenant_id": "acme"}

JWT claim is most secure (signed, not user-controlled). Subdomain works for vanity URLs. Path is simplest for API.

Resource limits per tenant

ALTER ROLE tenant_42 SET statement_timeout = '5s';
ALTER ROLE tenant_42 SET work_mem = '32MB';

Per-tenant query limits, work memory. Prevents one tenant from starving others.

For shared-DB-with-RLS: harder. App-level rate limiting / query budgets needed.

Backup / restore per tenant

• Shared-DB: dump the whole DB; filtering on restore is messy. Logical replication for big-ticket tenants.
• Schema-per-tenant: pg_dump --schema=tenant_42 is clean.
• DB-per-tenant: standard pg_dump per DB.

For “customer wants their data exported”: schema-per-tenant or DB-per-tenant make this easy.

Migration strategies

Going from shared-DB → schema-per-tenant or DB-per-tenant is a project. Plan:

1. New writes go to new model.
2. Backfill existing tenants in batches.
3. Cutover read as each tenant catches up.
4. Drop old after validation.

Months of work for big systems. Choose model wisely up front.

Common mistakes

1. Forgot tenant_id in a query

Cross-tenant leak. RLS catches it; without RLS, you find out from the customer. Always RLS for shared-DB.

2. Application-level filtering only

Bug → leak. RLS is the safety net.

3. Schema-per-tenant + 10000 tenants

Postgres struggles with thousands of schemas (catalog bloat). Stay <1000.

4. DB-per-tenant for everyone

Each tenant pays for a DB instance even if they make 10 calls/day. Massive waste.

5. No tenant ID in logs

Debugging cross-tenant issues impossible. Always include tenant_id in structured logs.

What I’d ship today

For a new B2B SaaS:

• Shared DB + tenant_id + RLS.
• JWT-based tenant identification.
• Per-tenant rate limits at the API gateway.
• Audit log of every tenant_id-scoped operation.
• CI test for cross-tenant access.
• Schema-per-tenant if you have <100 enterprise customers needing strict isolation.
• DB-per-tenant only when a customer’s contract requires it.

Read this next

• Postgres Locking 2026
• Authentication 2026
• Postgres Performance Tuning 2026
• Database Migrations 2026

If you want my multi-tenant Postgres template (RLS + tests), it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .