Module 4: Multi-Tenancy in PostgreSQL

Three multi-tenancy models

Capsule overview

Multi-tenancy in PostgreSQL isn't a technical feature. It's an architectural decision between three options, each optimal in a different range of "number of tenants" and "isolation requirement." If you choose it without understanding that range, you end up with the wrong option — and refactoring later costs months, not days.

This capsule teaches you to make the decision with quantitative criteria. You're going to understand the three models (shared schema with tenant_id, RLS over a shared schema, schema-per-tenant), their real trade-off (not the "it depends" version), a decision matrix with concrete numbers (how many tenants before each model stops scaling), and the signals that indicate when to migrate from one model to another. You're not going to write code in this capsule — you're going to make decisions. The implementation arrives in capsules 03 through 06.

By the end you'll be clear on: why a simple shared schema wins in consumer SaaS with millions of small accounts, why RLS is the default answer for B2B SaaS with 1k-100k customers, and why schema-per-tenant only makes sense for enterprise with <1k tenants and explicit isolation SLAs. That clarity is the foundation the next six capsules are built on.

Mental model: isolation as a spectrum, not a binary

Before presenting the three models, you need a framing. Multi-tenancy isn't "data together vs data separate." It's a spectrum between two extremes:

The "maximum sharing" extreme: a single PostgreSQL instance, a single DB, a single schema, a single table per entity, all tenants sharing rows in the same table. Cheap operationally, risky if query discipline fails.
The "maximum isolation" extreme: one PostgreSQL instance per tenant, with its own DB, its own schemas, its own users. It's impossible for one tenant to read another's data (they don't even share the connection). Expensive operationally, almost nobody really needs it.

The three models in this capsule sit at different points on the spectrum:

MAXIMUM SHARING  ←————————————————————————————————→  MAXIMUM ISOLATION

   Shared schema      →    Shared schema      →    Schema-per-tenant
   with tenant_id          with RLS                (same cluster, different schemas)
   (rows mixed)            (rows mixed
                            + a policy filters)

Beyond schema-per-tenant there's DB-per-tenant (one DB per tenant in the same cluster) and cluster-per-tenant (one PostgreSQL server per tenant). Both are out of this guide's scope because their usage niche is very specific (banking regulation, governments), but know they exist.

The question each architecture answers is: how much isolation do you need and how much operational cost can you pay? There's no universal answer. There are optimal zones according to scale and requirements.

Model 1: Shared schema with `tenant_id`

It's the simplest model. A single DB, a single schema, and the domain tables have a tenant_id column that identifies the row's owner. Every query in the application filters by tenant_id.

-- A typical schema
CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL REFERENCES tenants(id),
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'open',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- A composite index: almost every query starts with tenant_id
CREATE INDEX idx_tasks_tenant_created
    ON tasks (tenant_id, created_at DESC);

Every query in the app has to filter:

# app/api/tasks.py
@router.get("/tasks")
async def list_tasks(
    db: AsyncSession = Depends(get_session),
    current_tenant: Tenant = Depends(get_current_tenant),
):
    result = await db.execute(
        select(Task)
        .where(Task.tenant_id == current_tenant.id)  # CRITICAL: never forget it
        .order_by(Task.created_at.desc())
        .limit(50)
    )
    return result.scalars().all()

When it wins

Consumer SaaS with millions of small accounts. Spotify, Notion, Linear — products with millions of individual "tenants" or small teams. Each account has few rows. Partitioning by tenant is unnecessary overhead; maintaining N schemas is impossible at that scale.
Products where cross-tenant queries are frequent and desirable. Global "usage ranking"-style dashboards, aggregated analytics, internal metrics. With a shared schema, a query like SELECT COUNT(*) FROM tasks WHERE created_at > NOW() - INTERVAL '24 hours' is trivial. With schema-per-tenant, you have to iterate over every schema.
Products in a very early phase where refactoring to another model is cheap. An MVP with 5 customers: don't invest in RLS yet if there's no explicit requirement. When you reach 50 customers, migrate to RLS (it's relatively easy to add later).

When it loses

When query discipline is hard to guarantee. If your team grows, inevitably someone is going to forget the WHERE tenant_id = ... in a new query. With no DB-level protection, that lapse becomes a breach.
When a customer asks for documentable isolation. Enterprise buyers want a "DB guarantee," not a "code promise." A simple shared schema doesn't give it.
When you need to migrate a tenant to another DB. Moving a tenant to a dedicated cluster means running WHERE tenant_id = X queries for each table, exporting rows, importing. It's feasible but tedious. In schema-per-tenant it's a pg_dump of the entire schema.

Operational cost

Almost zero. A single DB, a single schema, trivial migrations with standard Alembic. The operational complexity is discipline in code review: every PR has to verify the new queries filter by tenant_id. This can be automated with lint rules, but it requires infrastructure.

Model 2: Shared schema with RLS

The same schema as model 1 (tables with tenant_id), but PostgreSQL applies an automatic policy: every query, no matter who writes it, gets filtered by the current request's tenant_id. If the app forgets the WHERE tenant_id = ..., PostgreSQL adds it implicitly.

-- The base schema is the same as model 1
CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL REFERENCES tenants(id),
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'open',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Turn on Row-Level Security
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks FORCE ROW LEVEL SECURITY;  -- applies to the owner too

-- The policy: every query only sees rows where tenant_id matches the context
CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

The app, before every transaction, sets the context:

# app/db/tenant_context.py
async def tenant_context(
    db: AsyncSession = Depends(get_session),
    current_tenant: Tenant = Depends(get_current_tenant),
) -> AsyncSession:
    await db.execute(
        text("SET LOCAL app.tenant_id = :tid"),
        {"tid": str(current_tenant.id)},
    )
    return db

After that the original query (with or without a WHERE tenant_id) is protected:

# Even if you forget the .where(Task.tenant_id == ...), RLS filters automatically
result = await db.execute(select(Task).order_by(Task.created_at.desc()))

Capsule 04 covers RLS's fundamentals and 05 the complete implementation with all the gotchas.

When it wins

B2B SaaS with 1k-100k tenants. The sweet spot. You have enough customers that manual query discipline is risky, but not so many that schema-per-tenant becomes unmanageable. RLS gives you a DB-level guarantee without the operational overhead of N schemas.
When enterprise buyers ask for "isolation guaranteed by the DB." RLS is a defensible answer: "PostgreSQL rejects the query even if we had a bug in the code. Here's the policy that enforces it." What a buyer loses with the answer "we have a WHERE in every query," they gain with RLS.
When your team is going to grow and discipline doesn't scale. With RLS, a new dev can forget the WHERE tenant_id and the app stays secure. The policy is the safety net.
When you're already on a shared schema and need to harden it. Migrating from "shared schema without RLS" to "shared schema with RLS" is relatively cheap: the same schema, you add policies, you add the dependency that sets the context, you adjust the tests. There's no data migration.

When it loses

Internal cross-tenant queries get complicated. A nightly job that computes "how many tasks were created today across all tenants" has to bypass the policy. Capsule 04 covers the patterns for this (a BYPASSRLS role, SET LOCAL row_security = off for a superuser), but it requires discipline.
There's a performance overhead. Every query now runs the policy's predicate. For simple queries it's marginal (~5%), for complex queries with joins it can be up to 20%. Capsule 04 covers how to measure it.
When tenants would ask for dedicated schemas by contract. Some enterprises want "we want to know our data is in a separate schema." RLS doesn't satisfy that requirement no matter how technically secure it is.

Operational cost

Low to medium. A single DB, a single schema, standard migrations. The extra complexity is: keeping policies updated when you add tables, managing a separate role for cross-tenant jobs (admin_app with BYPASSRLS), and watching out for prepared statements with asyncpg + PgBouncer. Covered in capsule 05.

Model 3: Schema-per-tenant

Each tenant has its own schema in the same DB. The same tables, replicated N times. The queries don't need to filter by tenant_id because the isolation is physical: the schema active in search_path decides which table gets accessed.

-- Create one schema per tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
CREATE SCHEMA tenant_initech;

-- Each schema has the same structure
CREATE TABLE tenant_acme.tasks (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'open',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE tenant_globex.tasks (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'open',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- ... repeat for each tenant

The app sets search_path at the start of each transaction according to the tenant:

async def schema_context(
    db: AsyncSession = Depends(get_session),
    current_tenant: Tenant = Depends(get_current_tenant),
) -> AsyncSession:
    schema_name = f"tenant_{current_tenant.slug}"
    await db.execute(text(f"SET LOCAL search_path TO {schema_name}, public"))
    return db

After that the query is the same in any tenant:

result = await db.execute(select(Task))
# Goes to tenant_acme.tasks or tenant_globex.tasks depending on search_path

Capsule 06 covers the complete implementation, including how to migrate N schemas with Alembic.

When it wins

Enterprise with <1k tenants and explicit isolation SLAs. Healthcare, finance, companies with contracts where "customer A's data in a dedicated schema" is a signed clause. Schema-per-tenant is the defensible answer.
When tenants have slightly different schemas. If Acme needs an extra column that Globex doesn't have, schema-per-tenant allows it. A shared schema forces you to have the column in every tenant (NULL for the ones that don't use it).
When some tenants have massive volume and others very little. Each schema can have its own maintenance plan, its own indexes, its own retention. In a shared schema everything applies to everyone.
When bring-your-own-DB is a feature. Some enterprise products let the customer use their own PostgreSQL instance. Schema-per-tenant is the natural intermediate step.

When it loses

Operationally expensive. A migration that adds a column to tasks now runs N times (once per schema). If you have 500 schemas and the migration takes 30 seconds each, that's 4 hours. If one fails halfway, recovery is complicated.
Cross-tenant queries are hard or impossible. "How many tasks are there in total across the whole platform?" requires a UNION ALL over 500 tables. It's generally an admin operation that runs rarely, but when it's needed it's painful.
It doesn't scale to millions of schemas. PostgreSQL can have thousands of schemas but it degrades with tens of thousands. The catalog gets big, the planners take longer, backups get complicated. If your tenant count passes ~10k, schema-per-tenant is a problem.
The connection pool gets complicated. If each connection has a different search_path, you can't prepare it and reuse it with different tenants without resetting state. The pool fragments.

Operational cost

High. You need:

A migration system that applies to N schemas (standard Alembic doesn't do it; you need a wrapper).
Disk monitoring per schema (some tenants can grow much more than others).
Tooling to create new schemas on signup ("tenant provisioning").
Backups with tooling that understands multi-schema (pg_dump --schema=tenant_acme for one, or a full backup for all).
A plan for tenants that grow a lot ("this schema moves to another DB").

That's the price of physical isolation. If physical isolation is a real requirement, you pay it.

Quantitative decision matrix

This is the module's key tool. Use it when you have to defend the architectural decision to the team.

Criterion	Shared schema (no RLS)	Shared schema with RLS	Schema-per-tenant
Optimal number of tenants	>1M (consumer) or <50 (MVP)	1k - 100k (B2B SaaS)	<1k (enterprise)
Isolation requirement	Logical (in code)	Guaranteed by the DB	Physical (a separate schema)
Cross-tenant queries	Trivial (all rows accessible)	Possible by bypassing the policy	Hard (UNION ALL over N schemas)
Operational cost of migrations	1 migration per change	1 migration + syncing the policies	N migrations per change (with tooling)
Per-tenant customization (different columns)	Impossible	Impossible	Possible
Onboarding a new tenant	An INSERT into `tenants`	An INSERT into `tenants`	CREATE SCHEMA + run the migrations
Leak risk from a query bug	High (one forgotten `WHERE`)	Low (the DB prevents it)	Very low (schemas physically separated)
Defensibility to enterprise buyers	Low	Medium-high	High
Performance overhead	0%	~5-15% on queries with policies	~0% (direct queries to one schema)
Complexity for new devs	Low (filter by `tenant_id`)	Medium (understand policies)	Medium (understand search_path)
Migrating between models	→ RLS: easy. → schema-per-tenant: expensive	→ schema-per-tenant: expensive. ← simple: trivial	← any: very expensive

Default recommendation by scenario

If your product is...	Recommended model
An MVP with <20 B2B customers	Simple shared schema. Migrate to RLS when you hit 50-100.
Typical B2B SaaS (50-10k customers)	Shared schema with RLS. It's this guide's default.
Consumer SaaS with individual accounts (Notion, Linear)	Simple shared schema, with `tenant_id`. The volume makes RLS overhead.
Enterprise B2B (<500 customers with SLAs)	Schema-per-tenant if the contracts explicitly require it. If not, RLS is also defensible.
Healthcare/finance with strict regulation	Schema-per-tenant or DB-per-tenant depending on the regulation. RLS typically doesn't satisfy auditors.
A product in the exploration phase (you're going to refactor everything in 6 months)	Simple shared schema. Don't invest in RLS until you validate the product.

For the rest of this guide and the capstone project TaskFlow we assume a shared schema with RLS because it covers the most common case (B2B SaaS) and because it teaches the subtlest model. Capsules 06 and 07 cover the other options so you have complete judgment.

Migration signals between models

In practice, products rarely stay in the initial model forever. Here are the signals that indicate it's time to migrate.

From a shared schema (no RLS) to a shared schema with RLS

You reached 50-100 customers and manual discipline is no longer sustainable.
A compliance officer asks for a "database-level isolation guarantee."
You had a near-miss (a PR that almost got a query with no WHERE tenant_id into production).
You're going to hire more devs and can't depend on each one learning the rule.

Migration: relatively cheap. The same schema, you add policies, you add a dependency, you adjust the tests. No data migration. Capsule 04 covers the steps.

From a shared schema with RLS to schema-per-tenant

You signed an enterprise contract with an explicit "dedicated schema" SLA.
Some tenants need schema customization (different columns).
Some tenant has such massive volume that it justifies a dedicated maintenance plan.
Regulators require physical isolation, not logical.

Migration: expensive. You have to create N schemas, copy the data per tenant (INSERT INTO tenant_acme.tasks SELECT * FROM tasks WHERE tenant_id = X), adjust the app to use search_path, adjust the connection pool. Do it gradually, tenant by tenant, with a feature flag.

From schema-per-tenant to a shared schema with RLS

You reached 5k+ schemas and it's no longer operationally manageable.
Migrations take hours and you can't deploy changes at the product's pace.
Cross-tenant features become critical (global analytics, admin dashboards).
The cost of custom tooling (a migration wrapper, per-schema monitoring) outweighs the value.

Migration: very expensive. You have to create the shared schema with tenant_id, move N schemas into tables with the column, rewrite queries that assume search_path, configure RLS. It's a months-long project. Almost nobody does it; typically it's a sign the initial decision was wrong.

Why does this matter in real work?

1. It's the technical decision with the highest cost to reverse. Almost every decision in your app can be refactored. Multi-tenancy can't. If you choose badly, you live with that choice for years or you spend months migrating.

2. It's the most frequent question in senior SaaS interviews. "How would you architect multi-tenancy for a B2B product with 10k customers?" If your answer is "RLS because it's new" without understanding the trade-offs, you lose the interview. If you give the decision matrix with quantitative criteria, you win it.

3. It's what separates a sellable enterprise SaaS product from an unsellable one. Serious buyers ask about the isolation model before signing. Your answer determines whether the deal closes.

4. It's what shows up in the postmortems of the most expensive breaches. Almost every historical cross-tenant leak starts with "we chose a shared schema with no RLS because it was simpler, and in one PR somebody forgot the WHERE." Knowing the models prevents being that postmortem.

Traps and common mistakes

Mistake 1 (conceptual): treating RLS as an "advanced PostgreSQL feature"

Symptom: a dev reads "PostgreSQL has Row-Level Security" and proposes using it on any table "because it sounds secure." The team ends up with RLS applied to tables that aren't multi-tenant (catalog tables, internal tables), debugging policies for cases where it adds nothing.

Why it happens: they confuse "a PostgreSQL technical feature" with "an architectural decision." RLS isn't "always more secure." It's "more secure when what you need is to isolate tenants."

How to tell: ask "what multi-tenant problem does this policy solve?". If the answer is vague ("it's more secure"), the policy probably shouldn't exist.

How to fix it: RLS only on tables that have tenant_id and where isolation between tenants is the requirement. For everything else, don't use RLS.

Mistake 2 (conceptual): choosing the "cleanest" model without measuring the operational cost

Symptom: the team chooses schema-per-tenant because "it's the cleanest conceptually." Six months later they have 200 schemas, a migration that used to take 30 seconds now takes 2 hours, deploys get painful.

Why it happens: "conceptual cleanliness" is seductive but it isn't an operational metric. The right question is "can we sustain this model when we have 10x the current tenants?". Schema-per-tenant almost never sustains it outside the pure enterprise niche.

How to tell: if the justification for the decision is "cleaner" or "prettier" with no numbers, there's a mistake. Ask for: the expected number of tenants, the documentable isolation requirement, the acceptable migration cost.

How to fix it: apply this capsule's decision matrix with concrete data. If the result is "shared schema with RLS," use it even if it seems less elegant to you.

Mistake 3 (conceptual): assuming "more isolation is always better"

Symptom: the team believes that since physical isolation (schema-per-tenant) is "stronger" than logical isolation (RLS), choosing the stronger one is always the safe decision.

Why it happens: it confuses "maximum theoretical security" with "the optimal real decision." More isolation = more operational cost. If you don't need physical isolation (because RLS satisfies your buyers and your compliance), paying the extra operational cost is waste.

How to tell: ask for concrete evidence of the requirement. Is there a signed contract mentioning "a dedicated schema"? Is there specific regulation? If the answer is "no, but just in case," physical isolation is overkill.

How to fix it: choose the minimum isolation that satisfies your real requirements. If RLS is enough, RLS wins. If you need schema-per-tenant because the contract says so, you pay the cost.

Mistake 4 (operational): not designing for migration between models

Symptom: the team chooses a shared schema with no RLS and designs the app assuming tenant_id "will always be in the app layer." When the time comes to migrate to RLS, they discover many queries don't set a transaction context, there are endpoints that assume implicit cross-tenant access, etc. The migration takes 3x longer than expected.

Why it happens: they assumed the initial decision was permanent and didn't design migration paths.

How to tell: review: does the app have a single point where the tenant context gets set? Are the queries uniform in how they filter? If not, migrating is hard.

How to fix it: from day one, encapsulate the "current tenant" in a single dependency. Even if you don't use RLS today, the day you add it you only change that dependency.

Mistake 5 (conceptual): miscounting the tenants for the decision matrix

Symptom: the team counts "tenants" as "users" and ends up thinking it has 100k tenants when it has 100 companies with 1000 users each. They choose the wrong model from a bad count.

Why it happens: "tenant" has an ambiguous definition. In B2B it's typically "a customer company." In consumer it can be "an individual account." If you confuse the units, the decision matrix gives you the wrong answer.

How to tell: define it formally: what is a "tenant" in your product? Is it the billing unit? The data isolation unit? Document the answer.

How to fix it: "tenant" for this guide is the data isolation unit: the set of rows that must NEVER be seen from another unit. If your product is B2B and each company is an isolation unit, "tenants" = "number of customer companies."

Exercises

Exercise 1: apply the decision matrix to three scenarios

For each of these scenarios, decide which model you'd recommend and justify it with two quantitative criteria from the decision matrix.

Scenario A: A B2B SaaS inventory management product. 800 active customers, all small/medium businesses. A backend team of 6 Python devs. Buyers ask for an "isolation guarantee" in the contract but don't specify a mechanism.

Scenario B: A Notion-style consumer notes product. 4 million individual accounts. Most have <100 notes. A backend team of 25 devs.

Scenario C: A B2B product for hospitals. 30 active customers, contracts with an explicit SLA of "patient data in a separate schema per institution." HIPAA regulation applies.

See solution

Scenario A: Shared schema with RLS.

Number of tenants: 800 is in RLS's sweet spot (1k-100k is the optimal range). A simple shared schema is risky with 6 devs (the probability of forgetting a WHERE goes up). Schema-per-tenant is overkill for 800 schemas with no specific enterprise contracts.
Defensibility: RLS answers "isolation guarantee" with "PostgreSQL enforces policies at the DB level." It's defensible without schema-per-tenant's operational friction.

Scenario B: Simple shared schema (no RLS).

Number of tenants: 4M is outside RLS's optimal range (the policy overhead on every query accumulates at that scale). Schema-per-tenant is impossible (4M schemas).
Consumer usage pattern: the accounts are individual, there are no enterprise contracts asking for documentable isolation. The "filter by account_id" discipline can be sustained with good lint tooling and lots of tests.

Scenario C: Schema-per-tenant.

An explicit requirement: the contract mentions "a separate schema," which is exactly what schema-per-tenant gives. RLS doesn't satisfy the literal requirement.
Number of tenants: 30 is well within schema-per-tenant's manageable range (<1k). Manageable operational cost.
Compliance: HIPAA typically wants to see documentable physical isolation. RLS passes many auditors but not all; schema-per-tenant passes all of them.

The key lesson: the answer isn't "RLS because it's new" in any case. It's the intersection of the number of tenants × the specific requirement × the acceptable operational cost.

Exercise 2: argue against a bad decision

Your lead proposes: "Let's go with schema-per-tenant for our B2B product. We have 50 customers now but we expect to grow to 5000. And it's cleaner than RLS."

Articulate 3 counter-arguments based on this capsule. What would you propose instead?

See solution

Counter-arguments:

"Clean" isn't an operational metric. At 5000 schemas, a migration that takes 30 seconds on a single schema takes 41 hours in total (5000 × 30s). Deploys become impractical. The "conceptual cleanliness" gets paid for in operations hours.
There's no documented requirement asking for physical isolation. If the contracts only say "we guarantee isolation" without specifying a mechanism, RLS satisfies it and is defensible. Paying the operational cost of separate schemas without having an explicit requirement is over-engineering.
Migrating from schema-per-tenant to another model later is 10x more expensive than migrating from RLS. If in 2 years you discover the chosen model doesn't scale, RLS lets you migrate gradually (change the dependency, adjust the policies). Schema-per-tenant forces you to consolidate N tables into one with tenant_id, adjust every query that assumes search_path, etc. It's a 6+ month project.

Alternative proposal: Shared schema with RLS from day one. Reasons:

It covers the range 50 → 5000 (and quite a bit beyond) with no architectural changes.
Defensible isolation to enterprise buyers.
Low operational cost (a single DB, standard Alembic migrations).
If at some point a specific customer requires a dedicated schema, we can migrate ONLY that customer (a hybrid) without touching the rest.

Future migration plan: document in MULTITENANCY.md that if we get a customer with an explicit dedicated-schema SLA, we migrate them to their own schema and keep the rest in shared. The best of both worlds when it's needed.

Exercise 3: identify the model of a well-known product

Research (or deduce from their public documentation) which multi-tenancy model these products use. Justify your deduction.

a) Supabase b) GitHub c) Slack d) Linear

See solution

a) Supabase: Every customer who creates a project on Supabase gets their own dedicated PostgreSQL DB (a model beyond schema-per-tenant: DB-per-tenant). Justification: the product sells "your own Postgres," not "a shared slice." For the user's data inside their project, Supabase recommends RLS (its flagship feature) — there the "tenant" is the end user of the product built on top of Supabase, not Supabase itself.

