Module 4: Multi-Tenancy in PostgreSQL

Multi-Tenancy in PostgreSQL

Next →

Welcome to the module

So far you've learned to do pagination that scales (module 1), soft deletes that don't kill performance (module 2), and audit logs that record who did what (module 3). Those patterns assume something implicit: that your API serves a single customer. The moment your product charges more than one organization for the same deployment, that assumption breaks — and the problem changes in nature.

Multi-tenancy is the architectural decision that defines a SaaS. You choose it once at the start and you live with the consequences for years. Choosing badly costs months of refactoring and, in the worst case, a public incident where tenant A's data shows up in tenant B's dashboard. The teams that end up in that situation didn't fail from a lack of technical capability. They failed because they treated multi-tenancy as a PostgreSQL feature ("learn RLS and you're done") instead of what it really is: an architectural decision between three options, each with its own quantifiable trade-off.

This module teaches you to make that decision with judgment, implement it properly in PostgreSQL 16+ with FastAPI and SQLAlchemy 2.0 async, and verify the isolation works even when a dev on your team writes a query with a bug. By the end you'll have a working multi-tenant mini-project implemented with Row-Level Security (RLS), "malicious" tests that prove the isolation, and the foundation that module 8 (TaskFlow) is going to extend into a complete SaaS API.

Where are we? Where are we going?

Module 3 closed the "Defensive modeling" block with audit logs. Its central lesson was: the audit log tells you who did what. Now comes the complementary question: how do you make sure tenant A can't read or modify tenant B's data — not even from a dev's bug? That guarantee doesn't come from the application code. It comes from PostgreSQL, configured correctly, with RLS or physical schema isolation.

This module opens the "Live Operations" block:

  • Module 4 (this one): How you isolate tenants in a single shared DB.
  • Module 5: How you evolve the schema with no downtime once that DB already has real traffic (the risk multiplies in multi-tenant: a downtime affects every tenant simultaneously).

After module 5 comes optimistic locking (module 6), bulk operations (module 7), and the capstone project TaskFlow (module 8). TaskFlow is designed multi-tenant from day one and uses exactly the configuration you're going to learn in this module: shared schema with tenant_id + RLS + SET LOCAL from a FastAPI dependency.

Professional objective

By the end of this module you'll be able to:

  • Decide between three multi-tenant architectures (shared schema with tenant_id, RLS over a shared schema, schema-per-tenant) with quantitative criteria based on the number of tenants, the isolation requirement, and the operational cost — not by preference.
  • Implement Row-Level Security in PostgreSQL: ALTER TABLE ... ENABLE ROW LEVEL SECURITY, CREATE POLICY ... USING (...), FORCE ROW LEVEL SECURITY, and the role ownership gotchas.
  • Integrate RLS with FastAPI using a dependency that runs SET LOCAL app.tenant_id = ... at the start of each transaction and configuring asyncpg to avoid the cached prepared statements bug.
  • Explicitly distinguish RLS for multi-tenancy from RLS for auth/RBAC (an expensive mistake that mixes two different problems).
  • Write isolation tests that prove "tenant A can't read tenant B's data" even with queries that have no WHERE tenant_id filter.
  • Diagnose the most common cross-tenant leak anti-patterns (queries with no filter, forgotten joins, cron jobs with the wrong role) and show how RLS prevents them even when there's a bug in the code.

Why does this module matter?

Any B2B SaaS product that charges more than one company for the same deployment is multi-tenant. That includes internal tools that grow, MVPs that sign their second customer, products that sell to teams instead of individuals. The decision of how to isolate tenants is the first thing an enterprise buyer asks before signing:

"How do you guarantee that an employee of a competitor company who's also your customer can't see our data?"

If the answer is "we have WHERE tenant_id = ... in every query," the buyer already knows a single forgotten WHERE opens a breach. If the answer is "RLS in PostgreSQL: the database rejects any query that tries to cross tenants, even if we had a bug in the code," the buyer signs. That difference in answer is the difference between closing enterprise deals or not.

The module also matters because the #1 mistake of teams that discover RLS is using it for user auth ("permissions by user_id"). That turns every query into a debugging session over SQL policies and kills productivity. You're going to learn not to fall into that trap: RLS for multi-tenancy (where the tenant is stable throughout the whole session), RBAC in the application layer for granular user permissions.

A scenario that illustrates the module

Imagine you join your new backend team at a B2B startup. The product has 47 customers. Each customer has between 5 and 200 users. Today everything lives in a single PostgreSQL with tables that have client_id and the rule "always filter by client_id in every query." Everything works.

In your first month your lead asks you to implement the GET /api/projects endpoint for a new feature. You write it, it passes the tests, it goes to production. A week later a customer reports a bug: three projects with strange names show up in their project list, names they swear they never created. Reviewing the code, you see the line:

projects = await db.execute(select(Project).order_by(Project.created_at.desc()))

You forgot the .where(Project.client_id == current_client.id). The query pulled projects from ALL the customers and showed them to the first one who asked. Customer 17 saw customer 31's project names. Your company just had a cross-tenant leak.

This module teaches you to avoid exactly that. Capsule 02 shows you the three possible architectures and how to choose between them. Capsules 03-05 implement the option that will be recommended as the default (shared schema + RLS): the database rejects the buggy query because PostgreSQL knows the request's tenant and filters automatically. Capsule 06 covers when to scale up to schema-per-tenant. Capsule 07 teaches you to anticipate the anti-patterns that cause leaks. Capsule 08 integrates everything into a mini-project that passes "malicious" tests designed to catch the leak before it reaches production.

Module map

CapsuleTopicWhat you'll learn
01Module introductionThe map, the objectives, the connection with the path (this one)
02Three multi-tenancy modelsA decision matrix between shared schema, RLS, and schema-per-tenant with quantitative criteria
03Shared schema with tenant_id and its pitfallsThe most common model, its classic anti-patterns, and why it isn't enough on its own
04Row-Level Security: fundamentalsHow RLS works in PostgreSQL, what it solves, and the caveat about not using it for auth
05RLS with FastAPI and SQLAlchemy asyncThe complete implementation: SET LOCAL from a dependency, asyncpg gotchas
06Schema-per-tenant: when and howThe enterprise approach: setup, migrations to N schemas, the real operational cost
07Cross-tenant leak: anti-patternsMistakes that end in a breach and how RLS prevents them even with a bug in the code
08Project: multi-tenant TaskFlow with RLSA mini-API with two tenants, working RLS, and isolation tests

The narrative flow: first you understand there are three options (02), then you see the base option with its limits (03), then you learn the mechanism that complements them (04-05), then you get to know the enterprise option (06), you anticipate the mistakes (07), and you build the mini-project that brings it all together (08).

Connection with the integrative project (TaskFlow, module 8)

