Multi-Tenant SaaS Database Design: Shared Schema vs Schema-per-Tenant

You're three weeks into building your SaaS. The schema is taking shape — users, subscriptions, the core domain tables. Then someone asks: "How do we make sure tenant A can't see tenant B's data?"

You add a tenant_id column to every table and move on. It's the obvious answer. It's also a decision that will shape your database architecture, your migration strategy, your backup options, and your compliance story for the entire life of the product.

I've made this decision several times across vatnode.dev, pi-pi.ee, and other projects. There's no single right answer — but there is a set of trade-offs that most articles gloss over. This is the breakdown I wish existed when I first had to make it.

The Three Models

Multi-tenancy in PostgreSQL has three main approaches, ranging from shared infrastructure to complete isolation:

Shared schema — all tenants in the same tables, separated by a tenant_id column. Row-level security (RLS) enforces isolation at the database layer. One schema, one migration path, one set of indexes.

Schema-per-tenant — each tenant gets their own Postgres schema (CREATE SCHEMA tenant_abc). The table structure is identical across schemas, but the data is physically separate. Migrations run once per schema.

Database-per-tenant — each tenant gets their own Postgres database or even their own database server. Maximum isolation, maximum operational complexity, highest cost.

Before picking, you need to understand what you're actually trading.

Shared Schema with Row-Level Security

This is what I reach for by default on new projects with an unknown tenant count. The appeal is real: one migration to write, one set of indexes to maintain, connection pooling works naturally, and cross-tenant analytics are simple SQL.

The foundation is a tenant_id column on every table and PostgreSQL's RLS feature to enforce isolation at the database layer — not the application layer.