b) GitHub: A shared schema with repo_id / org_id (no RLS, discipline in queries with many layers of protection). Justification: 100M+ users and 400M+ repos make schema-per-tenant impossible. RLS overhead at that scale would probably be too much. The protection comes from many layers: strict code review, automated tooling, dedicated SREs.

c) Slack: A shared schema with team_id in their main DBs, possibly sharded by team_id across different clusters at large scale. Justification: millions of workspaces, each with thousands of messages. The isolation unit (a workspace) is stable and filterable. There's no public evidence of RLS but it's likely on some critical tables.

d) Linear: A shared schema with workspace_id. Justification: a consumer-like product (each workspace can be a person or a team), high volume, a focus on performance. They've spoken publicly about their emphasis on optimized queries — RLS would add overhead that doesn't seem tolerable for their kind of UX (real-time).

The pattern to notice: no consumer-like product uses schema-per-tenant. It only shows up in pure B2B enterprise or as an explicit feature (Supabase). Most of the real B2B SaaS market is between a simple shared schema (with discipline + tooling) and RLS.

This exercise's limitation: what we know publicly may be out of date. What matters isn't getting the exact model right but understanding the reasoning by scale and product.

Exercise 4: predict the cost of a schema change

Your product has 250 tenants. You need to add a priority INTEGER NOT NULL DEFAULT 0 column to the tasks table. The base migration (not counting overhead) takes 8 seconds on a table with 100k rows.

