Multi-Tenancy: Shared Schema, Schema-per-Tenant and Database-per-Tenant

What you'll learn: By the end of this lesson you will be able to define a tenant and a tenancy model, compare shared-schema, schema-per-tenant and database-per-tenant storage on isolation, cost, migrations, backup and noisy neighbours, resolve a request to a tenant and then to a deployment, explain why authentication is not isolation, mix models by tier, and plan a safe tenant move and a fleet-wide schema change.

An apartment building has three ways to keep neighbours' belongings apart. Everyone can share one storage hall with numbered bins and a clerk who checks tickets. Each household can get a locked room in the same hall. Or each household can rent a separate building. The hall is cheapest and the buildings are safest, and the clerk's honesty decides how safe the hall really is.

ShopStream Stage 8 already has a short section on multi-tenancy: a three-row table and the choice of pooled storage with row-level security. This lesson is the dedicated treatment and adds what that section leaves out: how to pick a model, what each costs to operate, how requests find their tenant, and how a tenant moves between models.

Verified against the PostgreSQL 18 manual, Microsoft's Azure Architecture Center multitenant guidance and the AWS SaaS Architecture Fundamentals whitepaper, read on 2026-11-08. The whitepaper carries a notice that it is for historical reference.

The core idea

A tenant is a customer whose data and configuration must stay apart from every other customer's, even though many tenants run on one system. A tenancy model decides what is shared and what is separate. Isolation is a spectrum, not a switch: Microsoft describes a continuum from "fully isolated" (shared nothing) to "fully shared", and each tier of a solution can sit at a different point on it.

Three illustrations run through the lesson, each labelled:

• Minimal example: two tenants in one table, with a database-enforced policy.
• Realistic example: a grocery chain on ShopStream that needs more isolation than the pool gives.
• Failure example: a schema change that reaches some tenants and not others.

Who counts as a tenant is a decision too. In business-to-business products a tenant usually maps to a customer organisation, but one customer may need several (departments, countries, separate test and production copies); in consumer products a tenant may be a person, a family or a club.

Three ways to store tenant data

Figure 1. The same application over three storage models. How to read this: the application is shared in all three. What changes is how deep the separation goes: a column, a namespace or a whole database.

Microsoft also describes sharding (several databases, each holding one or more tenants) and the deployment stamp (a full copy of the solution for a group of tenants). Both sit between the extremes and reappear below.

Shared schema: the tenant column

Minimal example. Two tenants, store_17 and store_42, share one stock table. The SQL is valid PostgreSQL and shows the shape; it was not run for this lesson, so no output is shown.

CREATE TABLE stock (
  store_id text    NOT NULL,
  sku      text    NOT NULL,
  qty      integer NOT NULL CHECK (qty >= 0),
  PRIMARY KEY (store_id, sku)
);

ALTER TABLE stock ENABLE ROW LEVEL SECURITY;
ALTER TABLE stock FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON stock
  USING      (store_id = current_setting('app.store_id', true))
  WITH CHECK (store_id = current_setting('app.store_id', true));

-- per request, inside the transaction:
SELECT set_config('app.store_id', 'store_17', true);

The tenant id leads the primary key, so every lookup is naturally scoped. Row-level security (RLS) adds a database-enforced filter. The PostgreSQL manual says:

• With RLS enabled, normal access "must be allowed by a row security policy"; with no policy, "a default-deny policy is used".
• USING governs which existing rows a command can see or change; WITH CHECK constrains new row values and defaults to the USING expression when omitted.
• Superusers and BYPASSRLS roles always bypass it; table owners do too unless FORCE ROW LEVEL SECURITY is set. The application must connect as an ordinary role.
• set_config(name, value, true) applies "only during the current transaction", which matters when pooled connections are reused. current_setting(name, true) returns NULL when the setting is missing.

RLS has limits. Unique and foreign-key checks "always bypass row security", so a design can leak through "covert channels" (a duplicate-key error reveals that a row exists). Policies that read other rows can race, and the simplest policies, which look only at the row itself, perform best. Microsoft adds that many multitenant systems skip RLS because carrying user and tenant identity into every query is hard to design and test. Treat RLS as a backstop behind a repository layer that always takes the tenant as an argument.

Shared schema is also where noisy neighbours live: one tenant's heavy query uses capacity everyone shares. Microsoft recommends throttling or rate limiting and per-tenant consumption metrics, which a shared database may not report by itself.