-- Create the tenants table first
CREATE TABLE tenants (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Every domain table carries tenant_id
CREATE TABLE projects (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_projects_tenant ON projects(tenant_id);

Then enable RLS and write the policies:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Force RLS even for the table owner (critical — skipping this breaks isolation)
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

CREATE POLICY projects_tenant_isolation ON projects
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

In your application, set the tenant context at the start of every request — inside a transaction so the setting is scoped and automatically cleared when the transaction ends:

// lib/db-tenant.ts
import { db } from "@/lib/db";
import { sql } from "drizzle-orm";

export async function withTenantContext<T>(
  tenantId: string,
  fn: (tx: typeof db) => Promise<T>
): Promise<T> {
  return db.transaction(async (tx) => {
    // true = transaction-local scope — resets automatically on commit/rollback
    await tx.execute(sql`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`);
    return fn(tx);
  });
}

Usage in a Server Action or API route:

// app/api/projects/route.ts
import { NextRequest, NextResponse } from "next/server";
import { withTenantContext } from "@/lib/db-tenant";
import { db } from "@/lib/db";
import { projects } from "@/lib/db/schema";

export async function GET(request: NextRequest) {
  const tenantId = getTenantIdFromSession(request); // your auth layer

  const result = await withTenantContext(tenantId, async (tx) => {
    return tx.select().from(projects); // RLS filters automatically
  });

  return NextResponse.json(result);
}

With this setup, even if a developer forgets a WHERE tenant_id = $1 clause, PostgreSQL will not return another tenant's rows. The policy enforces at the storage layer.

Where Shared Schema Shoots You in the Foot

You forgot to set the context. If a query runs outside withTenantContext, current_setting('app.current_tenant_id') raises an error — unless you use the two-argument form current_setting('app.current_tenant_id', true), which returns null instead. If your policy uses USING (tenant_id = current_setting(...)::UUID) and the setting is null, the cast fails or the policy evaluates to false for all rows. Neither is a data leak, but the error surface is subtle.

The safer policy adds an explicit null guard:

CREATE POLICY projects_tenant_isolation ON projects
  FOR ALL
  USING (
    tenant_id = nullif(current_setting('app.current_tenant_id', true), '')::UUID
  );

Connection pooling and session state. If you use PgBouncer in session pooling mode (not transaction pooling mode), SET commands persist for the connection's lifetime and can bleed into the next request. Always use set_config(..., true) (transaction-local) inside a transaction, and use PgBouncer in transaction pooling mode. I covered the pooling trade-offs in more detail in PostgreSQL Production Patterns.

Cross-tenant admin queries. Your support team needs to look at a specific tenant's data. Your background job aggregates across all tenants for billing. These queries need to bypass RLS. You do this with a superuser role or by setting the context to a special value:

-- Create a bypass role for administrative queries
CREATE ROLE app_admin BYPASSRLS;

-- Or: a superuser context that bypasses
SET app.current_tenant_id = '00000000-0000-0000-0000-000000000000'; -- sentinel UUID
CREATE POLICY projects_tenant_isolation ON projects
  FOR ALL
  USING (
    current_setting('app.current_tenant_id', true) = 'admin'
    OR tenant_id = nullif(current_setting('app.current_tenant_id', true), '')::UUID
  );

I prefer the BYPASSRLS role approach — it's explicit and auditable. The sentinel UUID approach is fragile; someone will inevitably use it where they shouldn't.

Blast radius on a missed WHERE clause before RLS was added. This is historical: if you add RLS to a table that already had data and running application code, there's a gap where the old code ran without the protection. Add RLS before the first production deployment, not after.

Noisy neighbors. One tenant running a heavy analytical query on a shared table can slow queries for all other tenants on the same database. On shared schema, you have no per-tenant query budgets. At high tenant counts with variable workloads, this becomes a real operations problem.

slug="mvp-development"
text="Picking the wrong isolation model early is expensive to fix later. I help founders make this call with the right trade-offs in hand before any code is written."
/>

Schema-per-Tenant

Each tenant gets their own Postgres schema — a namespace within the same database. Tables are identical across schemas; data is logically separated.

-- Provision a new tenant
CREATE SCHEMA tenant_acme;

-- Run migrations for this tenant's schema
CREATE TABLE tenant_acme.projects (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  -- No tenant_id column — isolation is structural
);

Application code sets search_path to the tenant's schema at the start of each request:

// lib/db-schema.ts
import { db } from "@/lib/db";
import { sql } from "drizzle-orm";

export async function withSchemaContext<T>(
  tenantSlug: string,
  fn: (tx: typeof db) => Promise<T>
): Promise<T> {
  // Validate the slug format before using it in SQL — never interpolate user input raw
  if (!/^[a-z0-9_]+$/.test(tenantSlug)) {
    throw new Error(`Invalid tenant slug: ${tenantSlug}`);
  }

  return db.transaction(async (tx) => {
    await tx.execute(sql.raw(`SET LOCAL search_path TO tenant_${tenantSlug}, public`));
    return fn(tx);
  });
}

SET LOCAL scopes the search_path change to the current transaction, same as set_config(..., true) for RLS. The query SELECT * FROM projects will automatically hit tenant_acme.projects for the right tenant — no WHERE tenant_id = $1 anywhere.

What Schema-per-Tenant Is Good At

Simpler application code. No tenant_id columns. No RLS policies to maintain. No risk of a missing WHERE clause leaking data. The database structure enforces isolation through namespacing.

Single-tenant restore. If tenant Acme needs their data restored to last Tuesday, you can restore just tenant_acme schema from backup without touching any other tenant. With shared schema, you're doing point-in-time recovery for the whole database or running complex row-filtered restore scripts.

Per-tenant customization. If enterprise customers need custom columns or tenant-specific indexes, you can add them per schema without affecting others. This is rare but occasionally valuable.

GDPR right to erasure. Deleting all of a tenant's data is DROP SCHEMA tenant_acme CASCADE. Clean. Auditable. With shared schema, you're doing a DELETE FROM every_table WHERE tenant_id = $1 across 20 tables in the right order.

Where Schema-per-Tenant Gets Painful

Migrations. Your migration that adds a column now needs to run once per tenant schema. At 10 tenants this is fine. At 1,000 tenants this is a 10-minute deployment window. At 10,000 tenants it's a scheduling problem.

// scripts/migrate-all-tenants.ts
import { db } from "@/lib/db";
import { sql } from "drizzle-orm";

async function runMigrationForAllTenants(migrationSql: string): Promise<void> {
  // Fetch all tenant schemas from a central registry table
  const tenants = await db.execute<{ slug: string }>(
    sql`SELECT slug FROM tenants ORDER BY created_at`
  );

  for (const tenant of tenants.rows) {
    console.log(`Migrating tenant_${tenant.slug}...`);
    await db.execute(sql.raw(`SET search_path TO tenant_${tenant.slug}`));
    await db.execute(sql.raw(migrationSql));
  }
}

You need a migration strategy that handles partial failures, can be retried, and tracks which tenants have been migrated. What was a simple Drizzle migration becomes a coordination problem.

Connection pooling complexity. PgBouncer in transaction pooling mode doesn't support search_path per-connection reliably, because the path is a session property and transaction pooling returns the connection to the pool after each transaction. You need to either use session pooling (higher connection overhead), set search_path inside every transaction (the SET LOCAL approach above), or use a different pooler configuration per tenant schema.

Cross-tenant queries. Aggregating data across all tenants — billing, analytics, system health — requires either a shared metadata table in the public schema, or queries that explicitly reference all tenant schemas:

-- Cross-tenant aggregate — ugly and scales badly with tenant count
SELECT 'acme' AS tenant, COUNT(*) FROM tenant_acme.projects
UNION ALL
SELECT 'globex', COUNT(*) FROM tenant_globex.projects
UNION ALL
-- ... one row per tenant

You end up maintaining a schema registry and generating these queries dynamically, which is fragile.

Postgres object count limits. Each schema adds its own set of tables, indexes, sequences, and functions to the Postgres catalog. At thousands of tenants this causes catalog bloat that degrades planner performance. PostgreSQL's pg_class table starts to slow down with hundreds of thousands of objects.

Database-per-Tenant

The third option: each tenant gets a completely separate Postgres database (or separate Postgres instance). This is the maximum isolation model.

I won't go into the implementation in detail here — the operational complexity is significant and it's only justified in specific scenarios. The tradeoffs:

What it gives you: true physical isolation, independent backups and restores, per-tenant upgrade schedules, and the ability to move a tenant to different hardware. Enterprise customers who require data residency in a specific EU country can be provisioned on a database running in that region.

What it costs you: a connection pool per database, a migration system that orchestrates across independent databases, no cross-tenant SQL at all (everything is API-level aggregation), and operational overhead that grows linearly with tenant count.

I've seen this model work well for enterprise SaaS where each customer signs a contract and expects their data on dedicated infrastructure. It makes no sense for a product with 500 small tenants paying €30/month.

Decision Matrix

Criteria	Shared Schema + RLS	Schema-per-Tenant	Database-per-Tenant
Tenant count	Hundreds to millions	Dozens to low thousands	Dozens
Migration complexity	Simple (one run)	Medium (run per schema)	High (per database)
Blast radius on bug	High (all tenants)	Medium (schema boundary)	Low (one database)
Single-tenant restore	Hard	Straightforward	Trivial
Cross-tenant queries	Easy SQL	Awkward	API-level only
Noisy neighbor risk	High	Medium	None
GDPR erasure	Multi-table DELETE	DROP SCHEMA	DROP DATABASE
Physical data isolation	No	No	Yes
Connection pooling	Simple	Requires care	Complex
Per-tenant customization	Difficult	Possible	Full control

When to Use Each

Start with shared schema + RLS if:

• You're building an early MVP and don't know your tenant count or size distribution yet
• Your tenants are roughly similar in size and workload
• You need cross-tenant analytics or operational queries regularly
• Your team is small and you want migration simplicity

Move to schema-per-tenant if:

• You have a predictable and bounded tenant count (enterprise SaaS with named accounts)
• Your tenants have meaningfully different data volumes or compliance requirements
• You sell to customers who want to feel like their data is "separate"
• You need reliable single-tenant backup/restore without complexity

Use database-per-tenant if:

• You have enterprise customers with contractual data residency requirements
• You're building infrastructure SaaS where each customer expects fully dedicated resources
• Cost and operational overhead are not the binding constraint

Migrating Between Isolation Models

Switching isolation models mid-flight is the most expensive operation in a SaaS lifecycle — more disruptive than any feature rewrite. The reason it gets postponed is that the cost isn't obvious until you're already deep in it: data, application code, and operations all change simultaneously, and none of it can be tested in production without real risk.

The path that comes up most often is shared schema → schema-per-tenant: a product launched with tenant_id + RLS, got traction with enterprise buyers who now want clearer data boundaries, and needs to restructure. You can't do this atomically for all tenants at once. The only practical approach is tenant-by-tenant migration with a feature flag that controls which code path each tenant hits during the transition.

A single-tenant migration looks like this:

-- Step 1: Create the target schema and tables for this tenant
CREATE SCHEMA tenant_acme;

CREATE TABLE tenant_acme.projects (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Step 2: Copy data from shared schema into the tenant's schema
INSERT INTO tenant_acme.projects (id, name, status, created_at, updated_at)
SELECT id, name, status, created_at, updated_at
FROM public.projects
WHERE tenant_id = '018f1234-0000-7000-8000-000000000001'; -- tenant Acme's UUID

// scripts/migrate-tenant-to-schema.ts
import { db } from "@/lib/db";
import { sql } from "drizzle-orm";

async function migrateTenantToSchema(tenantId: string, tenantSlug: string): Promise<void> {
  // Validate slug — this goes into raw SQL
  if (!/^[a-z0-9_]+$/.test(tenantSlug)) {
    throw new Error(`Invalid tenant slug: ${tenantSlug}`);
  }

  await db.transaction(async (tx) => {
    // Create schema with the canonical table definition (indexes, FK, constraints intact)
    // Don't use CREATE TABLE ... AS SELECT — it silently drops indexes, defaults, and FK constraints
    await tx.execute(sql.raw(`CREATE SCHEMA IF NOT EXISTS tenant_${tenantSlug}`));
    await tx.execute(
      sql.raw(`
      CREATE TABLE tenant_${tenantSlug}.projects (
        id UUID PRIMARY KEY,
        name TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'active',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
      )
    `)
    );

    // Backfill data separately — schema is correct before any row is inserted.
    // The schema name is already validated, so it's safe as a raw identifier;
    // tenantId stays a bound parameter — never interpolate user input into SQL.
    const tenantSchema = sql.raw(`tenant_${tenantSlug}`);
    await tx.execute(sql`
      INSERT INTO ${tenantSchema}.projects (id, name, status, created_at, updated_at)
      SELECT id, name, status, created_at, updated_at
      FROM public.projects
      WHERE tenant_id = ${tenantId}
    `);

    // Mark tenant as migrated in the registry
    await tx.execute(sql`UPDATE tenants SET isolation_model = 'schema' WHERE id = ${tenantId}`);
  });
}

After the copy, you run a dual-write period: new writes go to both public.projects (RLS path) and tenant_acme.projects (schema path). Your routing layer reads isolation_model from the tenants table and calls either withTenantContext or withSchemaContext accordingly. Once you've verified row counts and spot-checked a sample of records, you flip the flag, drain the dual-write, and delete the tenant's rows from the shared tables.

For tenants with large data volumes, simple dual-write isn't enough — a slow backfill means rows written during the copy window can be missed. In those cases I've used a temporary write freeze (pausing writes for the tenant for a few seconds at the end of the backfill) or logical replication via WAL streaming / Debezium to stream changes continuously until cutover.

Once the backfill completes — however you manage it — verification is the step most teams skip and regret:

-- Verify counts match before cutting over
SELECT
  (SELECT COUNT(*) FROM public.projects WHERE tenant_id = $1) AS shared_count,
  (SELECT COUNT(*) FROM tenant_acme.projects) AS schema_count;

Do this for every table. A mismatch means either the copy job hit an error partway through, or something wrote to the shared table during the migration window. Both need to be resolved before cutover.

COUNT is a rough first gate. Before you delete the tenant's rows from the shared tables, run a proper reconciliation job: compare checksums or hash-aggregates per table, identify any missing or late-arriving rows, and queue them for retry. I've had COUNT match while a few rows were silently corrupt due to a type coercion in the SELECT — a row-level hash check would have caught it.

The same pattern applies in reverse (schema-per-tenant → shared schema), but that direction is rarer and carries higher risk: you're collapsing isolation boundaries, not adding them.

The practical conclusion: you can migrate, but it takes a week of engineering per model transition, careful phasing, and a rollback plan for each tenant. Choosing the right model upfront — based on your realistic tenant count trajectory, not your optimistic one — is almost always cheaper.

Real Pitfalls I've Hit

Forgetting FORCE ROW LEVEL SECURITY. RLS by default doesn't apply to the table owner. If your application database role is the same as the table owner (common in development setups), your RLS policies silently don't apply. Always add FORCE ROW LEVEL SECURITY alongside ENABLE ROW LEVEL SECURITY.

-- Both lines are required
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Schema-per-tenant with a SaaS that grew unexpectedly. I've seen products launch with schema-per-tenant for 50 expected enterprise customers, then pivot to a self-serve model. Suddenly they have 3,000 schemas, migrations take 40 minutes, and catalog queries slow down noticeably. Migrating from schema-per-tenant to shared schema is painful — it requires a full data migration, not a schema change.

Not thinking about the tenant provisioning path. With shared schema, adding a tenant is an INSERT. With schema-per-tenant, it's CREATE SCHEMA, running all migrations for that schema, and updating your schema registry. Make this an automated provisioning flow from day one, not a manual step.

UUID v7 for tenant IDs. If you're using tenant UUIDs in URLs or API responses (you probably are), use UUID v7 via the uuidv7 package — time-sorted, index-friendly, and non-enumerable. UUID v4 causes index fragmentation at scale. I covered the full comparison in UUID v7, ULID, and NanoID compared.

The performance of search_path in schema-per-tenant. Setting search_path inside a transaction (the SET LOCAL approach) is correct and works with transaction-pooled connections. What doesn't work: setting it at the connection level via your ORM's configuration, then using PgBouncer transaction pooling. The search_path resets between connections in the pool — the next request's query hits the public schema and finds no tables. If you see "relation does not exist" errors under load, this is why.

What I Actually Use

For vatnode.dev — shared schema with RLS. It's a public SaaS with an unknown and growing tenant count, each tenant using the service at roughly similar volumes. Migrations are simple, cross-tenant billing queries are easy SQL, and the RLS policies have been in place since the first deployment. The 11-table schema with tenant_id on every domain table has never caused isolation problems.

For a B2B platform I built with named enterprise accounts, I used schema-per-tenant. Each customer had a migration history, clear data ownership, and the ability to get their own data exported cleanly. The provisioning automation handled schema creation and migration runs on signup. We never had more than a few dozen tenants, so the migration overhead was manageable.

The earlier you make this decision, the cheaper it is. Migrating from shared schema to schema-per-tenant — or in the other direction — requires moving data, updating connection management, and rethinking your migration tooling. It's a week of engineering work at minimum, and it can't be done with zero downtime without careful planning.

Pick the model that matches your tenant count trajectory and compliance requirements. If you're not sure what that trajectory looks like yet, start with shared schema and RLS. The RLS policies can be removed later; the connection pooling is standard; the migrations are simple. It's the model that keeps your options open.

---

Further reading:

• PostgreSQL Production Patterns — RLS basics, connection pooling, UUID strategy, and migration discipline
• UUID v7, ULID, and NanoID compared — why time-sorted IDs matter for tenant and record IDs at scale
• Next.js SaaS Checklist: Launch Production-Ready in 8 Weeks — how the database layer fits into the full SaaS foundation
• PostgreSQL Row Security Policies documentation
• PostgreSQL Schemas documentation
• PgBouncer configuration reference