Building Multi-Tenant SaaS on Postgres: Schemas, RLS, and Pooling

Multi-tenancy is the architectural decision that haunts B2B SaaS the longest. Get it wrong early and you're refactoring during your Series A. Get it right and you have a system that scales from one customer to ten thousand without a re-platform.

There are three real patterns on Postgres. None of them is universally right. We'll cover the trade-offs, the RLS implications, and the operational realities of each, all with Supabase as the implicit platform because that's where most teams shipping in 2026 start.

The three patterns#

1. Shared table + tenant_id column. Every row carries a tenant_id. RLS filters on it. One database, one schema, one set of tables.
2. Schema per tenant. Each tenant gets a Postgres schema with identical table structure. The application sets search_path per request.
3. Database per tenant. Each tenant gets their own Postgres database (or Neon / Supabase project). Connection routing per request.

That's it. People will try to invent a fourth; usually it's a hybrid of two of the above.

Pattern A: shared table + tenant_id#

This is what 80% of teams should ship. The shape:

-- Tenants live in their own table.
CREATE TABLE tenants (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name        text NOT NULL,
  created_at  timestamptz DEFAULT now()
);

-- Every business entity carries a tenant_id.
CREATE TABLE projects (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name        text NOT NULL,
  created_at  timestamptz DEFAULT now()
);
CREATE INDEX projects_tenant_idx ON projects (tenant_id);

-- Membership table to map users to tenants.
CREATE TABLE memberships (
  tenant_id  uuid NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id    uuid NOT NULL,
  role       text NOT NULL DEFAULT 'member',
  PRIMARY KEY (tenant_id, user_id)
);

-- RLS that tells Postgres "this user can only see their tenant's projects".
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Members read projects" ON projects FOR SELECT TO authenticated
  USING (tenant_id IN (
    SELECT tenant_id FROM memberships WHERE user_id = auth.uid()
  ));
CREATE POLICY "Members write projects" ON projects FOR ALL TO authenticated
  USING (tenant_id IN (
    SELECT tenant_id FROM memberships WHERE user_id = auth.uid()
  ))
  WITH CHECK (tenant_id IN (
    SELECT tenant_id FROM memberships WHERE user_id = auth.uid()
  ));

What this pattern gets you:

• One schema. Migrations apply once.
• Reporting and analytics over "all tenants" is a single SQL query.
• Connection pooling is straightforward, every request hits the same pool.
• You can move a tenant to another shard later by introducing a routing layer; the schema stays the same.

The trade-offs you actually feel:

• A bug in your RLS policy is a cross-tenant leak. The blast radius is higher than schema- or database-per-tenant.
• Noisy-neighbour performance. A tenant with 100x your normal data volume can saturate the indexes. tenant_id partitioning mitigates but doesn't eliminate it.
• You can't hand a customer a pg_dump of their data without filtering, which sometimes matters for compliance audits.

Pattern B: schema per tenant#

Postgres lets you have many schemas in one database. With schema- per-tenant, every tenant gets, say, tenant_abc.projects, tenant_xyz.projects, etc. Routing happens via search_path:

-- Provision a new tenant.
CREATE SCHEMA tenant_acme;
GRANT USAGE ON SCHEMA tenant_acme TO app_user;

-- Run your normal migrations *into the schema*.
ALTER ROLE app_user IN DATABASE postgres SET search_path = tenant_acme, public;

-- Or per-request:
SET LOCAL search_path = tenant_acme, public;
SELECT * FROM projects; -- resolves to tenant_acme.projects

Wins:

• Per-tenant pg_dump works. You can give a customer their data as a SQL file.
• Indexes are per-tenant, so a giant tenant doesn't bloat shared indexes.
• Schema-level GRANTs give a real second line of defence below RLS.

Losses:

• Migrations apply N times (once per tenant). Tooling has to know this; you can't just point Drizzle at a single schema and call it done.
• Cross-tenant queries (admin reporting) become a UNION ALL with N arms. Past a few hundred tenants this is unworkable.
• PostgREST and Supabase's realtime/storage assume schemas they know about. Custom schemas need explicit allow-listing.
• Postgres performance starts to suffer past ~5000 schemas in one database. pg_dump alone takes minutes.

We've seen schema-per-tenant work great for B2B products with up to a few hundred tenants. Past that, the operational headaches stack up.