Schema per tenant: one namespace each

A PostgreSQL schema is a namespace: "Schemas are analogous to directories at the operating system level, except that schemas cannot be nested". Each tenant gets identical tables in its own schema.

CREATE SCHEMA store_17;
CREATE TABLE store_17.stock (
  sku text PRIMARY KEY,
  qty integer NOT NULL CHECK (qty >= 0)
);

-- per request, inside the transaction:
SELECT set_config('search_path', 'store_17', true);

Three facts from the manual shape the design:

• A schema is not a wall: users can reach objects in any schema "if they have privileges to do so". Isolation comes from privileges such as USAGE, which by default is not granted on schemas a user does not own.
• Names resolve through search_path, and the manual warns that adding a schema to it "effectively trusts all users having CREATE privilege on that schema". Take the schema name from your tenant directory, never from raw input.
• pg_dump -n exports one schema, but "makes no attempt to dump any other database objects that the selected schema(s) might depend upon", so check restores. The manual also calls pg_dump "generally not the right choice for taking regular backups of production databases".

The cost is operational: every migration runs once per tenant. Microsoft warns against per-tenant tables in one database ("table-based isolation") because it becomes "increasingly difficult to query, manage, and update". Identical schemas avoid the worst of that, but object counts still grow with tenants. The schema page states no limit, so measure migration time and catalog size at your target tenant count.

Database per tenant: a database each

Microsoft lists the benefits as data isolation and some room for per-tenant customisation, with high density for the non-data components because only the data tier is duplicated. The cost is dedicated resources per tenant, partly offset by pooling features such as elastic pools where a platform has them. Its note: automate provisioning, or "the complexity of manually deploying and managing the databases becomes overwhelming."

It suits tenants who need their own encryption keys, their own backup and restore policy, or data in a particular geography; Microsoft names those three as reasons to isolate a tenant. A shared application tier with separate databases also keeps one tenant's load from degrading other tenants' databases.

Finding the tenant: from request to data

Every request needs two answers: which tenant is this, and where does that tenant's data live? Microsoft separates tenant identification from a tenant mapping layer that points to a deployment.

It lists these ways to identify a tenant: a domain or subdomain, a path segment, a query string, a custom header, a token claim, an API key or a client certificate. Each has a catch: custom headers are unreliable for browser GET requests, API keys are long-lived and carry no claims, and a claim needs a rule for users in several tenants. Whatever you pick, validate it: a tenant domain in the request "doesn't mean you should automatically grant access".

Figure 2. One request from token to rows. How to read this: the tenant id is verified at the edge, never taken from the request body. The directory answers the second question, so moving a tenant changes one directory row, not the application.

The directory also holds the per-tenant schema version, which Microsoft says to track "in a dedicated database or lookup table". Mapping runs on every request, so Microsoft suggests caching (for example a session cookie); a cache needs invalidation when a tenant moves.

CREATE TABLE tenant_directory (
  tenant_id      text PRIMARY KEY,
  model          text    NOT NULL CHECK (model IN ('pooled', 'schema', 'database')),
  location       text    NOT NULL,
  schema_version integer NOT NULL
);

Authentication is not isolation

The AWS whitepaper puts it plainly: "the fact that a tenant user is authenticated does not mean that your system has achieved isolation". A token's role can gate features and still let a user reach another tenant's rows if nothing scopes the query. Isolation "focuses exclusively on using tenant context to limit access to resources", for every user of the tenant. Dedicated stacks scope by network and account boundaries; pooled storage needs finer controls such as RLS or an enforced repository layer. Pick enforcement that fails closed, then test it by trying to cross the boundary.

Mixing models: tiers and moves

Microsoft's vertical partitioning mixes models: most tenants on shared infrastructure, a few on dedicated deployments. The cost is that the codebase must support both and you must know which tenant is where.

Figure 3. A tenant's lifecycle. How to read this: the two arrows out of Moving matter most. A move is safe only if you can abandon it with the directory unchanged. Microsoft advises automating onboarding, migration, retention and offboarding.

A move from pool to dedicated database is a copy plus a directory switch, with writes paused or replicated for the short cutover.

Choosing a model

Figure 4. A decision, not a rule. How to read this: the first question is about isolation requirements, which no tuning can satisfy inside a shared model. "Moderate" is a prompt to measure your own migration time, not a limit.

The other side of this topic