TaskFlow is the multi-tenant SaaS API you're going to build in module 8. Three fictional tenants (Acme Corp, Globex, and Initech), each with users, projects, and tasks. The chosen architecture is shared schema with RLS because it satisfies the quantitative criteria you're going to learn in capsule 02:

  • 1k-100k tenants expected (RLS's optimal range).
  • B2B SaaS with a requirement for isolation guaranteed at the DB level but with no enterprise contracts asking for dedicated schemas.
  • An acceptable operational cost (there's no need to migrate 10k schemas on every deploy).

Module 4 takes you exactly to the point where you have that configuration working with two tenants and two tables. Module 8 scales that foundation into the complete API. The decision is documented in the final project's MULTITENANCY.md, written exactly with this module's criteria.

What is NOT covered in this module

An explicit list with reasons:

  • Auth and RBAC — covered in guide #9 (Auth & RBAC). Here RLS is used only to isolate tenants, not for per-user permissions. Capsule 04 explains why mixing them leads to debugging hell.
  • Zero-downtime migrations — covered in module 5 (next). Here we assume the migrations get applied at moments where downtime doesn't matter, to keep the focus on isolation.
  • Bulk operations in multi-tenant — covered in module 7. There are particularities when a bulk insert crosses tenants (cross-tenant imports, global dashboards) that get explored there.
  • Horizontal sharding by tenant — out of scope. This guide covers multi-tenancy in a single DB. Sharding (Citus, Vitess) is a topic for more advanced guides.
  • Advanced connection pooling with RLS — the gotchas of PgBouncer + asyncpg + prepared statements get mentioned but the production setup of pool tuning is in guide #12 (Database Performance).
  • Cross-cutting multi-tenant audit logs — module 3's audit log already records tenant_id if it's in current_setting('app.tenant_id'). Here we don't go deeper into "audit per tenant" specific patterns.

Traps to avoid while taking the module

  1. Don't skip capsule 02 even though it looks "just conceptual." The decision matrix between the three models is the most important thing in the module. If you go straight to "implement RLS" without understanding when NOT to use RLS, you're going to apply it in cases where a simple shared schema or schema-per-tenant win.

  2. Read the "RLS isn't for auth" caveat in capsules 04 AND 05. We repeat it on purpose. It's the most expensive mistake to reverse once you have 50 SQL policies implemented for "granular per-user permissions" and you discover every query is a debugging session.

  3. Don't assume SET LOCAL works automatically with asyncpg. Capsule 05 covers the cached prepared statements bug with PgBouncer. It's the kind of gotcha you discover in production when one tenant intermittently sees another's data.

  4. Don't underestimate the operational cost of schema-per-tenant. Capsule 06 shows what happens when you have 500 schemas and a migration fails halfway. If you only read the theory without the concrete example, you're going to choose schema-per-tenant thinking it's "cleaner" when operationally it's extremely heavy.

  5. Don't confuse WHERE tenant_id = X with isolation. Module 03 shows why the "discipline in queries" approach has the ceiling of "a single forgotten WHERE = a breach." RLS doesn't replace the discipline; it backs it up with a DB-level guarantee.

Self-assessment question

Before starting, answer:

  1. What's the difference between "logical isolation" and "physical isolation" between tenants? (Hint: one lives in queries, the other in DB structures.)

  2. Why is WHERE tenant_id = $1 in every query fragile even though it's technically correct? (Hint: how many lines of code have to forget it to cause a leak?)

  3. If your product has 200 enterprise tenants with isolation SLAs that mention "a dedicated schema," what architecture would you choose and why? (There's no single answer — it depends.)

  4. What's the difference between current_user (a PostgreSQL role) and "current tenant" (an entity in your app)? This distinction is critical before designing RLS policies.

If you're unsure about any of them, that's the capsule you should pay the most attention to. Capsules 02 (decisions), 03 (shared schema), 04 (RLS fundamentals), and 06 (schema-per-tenant) each answer one question from the list.

Evidence of success

By the end of the module you'll know you succeeded if:

  • You can explain the three multi-tenancy models with a use case for each one and a quantitative criterion (number of tenants, isolation requirement, operational cost).
  • You implemented capsule 08's mini-project (two tenants, two tables, working RLS) and the isolation tests pass.
  • You can diagnose a cross-tenant leak in a code review: you identify the buggy query, explain why RLS would have prevented it, and write the test that catches it.
  • You clearly distinguish RLS for multi-tenancy from RBAC for auth and can argue why mixing them is a problem.
  • You know the stack's gotchas (asyncpg + PgBouncer + RLS, FORCE ROW LEVEL SECURITY, role ownership) and can anticipate them in a PR.

We start in the next capsule

We start with capsule 02: Three multi-tenancy models. There you're going to build the mental model that supports the rest of the module: there are three ways to isolate tenants in PostgreSQL, each one solving a different range of "how many tenants you have" and "how strict your isolation requirement is." Without that clear decision, implementing RLS or schema-per-tenant means picking a tool before understanding the problem.

Before moving on, make sure you have:

  • PostgreSQL 16+ installed or accessible (Docker works perfectly).
  • A working FastAPI + SQLAlchemy 2.0 async project where you can experiment without fear (it can be a new one or module 3's).
  • Access to psql or a SQL client you can use to check policies and current_settings.

Resources for the module

  1. PostgreSQL 16 — Row Security Policies — the official reference, required reading before capsule 04.
  2. Supabase — Row Level Security — the most widely used RLS model in modern multi-tenant production; a reference for real patterns.
  3. AWS — Multi-Tenant SaaS Storage Strategies — a whitepaper covering the three models with architectural judgment, not feature-level.
  4. Crunchy Data — Designing Your Postgres Database for Multi-Tenancy — a pragmatic analysis of the trade-offs.
  5. Brandur — "Implementing Stripe-like Idempotency Keys in Postgres" — not directly about multi-tenancy, but the style of reasoning about DB-level guarantees is exactly the module's.
  6. Citus Data — Multi-tenant data models — a perspective from sharding but the base models are the same.

Module 4 — SQL Patterns for Production APIs Guide

Next capsule: Three multi-tenancy models — the architectural decision that defines the rest of the module.

← PreviousModule 3 deliverable: Audit Trail in TaskFlow (standalone)Next →Three multi-tenancy models