Skip to content
Tutorials Center
My learning

Topics

Lesson 84 of 125 ยท Designing Databases

Multi-Tenant Schema Design

Design a SQL database that serves many customers safely. Compare shared tables, separate schemas and separate databases, and enforce tenant isolation with keys.

Imagine you are building an invoicing app that hundreds of companies will use. Each company, called a tenant, must see only its own customers and invoices, yet you do not want to run hundreds of separate copies of the whole system. An application that serves many tenants from one deployment is multi-tenant, and the database design behind it is one of the earliest decisions you make. It is hard to change later.

There are three classic strategies. They differ in how strongly tenants are separated, how much they cost to run, and how painful it is to change the schema. In this lesson you will compare them, build the most common one (shared tables with a tenant key) and learn how to make it hard to leak data between tenants.

Three ways to separate tenants

flowchart TB
    subgraph S1["1. Shared tables"]
        T1["invoices<br/>tenant_id column"]
    end
    subgraph S2["2. Schema per tenant"]
        A2["acme.invoices"]
        B2["globex.invoices"]
    end
    subgraph S3["3. Database per tenant"]
        A3["acme_db: invoices"]
        B3["globex_db: invoices"]
    end

Strategy 1 puts everyone in the same tables; strategies 2 and 3 give each tenant its own copy of the tables.

Shared tables Schema per tenant Database per tenant
Isolation Weakest (one missing WHERE leaks) Good Strongest
Cost per tenant Lowest Medium Highest
Tenants supported Millions Thousands Hundreds, maybe thousands
Schema change Once Once per tenant Once per tenant
Cross-tenant reports Easy Awkward Hard
Per-tenant backup or restore Hard Medium Easy
Noisy neighbors Possible Possible Isolated

A schema here means a namespace inside a database (PostgreSQL, SQL Server and others support them; MySQL uses "database" for the same idea, and SQLite has neither). Shared tables are the default choice for software-as-a-service products with many small tenants. Separate schemas or databases make sense when tenants are few, large, or have legal needs such as data living in a particular country or being deleted and restored on their own.

Many real systems mix strategies: shared tables for the thousands of small tenants, and a dedicated database for the one big enterprise customer.

Shared tables with a tenant key

In the shared design, every table that holds tenant data gets a tenant_id column. Every query must filter on it.

SQL One set of tables, many tenants

Press Run (or Ctrl + Enter) to see the result here.

Two design choices here matter.

Primary keys start with the tenant. The key is (tenant_id, id), so each tenant can number its invoices from 1 and the ids are only unique inside a tenant. This also puts a tenant's rows next to each other in the primary-key index. The same rule applies to other indexes: nearly every query filters by tenant first, so make tenant_id the leading column. See Indexes and Advanced Indexing for why column order matters.

Joins include tenant_id. Joining only on customer_id would be wrong, because customer 1 exists in every tenant. Always join on the whole key.

Whether to use a globally unique id (a sequence or UUID) instead of a per-tenant counter is a matter of taste. Globally unique ids make accidental cross-tenant joins fail loudly instead of returning plausible wrong rows. Per-tenant counters give tidy invoice numbers. Either way, keep tenant_id in the key.

Making cross-tenant mistakes impossible

The scary failure is a row in tenant 1 that points at a customer owned by tenant 2. A plain foreign key on customer_id cannot stop it, because it only checks that some customer with that id exists. The fix is a composite foreign key that includes the tenant:

SQL Composite foreign keys enforce the boundary

Press Run (or Ctrl + Enter) to see the result here.

The first insert works. The second is rejected with a foreign key error, because there is no customer (1, 2). The database now enforces the tenant boundary on writes, no matter how careless the application code is. Use this pattern on every child table (see Foreign Keys).

Making reads safe

Writes can be checked by constraints, but reads depend on a WHERE tenant_id = ? that someone might forget. There are two common safeguards.

1. Scoped views. Give application code views that only show the current tenant. Real databases let you read a per-session setting inside the view; SQLite has no sessions, so this example fakes one with a one-row table:

SQL A view that shows only the current tenant

Press Run (or Ctrl + Enter) to see the result here.

The query code never mentions the tenant, and it cannot see anyone else's rows. See Views.

2. Row-level security. PostgreSQL (and SQL Server, with a different syntax) can attach the rule to the table itself, so even a direct SELECT * FROM invoices is filtered. This snippet is read-only; it needs a server database:

SQL
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::int);

-- The application runs this once per request or transaction:
SET app.tenant_id = '42';

With the policy in place, a forgotten WHERE returns only tenant 42's rows. Pair it with a database role that is not the table owner, since owners bypass the policy by default. For role setup see Users and Permissions.

Isolation bugs are security bugs. Showing one customer another customer's invoices is a data breach, not a cosmetic issue. Test it: write automated tests that act as tenant A and try to read or write tenant B's data through every endpoint.

Schema or database per tenant

With separate schemas, each tenant gets its own tables inside one database, and the connection chooses the tenant by setting its search path (SET search_path TO acme in PostgreSQL). Queries need no tenant_id filter at all. The price is operational:

  • A migration must be run once per tenant, and a failure halfway leaves tenants on different versions.
  • Thousands of schemas mean hundreds of thousands of tables, which slows catalog lookups, backups and tools.
  • Reporting across tenants needs a loop over schemas or a generated UNION ALL.

A separate database per tenant goes further: separate files or servers, independent backups and restores, and a simple way to move a large tenant to its own hardware. Connection management gets harder, since you need a pool per database (see Connection Pooling and Plan Caching).

Other design points

  • Unique constraints are per tenant. An email may be unique within a tenant but not across tenants: UNIQUE (tenant_id, email).
  • Deleting a tenant is one DELETE ... WHERE tenant_id = ? per table with shared tables (use ON DELETE CASCADE carefully), or DROP SCHEMA / DROP DATABASE with separation. Plan for exports on request.
  • Noisy neighbors. One huge tenant can slow everyone in shared tables. Partitioning by tenant (see Table Partitioning and Tablespaces) or moving that tenant to a dedicated database are the usual remedies.
  • Per-tenant settings belong in a tenants table or a tenant_settings table, not hard-coded in the application.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which multi-tenant strategy is cheapest per tenant and scales to millions of small tenants?

    Shared tables avoid duplicating tables and connections per tenant, so cost per tenant is lowest. The tradeoff is weaker isolation.
  2. 2. In a shared-table design, why should the primary key be (tenant_id, id) rather than just id?

    A composite key lets every tenant number its rows independently and clusters each tenant's rows in the index. It does not make ids globally unique.
  3. 3. A table has FOREIGN KEY (customer_id) REFERENCES customers (id), where customers has a composite key (tenant_id, id). What is wrong with this design?

    A single-column foreign key checks only that some customer with that id exists. Including tenant_id in the foreign key ties both rows to the same tenant.
  4. 4. What does row-level security add to a shared-table design?

    A row-level security policy is applied by the database to every query on the table, so a missing tenant filter in application code no longer leaks data.
  5. 5. Which is a real downside of schema-per-tenant?

    Each tenant has its own copy of the tables, so migrations run once per tenant, and thousands of tenants make that slow and error-prone.

Summary

  • Multi-tenant means one deployment serving many customers. The main strategies are shared tables, a schema per tenant, and a database per tenant.
  • Shared tables are cheapest and easiest to migrate but need discipline: put tenant_id in every table, every key, every join and the front of every index.
  • Composite foreign keys that include tenant_id make cross-tenant references impossible at the database level.
  • Scoped views and row-level security protect reads when application code forgets a filter.
  • Separate schemas or databases give stronger isolation and easy per-tenant restore, at the price of per-tenant migrations and connection management.
  • Unique constraints, deletion, and noisy neighbors all need a tenant-aware plan.

Next: Reading Query Plans, where the Performance chapter begins.