On the front end, a multi-tenant product also separates branding, configuration and routes per tenant, and must never serve one tenant's cached response to another. That is a planned lesson in the Frontend System Design track; it is not published, so there is no link.

In practice: one grocery chain asks for more than the pool gives

This is the realistic example on ShopStream. Every figure except ShopStream's shared 5,000 stores is illustrative, and nothing describes a real company.

This is a realistic scenario, not a case history.

The situation

ShopStream keeps the stock of its 5,000 stores in one PostgreSQL database with a store_id column and RLS, as Stage 8 decided. A chain that runs 40 of those stores (illustrative) signs on and asks for three things: its data under its own encryption keys, a point-in-time restore that touches no one else, and its data in one region.

Starting architecture

Figure 5. The starting point. How to read this: all stores share one database, which cannot meet the chain's three requests: Microsoft names own keys, separate restore and location as reasons to move a tenant out of shared storage.

Options considered

The decision

Option C, the horizontal partition Microsoft describes: the application tier stays shared and only the data tier is dedicated. The directory gets a row mapping the chain to its database; other stores stay pooled. One pipeline runs migrations against the pool and each dedicated database, and the application tolerates one version of schema skew.

Rollout and verification

Follow Figure 3: copy the chain's rows, compare row counts and a per-table checksum, pause writes for the cutover, switch the directory, and keep the old rows until the rollback window ends. Verify isolation by test, as Microsoft recommends: as store 17, try to read store 42's rows in every table and expect none, in both models. Watch directory cache hit rate and per-tenant error rate.

The 2 a.m. failure

The page reads: errors on qty_reserved for part of the fleet. A migration added that column to the pool, then to the chain's database, and the pipeline stopped when a connection timed out; the new application version assumed the column everywhere. The first check is schema_version grouped by location. The mitigation is to roll the application back, which works only if the old version still runs on the new schema. The lasting fix is Microsoft's advice on version dependencies: stay compatible with at least one previous schema version and sequence destructive changes over several releases.

Architect's notes

• Start from requirements. Own keys, per-tenant restore and location rule out shared models before cost matters.
• One codebase. Microsoft advises against forking infrastructure per tenant; use feature flags and tenant configuration.
• Build the directory early. Later moves become data changes.
• Automate the fleet and record the schema version per tenant.
• Test the boundary in every model you run.

Common mistakes

• Treating authentication as isolation.
• Trusting a tenant id from the request body.
• Connecting as the table owner or a BYPASSRLS role, then wondering why policies do nothing.
• Session-level tenant context on pooled connections.
• Schema-per-tenant without migration automation.
• Moving a tenant without a way back.

Key takeaways

• A tenancy model decides what is shared; isolation is a spectrum.
• Shared schema is cheapest and needs enforced filters; RLS is a backstop with documented gaps.
• Schema per tenant eases export and multiplies migrations; database per tenant isolates most and enlarges the fleet.
• Own keys, per-tenant restore and data location push a tenant to a dedicated database.
• Authentication is not isolation: scope every query by verified tenant context.
• A tenant directory makes moves a data change; keep the application compatible with one previous schema version.

Practice exercises

Exercise 1 (30 min). For ShopStream's stock table, write three queries that would leak data without a tenant filter, and the policy that blocks each.

Exercise 2 (45 min). Sketch the tenant_directory for 5,000 pooled stores plus one dedicated chain and trace a request for each.

Exercise 3 (1 hour). Plan a column addition across 300 dedicated databases (illustrative): version tracking, halt rule, rollback.

Exercise 4 (1 hour). Use Figure 4 for a clinic scheduler, a note-taking app and a payroll product, and justify each choice.

Exercise 5 (2 hours). Design the pool-to-dedicated move for one tenant: copy, verify, cutover, rollback, and how long writes pause.

Related reading

• ShopStream Stage 8: Services: the first pass at pooled tenancy.
• Microservices decomposition and data ownership: who owns which data.
• Databases basics and Sharding: relational fundamentals and spreading data over databases.
• API gateway and service mesh: where tenant identity is verified.

Official documentation

• PostgreSQL 18 manual: Row security policies, Schemas, System administration functions (set_config, current_setting), pg_dump.
• Microsoft Azure Architecture Center: Tenancy models for a multitenant solution, Storage and data in multitenant solutions, Map requests to tenants.
• AWS, SaaS Architecture Fundamentals: Tenant isolation.