Pattern C: database per tenant#

The hardest mode, the strongest isolation.

Each tenant gets their own Postgres database. In Supabase terms, that's one Supabase project per tenant. Routing happens at the application layer: a tenant lookup decides which connection string to use.

// Pseudo-code: given a subdomain or path prefix, look up the tenant's
// connection string and hand it to your request scope.
const url = await tenantsTable.get(tenantId).connectionString;
const sql = postgres(url, { max: 5, prepare: false });
const rows = await sql`SELECT id, name FROM projects ORDER BY created_at DESC`;
await sql.end();

Where this wins:

• Cross-tenant leaks are structurally impossible. A bug in your code can't leak a customer's data to another customer.
• Per-tenant compliance: HIPAA, data residency, "my data on my server" for enterprise contracts.
• Per-tenant scaling. A giant customer gets their own large database; a tiny customer gets a tiny one.

Where it hurts:

• Pooling per tenant is expensive. If you have 1000 tenants and each pool holds 10 connections, you're looking at 10,000 connections. You need a smart router (PgCat, Supavisor in transaction mode) and a tiny pool per tenant.
• Migrations run N times. You need orchestration. Tools like Liquibase, Bytebase, or a homegrown runner that knows about your tenant list.
• Provisioning a tenant takes seconds-to-minutes (CREATE DATABASE, apply schema, seed data) instead of being a single row insert.

For Supabase specifically, "database per tenant" usually means "Supabase project per tenant". This is what some agencies do for clients who need full isolation. The admin overhead of managing dozens of dashboards is significant, which is why multi-project admin is one of the most common reasons people pick our workspace.

How to choose#

Use this priority order:

1. Default to shared table + tenant_id. Switch away only when a concrete pain forces you.
2. Pick schema per tenant if your tenants are large but few (think: enterprise SaaS, <500 tenants total), regulatory requirements push you toward stronger isolation, OR you genuinely need per-tenant pg_dump.
3. Pick database per tenant if you have hard compliance requirements (HIPAA, data residency per customer), OR you have tenants whose individual data volume is large enough to justify their own database, OR you're explicitly pricing on "dedicated infrastructure".

RLS trapdoors specific to multi-tenant#

In shared-table mode, RLS is your isolation. The bugs that bite teams here are:

• A policy that joins through memberships but doesn't force the join to be evaluated per row, allowing the planner to materialise the whole memberships table and leak it via timing.
• A function used in a policy that's declared SECURITY DEFINER and forgets to filter by tenant_id. Now the function leaks across tenants.
• Background jobs running as service_role that touch many tenants' data and forget to set tenant_id explicitly. The audit log shows the writer as service_role, which is opaque after the fact.

Test the negative case in CI. The RLS guide has the exact transaction pattern.

Pooling implications#

Pooling matters more in multi-tenant systems than in single-tenant ones, because the number of distinct "connections you need to keep warm" multiplies by tenant count.

• Shared table: one pool. Sized to your aggregate concurrent-request rate. Easiest.
• Schema per tenant: one pool. SET LOCAL search_path per request stays within the same connection.
• Database per tenant: one pool per tenant. Use transaction-mode pooling so you can keep each pool tiny (2-5 connections), and a router in front (Supavisor in transaction mode or PgCat in proxy mode).

See our pooling guide for the specific configurations.

Operating each shape#

Whichever shape you pick, you'll spend more time looking at production data than you expect. Some Day-2 questions to design for:

• How do you see one tenant's data? In shared mode this is a filter. In schema mode this is SET search_path. In database mode this is a separate connection.
• How do you delete a tenant? Shared: DELETE FROM tenants with cascading FKs. Schema: DROP SCHEMA tenant_x CASCADE. Database: DROP DATABASE tenant_x.
• How do you audit cross-tenant writes? The audit log should record tenant_id explicitly (even when RLS enforces it), because your service_role admin tasks bypass RLS and you want to know which tenant they touched.

These are also exactly the questions an admin tool needs to answer. For shared-table tenants, Suparbase filters every list by tenant with a saved view; for schema-per-tenant, you switch the connection'ssearch_path setting; for database-per-tenant, you keep each project as a separate connection in your workspace. All three are valid; the shape that matches your tenancy model is the right one.

The thing not to do is try to support all three at once. Pick the pattern that fits your business shape, build for it, and revisit when the pain tells you to.