Multi Tenant

Almost every SaaS product serves many customers ("tenants") from one system. By the end of this lesson you'll be able to choose between the three standard isolation models — shared schema , schema-per-tenant , and database-per-tenant — and implement the most common one safely with a tenant_id column and PostgreSQL Row-Level Security so the database itself stops one customer from ever seeing another's data.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Every example in this lesson uses one tasks table shared by two customers: tenant 1 = Acme and tenant 2 = Globex . Notice the tenant_id column — that single column is what keeps their data apart.

Rows 1–3 belong to Acme; rows 4–6 belong to Globex. They sit side by side in one physical table.

A tenant is one customer of your SaaS — usually a whole company and all of its users. A multi-tenant system serves many tenants from shared infrastructure. The central challenge is isolation : tenant A must never see, change, or even count tenant B's rows, even though they might be running the very same product on the very same servers.

Think of an apartment building. Shared schema = everyone shares the building and the lobby, but each flat has its own lock (the tenant_id ). Schema-per-tenant = each family gets a private floor. Database-per-tenant = each family gets their own separate building. More walls means more isolation — but also more cost to build and maintain.

One set of tables, a tenant_id on each. Cheapest, easiest to scale to thousands of tenants. Risk: a forgotten filter leaks data.

Each tenant gets its own schema (namespace) of tables. Stronger isolation, moderate cost. Migrations must run N times.

A whole separate database per tenant. Strongest isolation, easiest per-tenant backup. Most expensive; hard past a few hundred tenants.

This is the default choice for most SaaS apps because it's the cheapest and scales to thousands of tenants on one set of tables. The trick is a discriminator column — conventionally tenant_id — added to every tenant-owned table. The golden rule: every query must filter on tenant_id , and every multi-column index should list tenant_id first.

Compare that to what happens when you forget the filter. Because every tenant's rows live in one table, an unscoped query quietly returns everyone — the single most dangerous mistake in multi-tenant SQL.

Fill in the blanks to return only Globex's open ( todo ) tasks. The expected result is in the comments so you can check yourself.

Relying on every developer to remember WHERE tenant_id = … on every query forever is a bet you will eventually lose. Row-Level Security (RLS) moves the filter into the database. You set the current tenant once per connection, write one POLICY per table, and PostgreSQL transparently appends the tenant filter to every SELECT , UPDATE , and DELETE . A forgotten WHERE clause is no longer a leak.

Change the tenant context and re-run the same query — the database now shows a completely different set of rows. Tenant 2 never sees tenant 1's data, and the SQL never even mentions tenant_id .

Sometimes shared tables aren't enough — a big enterprise customer wants its data provably separated, or a regulation demands a dedicated backup per customer. Two stronger models trade cost for isolation:

Schema-per-tenant: each tenant gets its own schema (a namespace of tables) inside one database. You route a connection with SET search_path TO tenant_acme; , then plain SELECT * FROM projects hits that tenant's tables. Isolation is strong and there's no tenant_id column to forget — but every schema change must be applied to every schema, so migrations fan out and the catalogue gets large past a few thousand tenants.

Database-per-tenant: a whole separate database (or server) per tenant. The strongest isolation, the simplest "delete a customer" and "restore one customer" story, and a natural fit for compliance (HIPAA, data residency). The price: provisioning, connection-pool pressure, and migrations that must run across hundreds of databases. It rarely scales past the low hundreds of tenants without serious tooling.

The "noisy neighbour" angle: in shared schema, one tenant running a huge report can slow everyone down (shared CPU, cache, locks). More physical separation (schema → database) reduces noisy-neighbour blast radius, which is another reason big customers pay for it.

Read the scenario in the comments and choose the model that fits. There's one blank — write the model name.

Rule of thumb: start with shared schema + RLS; promote your biggest/most-regulated tenants to schema- or database-per-tenant only when they need it.

Q: Isn't a tenant_id on every table wasteful?

No — it's a single integer column, and it's also exactly what your indexes need. Put tenant_id first in composite indexes ( (tenant_id, status) ) so the database can jump straight to one tenant's rows.

Q: Does Row-Level Security slow queries down?

The policy is just an extra AND tenant_id = … the planner adds. With a tenant_id -leading index the cost is negligible. Keep policy expressions simple (avoid sub-queries) and they stay fast.

Yes, and large SaaS companies do. Most tenants share one schema with RLS, while a handful of huge or regulated customers are promoted to their own database. The app routes each request to the right place based on the tenant.

Q: How do I run a report across all tenants if RLS hides everything?

Use a separate privileged role with a BYPASSRLS attribute or a policy granting it full access, and run analytics with that role only. Never reuse it for normal request handling — that defeats the isolation.

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then run it in a PostgreSQL playground to confirm the table auto-scopes.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary