Multi-tenant database design: shared tables, schema per tenant, or database per tenant

For most SaaS products, the right multi-tenant database design is shared tables with a tenant id on every tenant-owned row, backed by PostgreSQL row-level security. Give a tenant its own database later, when a specific customer needs it for compliance, size or contract reasons. Schema per tenant sits in between, and it's the right choice less often than its popularity suggests.

That's the short answer. The rest of this post covers when each model wins, and then the parts most articles skip: how to lay out the tables so tenant isolation holds, measured on the multi-tenant schemas in the drawDB gallery, and a row-level security setup we ran against PostgreSQL 16 to confirm it actually isolates tenants.

What are the three multi-tenant database models?

Microsoft's SaaS tenancy patterns guide has the same trade-offs at more length. The practical summary is that the operational cost of the last two columns grows with the number of tenants, and the first doesn't.

Many products end up hybrid: shared tables for the long tail, and a dedicated database for the handful of enterprise customers whose contract requires one. That only stays cheap if the shared model was designed so a tenant can be lifted out cleanly, which is what the rest of this post is about.

Should every table have a tenant_id column?

Every table that holds tenant-owned rows should, even when the tenant could be derived through a parent. The gallery shows both styles, and the difference is instructive:

The Slack- and Notion-style schemas derive the tenant through parents: a message belongs to a channel, which belongs to a workspace. That's normalized and fine for a schema reference. For a production SaaS, put the key on the message too, for three reasons:

1. One policy for every table. Row-level security can then be the same one-line check everywhere, rather than a subquery that walks up the hierarchy on every row.
2. Indexes that start with the tenant. Almost every query filters by tenant, so (workspace_id, channel_id, created_at) serves them directly.
3. Tenant moves and deletes are a filter. Exporting, deleting or relocating a tenant becomes WHERE workspace_id = 42 on each table instead of a join chain.

What stays global is the identity layer. In the gallery's SaaS schema, users has no organization_id: a person has one account and joins many organizations through memberships, which carries the role. Billing plans and other reference data are global too.

SaaS

Multi-tenant SaaS database schema

Shared-database multi-tenancy done properly: organizations as tenants, global users joined through memberships, invitations, scoped API keys, plans, subscriptions, metered usage and an append-only audit log.

How do you stop one tenant's rows from referencing another tenant's?

Use composite foreign keys that include the tenant id. A plain project_id foreign key only proves the project exists, not that it belongs to the same tenant as the task pointing at it. A bug, or a crafted API request, can then attach tenant A's task to tenant B's project, and the database will accept it.

None of the 94 foreign keys on tenant-owned tables in these six gallery schemas does this; every one references the parent's id alone. It's the most common gap in multi-tenant schemas, ours included, and it closes with a unique constraint and a wider foreign key:

CREATE TABLE "organizations" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" VARCHAR(160) NOT NULL
);

CREATE TABLE "projects" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "organization_id" BIGINT NOT NULL REFERENCES "organizations" ("id"),
  "name" VARCHAR(160) NOT NULL,
  UNIQUE ("organization_id", "id")
);

CREATE TABLE "tasks" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "organization_id" BIGINT NOT NULL REFERENCES "organizations" ("id"),
  "project_id" BIGINT NOT NULL,
  "title" VARCHAR(200) NOT NULL,
  FOREIGN KEY ("organization_id", "project_id")
    REFERENCES "projects" ("organization_id", "id") ON DELETE CASCADE
);

CREATE INDEX "idx_tasks_org_project" ON "tasks" ("organization_id", "project_id");

With this in place, inserting a task whose organization_id doesn't match its project's fails with a foreign key violation. We tested that case against the schema above. If you later shard with Citus, the composite form is also what the distributed data model expects, since unique constraints and foreign keys between distributed tables have to include the distribution column.

Which unique constraints should include the tenant id?

Anything a customer chooses or sees: slugs, project keys, invoice numbers, a member's email within a workspace. Two tenants must be able to have a project called api or an invoice numbered INV-0001.

In the six schemas above, 13 of the 14 multi-column unique constraints on tenant tables include the tenant key. The exception, slots (provider_id, starts_at), is scoped through the provider, who belongs to one clinic. Of the 13 single-column unique constraints, 12 are deliberately global: invitation token hashes, API key hashes and payment-provider references. Those identify one thing across the whole system, so they should stay globally unique. The last one, appointments.slot_id, enforces one booking per slot.

CREATE TABLE "invoices" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "organization_id" BIGINT NOT NULL REFERENCES "organizations" ("id"),
  "number" VARCHAR(40) NOT NULL,
  "provider_reference" VARCHAR(80) UNIQUE,
  UNIQUE ("organization_id", "number")
);