Compute the estimated total migration time for each model:

a) A shared schema (with or without RLS — the migration is the same). b) Schema-per-tenant.

What additional operational risks does each one have?

See solution

a) Shared schema: 8 seconds. A single table, a single migration.

Risks:

If the table is under load, the ALTER TABLE takes an exclusive lock. For 8 seconds that may be tolerable; for large tables it's problematic (capsule 05 of module 5 covers zero-downtime migrations).
The migration affects ALL tenants simultaneously. If it breaks something, it breaks for everyone.

b) Schema-per-tenant: 250 × 8 seconds = 33 minutes (serially).

In parallel (5 schemas at a time): ~7 minutes. But it requires tooling and it's still 50x more than a shared schema.

Risks:

If some migration fails halfway (from a timeout, from a lock conflict), you have an inconsistent state: some schemas migrated, others not. Recovery requires explicit logic.
You need a script that applies to N schemas, handles failures, reports progress. That's not standard Alembic.
Some tenants can have much more data (1M rows instead of 100k). The duration isn't uniform.
If a tenant has the table locked by a long user operation, the migration hangs. Skip and retry? Wait?

The lesson: schema-per-tenant turns a routine migration into a mini operational project. At 250 tenants it's manageable with tooling. At 5000 it's a constant pain. At 50000 it's blocking for deploys.

