Multi-Tenant Postgres for SaaS: Patterns That Scale

For most SaaS apps, the best multi tenant Postgres design is a shared set of tables with a tenant_id (or org_id) column on every tenant-owned row, enforced by Row Level Security. It is the simplest to migrate, query and operate, and it scales to many thousands of tenants. Schema-per-tenant and database-per-tenant designs give stronger isolation, but each adds operational work that grows with the number of customers.

This article compares the three patterns, then goes deep on the shared-table pattern: schema, policies, indexes, and the operational habits that keep it healthy.

#Three multi tenant Postgres patterns

#Shared tables

Every tenant-owned table has a tenant column. Every query filters on it. RLS makes the filter mandatory so a missing where clause does not leak data.

Pros: one schema, one migration path, one connection pool, simple analytics across tenants. Cons: one big tenant can affect others (noisy neighbor), and per-tenant restore or deletion means working row by row.

#Schema per tenant

Each tenant gets its own schema with identical tables: tenant_acme.orders, tenant_globex.orders. The app sets search_path per request.

Pros: clean separation, easy per-tenant dump. Cons: migrations must loop over every schema, and the system catalog grows with every tenant. Thousands of schemas times dozens of tables make pg_dump, autovacuum and some tools slow. Also, search_path per request does not play well with transaction-mode poolers unless you use set local.

#Database per tenant

Each tenant gets its own database or even server.

Pros: strongest isolation, per-tenant backups, region choice per tenant, no noisy neighbors. Cons: you now operate N databases: provisioning, migrations, monitoring, connection limits and cost for each.

A common hybrid: shared tables for most customers, a dedicated database for the few enterprise customers who need it contractually.

#Building the shared-table pattern

#Schema

create table public.orgs (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz not null default now()
);

create table public.org_members (
  org_id uuid not null references public.orgs on delete cascade,
  user_id uuid not null references auth.users on delete cascade,
  role text not null default 'member' check (role in ('owner', 'admin', 'member')),
  primary key (org_id, user_id)
);
create index on public.org_members (user_id);

create table public.projects (
  id bigint generated always as identity primary key,
  org_id uuid not null references public.orgs on delete cascade,
  name text not null,
  created_at timestamptz not null default now()
);
create index on public.projects (org_id, created_at desc);

Two rules:

1. Put org_id on every tenant-owned table, even child tables where you could derive it through a join. Denormalizing it keeps policies simple and lets every index lead with the tenant.
2. Lead indexes with org_id. Almost every query is "rows of this tenant, sorted or filtered by something". A composite index (org_id, created_at) serves that directly.

#Composite foreign keys

To make it impossible to attach a task in org A to a project in org B, include the tenant in foreign keys:

alter table public.projects add unique (org_id, id);

create table public.tasks (
  id bigint generated always as identity primary key,
  org_id uuid not null,
  project_id bigint not null,
  title text not null,
  foreign key (org_id, project_id) references public.projects (org_id, id) on delete cascade
);

#Policies

A helper function keeps policies readable and fast:

create function public.is_org_member(o uuid) returns boolean
language sql stable security definer set search_path = ''
as $$
  select exists (
    select 1 from public.org_members
    where org_id = o and user_id = (select auth.uid())
  )
$$;

alter table public.projects enable row level security;

create policy "members read" on public.projects
  for select to authenticated using (public.is_org_member(org_id));

create policy "members write" on public.projects
  for insert to authenticated with check (public.is_org_member(org_id));

Repeat for every tenant table. Add a role check for destructive actions (only admins delete).

#Using Organizations on StitchDB

If you build on StitchDB, you can skip writing this layer yourself. Turning on Organizations under Authentication › Organizations installs organization, member and invite tables, invite e-mails, functions your app calls (org_create, org_invite, org_accept_invite, org_set_role), JWT claims (org_id, org_role, orgs) and policy helpers:

create table projects (
  id bigint generated always as identity primary key,
  org_id uuid not null,
  name text
);
alter table projects enable row level security;

create policy "members read" on projects
  for select to authenticated using (auth.is_member(org_id));

create policy "admins write" on projects
  for all to authenticated
  using (auth.has_role('admin', org_id))
  with check (auth.has_role('admin', org_id));

From the app:

const org = await stitchdb.orgs.create({ name: 'Acme', slug: 'acme' })
await stitchdb.orgs.invite(org.id, { email: '[email protected]', role: 'admin' })
await stitchdb.orgs.setActive(org.id) // next token carries org_id and org_role

The helpers look up membership live, so removing a member takes effect immediately even if they still hold an older token. Use the JWT claims for UI decisions (which org is active), and the helpers in policies.

#Testing isolation

Isolation bugs are the worst kind of SaaS bug. Test them deliberately:

• Create two orgs with one user each. As user A, try to read, update and delete org B's rows through the API. Expect zero rows and rejected writes.
• Try to insert a row with org B's org_id as user A. Expect with check to reject it.
• Run these checks in CI against a preview database.

StitchDB's Check access in Studio runs every policy as a chosen user for a chosen row and explains the result, and the Exposure check shows what an anonymous visitor and a random signed-in user can read and write.

#Operating a shared-table database

#Noisy neighbors

One large tenant can dominate CPU or I/O. Mitigations:

• Per-tenant rate limits at the API layer.
• Statement timeouts so one runaway report cannot hold resources.
• Move heavy reporting to a read replica.
• For the largest tenants, consider a separate project or server.

#Large tables

When event or log tables grow past tens of millions of rows, partition them by time (and keep org_id as the leading index column inside each partition). StitchDB's docs cover native partitioning with pg_partman; see Partitioning.

#Deleting a tenant

With on delete cascade from orgs, deleting one org removes its rows. On big tenants that is a large transaction; delete in batches from the largest tables first, then delete the org.

#Per-tenant export

Customers will ask for their data. Write a script that exports each tenant table filtered by org_id, for example with \copy (select * from public.projects where org_id = '...') to 'projects.csv' csv header.

#Migrations

Shared tables mean one migration for everyone, which is the pattern's biggest operational win. Keep migrations backward compatible (add, backfill, then drop later), and use non-blocking forms such as create index concurrently on large tables. See Safe schema changes.

#When to choose something else

Pick schema or database per tenant when:

• Contracts require physical separation or a tenant-specific region.
• Tenants need custom columns or extensions you cannot share.
• You need per-tenant point-in-time restore without affecting others.

On StitchDB, the database-per-tenant pattern maps to a project per tenant, each with its own database, keys and backups. That is workable for a handful of large customers, and the Management API can provision projects. For thousands of small tenants, shared tables are the better fit.

#FAQ

#Is Row Level Security enough to isolate tenants?

For most SaaS apps, yes, when every tenant table has RLS on, policies are tested, and the service_role key stays on servers. Add database-per-tenant only for contractual or regulatory needs.

#Should I use the tenant id from the JWT or a lookup?

Use a live membership lookup in policies. JWT claims can be stale until the token refreshes.

#How do I avoid forgetting org_id in queries?

RLS enforces it for API requests. For server code, connect with a role that is subject to RLS, or set the tenant per transaction and reference it in policies.

#Does schema-per-tenant work with connection pooling?

Only with care: set search_path with set local inside each transaction in transaction mode.

Build your SaaS on Postgres with organizations, RLS and APIs included: start free, or read the RLS guide.