The test for each column: should two different customers be able to hold the same value? If yes, scope it to the tenant. If no, keep it global.

How should you index a multi-tenant schema?

Lead with the tenant id. In the six schemas above, 26 of the 45 non-unique indexes on tenant tables start with the tenant key. The other 19 fall into three groups, and each is a legitimate exception:

• Lookups through a parent that already belongs to one tenant, like charges (payment_intent_id) or product_variants (product_id). The parent id already pins the tenant.
• Background jobs that sweep every tenant, like idempotency_keys (expires_at) or disputes (evidence_due_by).
• Questions about a person across tenants, like workspace_members (user_id): which workspaces does this user belong to?

Leading with the tenant does two things. Every query that filters by tenant (almost all of them) can use the index directly. And per-tenant operations like exports, deletes and usage counts stay index range scans, no matter how many other tenants share the table.

How do you enforce tenant isolation with row-level security?

Application code should still filter by tenant, but one forgotten WHERE clause leaks another customer's data. Row-level security moves the rule into the database, so a missing filter returns nothing instead of everything. This is the setup we ran against PostgreSQL 16:

ALTER TABLE "projects" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "projects" FORCE ROW LEVEL SECURITY;

CREATE POLICY "tenant_isolation" ON "projects"
  USING ("organization_id" =
    NULLIF(current_setting('app.organization_id', true), '')::bigint)
  WITH CHECK ("organization_id" =
    NULLIF(current_setting('app.organization_id', true), '')::bigint);

CREATE ROLE "app_user" NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON "projects" TO "app_user";

Then, per request, inside the transaction that serves it:

SELECT set_config('app.organization_id', '42', true);

In our test, the app_user role saw only organization 42's projects, got zero rows (not an error) when no organization was set, and was refused by the WITH CHECK clause when it tried to insert a project for organization 7. Three details decide whether this works in production:

• Connect as a role that owns nothing. Superusers and roles with BYPASSRLS skip policies, and so do table owners unless the table has FORCE ROW LEVEL SECURITY (PostgreSQL docs). If your app connects as the role that ran the migrations, the policy does nothing.
• Set the tenant per transaction, not per session. The third argument true makes set_config transaction-local. A session-level setting survives on a pooled connection and can serve the next request with the previous tenant's id. PgBouncer in transaction mode makes transaction-local settings the only safe option.
• Fail closed. NULLIF(current_setting(..., true), '') turns a missing tenant into NULL, which matches no rows, rather than an error you might catch and retry without the setting.

When is schema per tenant the right choice?

When tenants need structural differences, like custom fields as real columns or per-customer extensions, and you have tens or hundreds of tenants rather than tens of thousands. It gives you per-tenant pg_dump -n backups and an easy answer when a customer asks where their data lives.

CREATE SCHEMA "tenant_acme";
CREATE TABLE "tenant_acme"."projects" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" VARCHAR(160) NOT NULL
);
SET LOCAL search_path TO "tenant_acme";

The costs grow with the tenant count. Every migration runs once per schema and can half-fail, leaving tenants on different versions. Every table and index is duplicated in the system catalogs, so planning and tools like pg_dump slow down as schemas multiply. Every request has to set search_path correctly, which is the same pooled-connection hazard as the RLS setting above, but with no policy to catch a mistake.

When do you need a database per tenant?

When isolation is a requirement rather than a preference: data-residency rules that put a customer's rows in a specific region, contracts that require dedicated infrastructure, or a tenant big enough to need its own hardware. Keep the routing in your shared control-plane database:

CREATE TABLE "tenant_placements" (
  "organization_id" BIGINT PRIMARY KEY REFERENCES "organizations" ("id"),
  "cluster" VARCHAR(40) NOT NULL DEFAULT 'shared-eu-1',
  "region" CHAR(2) NOT NULL DEFAULT 'eu',
  "moved_at" TIMESTAMPTZ
);

With a tenant id on every row, moving one customer from shared-eu-1 to a dedicated cluster is a filtered copy plus one update to this table. That's the payoff of designing the shared model carefully: you rarely have to choose an isolation model once and for all, because a single tenant can be moved later.

Fintech

Stripe-style payments platform database schema

How a payment processor models its data: merchant accounts, tokenised payment methods, the payment intent state machine, charges and partial refunds, chargebacks with evidence deadlines, payouts, webhook deliveries and idempotency keys.

Start from a working schema

The gallery's multi-tenant schemas are public domain. Open SaaS multi-tenant or Shopify-style multi-store in the drawDB editor, add the composite keys and policies above, and export the SQL for your database.

Frequently asked questions