This is the abstract "operational cost" made concrete: it isn't "extra hours" in general, it's hours the team can't ship features because they're operating migrations.

Exercise 5: design the migration path

Your product is on a simple shared schema (no RLS) with 80 customers. Next quarter you're going to hire 4 new devs. You decide to migrate to a shared schema with RLS before the team grows.

Design the 5 high-level steps of the migration (no code yet — you'll see the code in capsules 04-05). What comes first, what comes after, what can be done in parallel?

See solution

Step 1 (preparation, week 1): Encapsulate the "current tenant" in a single dependency. If the app has 12 different places where current_tenant gets obtained, consolidate them into one. This is a prerequisite because the dependency is going to be where we add SET LOCAL app.tenant_id. Without a single insertion point, the migration to RLS doubles in complexity.

Step 2 (preparation, week 1): Audit every query that touches multi-tenant tables. List which ones already filter by tenant_id and which don't. The ones that don't filter are the ones that currently depend 100% on the dev's discipline. RLS is going to rescue them, but it's worth knowing how many there are (it's the current security "gap").

Step 3 (DB changes, week 2): Create a separate role for the app (app_user) that is NOT the tables' owner. Reason: by default, RLS doesn't apply to the owner. We need a separate role or to use FORCE ROW LEVEL SECURITY. Document the decision.

Step 4 (DB changes, weeks 2-3): Apply ENABLE ROW LEVEL SECURITY and CREATE POLICY to every multi-tenant table, one by one. In staging first, validate the app still works, then production. Important: during this phase, the dependency isn't setting the context yet, so the policies won't filter (because current_setting will be NULL). You have to decide: do we want the queries to fail for safety, or to pass with no filter during the migration window? I recommend: policies with an explicit fallback (USING (current_setting('app.tenant_id', true) IS NULL OR tenant_id = current_setting('app.tenant_id')::BIGINT)) during the transition.

Step 5 (app changes, week 3): Modify the centralized dependency to run SET LOCAL app.tenant_id = .... Validate in staging. Then, remove the fallback from the policies (replace it with the strict version USING (tenant_id = current_setting('app.tenant_id')::BIGINT)).

Step 6 (verification, week 4): Write "malicious" tests that prove the isolation. Run them in CI. These tests try to read another tenant's data with queries deliberately lacking a WHERE and verify they come back empty.

Possible parallelism:

Step 1 and Step 2 can be done in parallel (one touches auth code, the other touches queries).
Step 3 and Step 4 have to be done serially (you need the role before applying FORCE).
Step 5 and Step 6 can start in parallel: the dependency migration in one branch, the tests in another.

Total estimated time: 4 weeks with a focused mid-level dev. Most of it is validation (not breaking the app), not implementation.

The main risk: that during the transition (Step 4 before Step 5) a tenant accidentally sees unfiltered queries return empty data instead of their data. The policy with the fallback during the window mitigates this.

Summary and next step

In this capsule you learned:

Multi-tenancy is a spectrum, not a binary. There are three main models in PostgreSQL: a simple shared schema, a shared schema with RLS, and schema-per-tenant.
A simple shared schema wins in consumer SaaS with millions of small accounts, MVPs, and products where cross-tenant queries are frequent.
A shared schema with RLS wins in B2B SaaS with 1k-100k tenants. It's the sweet spot for most of the B2B SaaS market and this guide's default model.
Schema-per-tenant wins in enterprise with <1k tenants with explicit physical-isolation SLAs, per-tenant customization, or strict regulation.
The quantitative decision matrix lets you defend the choice with judgment: the number of tenants, the documentable isolation requirement, the acceptable operational cost.
Migration between models isn't symmetric: increasing isolation (shared → RLS → schema-per-tenant) gets progressively more expensive. That's why the initial decision matters.
Mistake #1 is choosing by "conceptual cleanliness" without measuring the operational cost. Schema-per-tenant is seductive but operationally heavy.
For this guide and TaskFlow, the default = a shared schema with RLS. The next capsules implement exactly that, after first seeing the simplest model to understand why it isn't enough on its own.

Before moving on you should be able to:

Recite the three models with their optimal tenant range and a use case for each one.
Apply the decision matrix to a new scenario and defend the choice.
Identify the signals that indicate migration between models.
Anticipate the operational cost of each model (especially migrations in schema-per-tenant).
Argue why the "more isolation is always better" decision is wrong.

Next capsule — Shared schema with tenant_id and its pitfalls. You're going to implement the simplest model (a shared schema with tenant_id in every table, filtering manually in queries) and see its limits firsthand. You're going to understand why manual discipline doesn't scale, what patterns help mitigate the risk (lint rules, query encapsulation, a code review checklist), and why eventually you're going to want RLS's guarantee on top. It's the capsule that motivates the rest of the module: to appreciate what RLS gives you, you first have to understand what not having RLS takes away.

Resources

AWS — SaaS Tenant Isolation Strategies (whitepaper) — the most complete reference on the three models. Required reading for making defensible architectural decisions.
Crunchy Data — Designing Your Postgres Database for Multi-Tenancy — a pragmatic analysis with real examples of the trade-offs.
Citus Data — At what scale does Postgres start to show its age? — a sharding perspective on the base models. Useful for understanding why some models don't scale.
Supabase — Multi-tenancy with RLS — the most widely used RLS model in modern production. Real patterns you're going to see in capsules 04-05.
PostgreSQL — Row Security Policies — the feature's official reference. Reading for capsule 04.
Brandur — Postgres-only stacks at scale — a perspective on when PostgreSQL alone is enough, relevant for understanding each model's limits at scale.

Module 4 — SQL Patterns for Production APIs Guide

Next capsule: Shared schema with tenant_id and its pitfalls — the simplest model and why it isn't enough on its own.

← PreviousMulti-Tenancy in PostgreSQL Next →Shared schema with `tenant_id` and its pitfalls

Three multi-tenancy models

Capsule overview

Mental model: isolation as a spectrum, not a binary

Model 1: Shared schema with tenant_id

When it wins

When it loses

Operational cost

Model 2: Shared schema with RLS

When it wins

When it loses

Operational cost

Model 3: Schema-per-tenant

When it wins

When it loses

Operational cost

Quantitative decision matrix

Default recommendation by scenario

Migration signals between models

From a shared schema (no RLS) to a shared schema with RLS

From a shared schema with RLS to schema-per-tenant

From schema-per-tenant to a shared schema with RLS

Why does this matter in real work?

Traps and common mistakes

Mistake 1 (conceptual): treating RLS as an "advanced PostgreSQL feature"

Mistake 2 (conceptual): choosing the "cleanest" model without measuring the operational cost

Mistake 3 (conceptual): assuming "more isolation is always better"

Mistake 4 (operational): not designing for migration between models

Mistake 5 (conceptual): miscounting the tenants for the decision matrix

Exercises

Exercise 1: apply the decision matrix to three scenarios

Exercise 2: argue against a bad decision

Exercise 3: identify the model of a well-known product

Exercise 4: predict the cost of a schema change

Exercise 5: design the migration path

Summary and next step

Resources

Model 1: Shared schema with `tenant_id`