The Multi-Tenancy Conundrum in B2B SaaS

Every engineer building a B2B Software-as-a-Service (SaaS) application eventually faces the single most consequential architectural decision of their infrastructure: How do we isolate customer data?

If Customer A ever sees Customer B’s invoices or user list, your company’s reputation and enterprise trust are incinerated. But if your isolation strategy is too complex, your database migrations take hours, your hosting bills explode, and onboarding a new customer requires spinning up dedicated database clusters.

“Data isolation in multi-tenant architectures cannot rely on application developers remembering WHERE tenant_id = ? in every query. The moment a junior developer forgets that clause in an internal reporting endpoint, a catastrophic data breach occurs. Isolation must be enforced by the database engine itself.” — Principal Security Engineer at Enterprise SaaS Company

Let’s examine the three canonical multi-tenancy models in PostgreSQL, and provide a complete, runnable production blueprint using PostgreSQL Row Level Security (RLS) and Drizzle ORM.

---

The 3 Multi-Tenancy Architecture Patterns

graph TD
    subgraph Model 1: Database-per-Tenant
        DB1[Database: Tenant A]
        DB2[Database: Tenant B]
    end

    subgraph Model 2: Schema-per-Tenant
        SDB[Single Database]
        SDB --> SC1[Schema: tenant_a]
        SDB --> SC2[Schema: tenant_b]
    end

    subgraph Model 3: Shared Schema with RLS (Recommended)
        RDB[Single Database & Shared Schema]
        RDB --> TAB[All Tables contain tenant_id]
        TAB --> RLS[PostgreSQL Row Level Security Policy Engine]
    end

1. Database-per-Tenant

• How it works: Each customer has a completely distinct PostgreSQL database instance.
• Pros: Total physical isolation, trivial per-tenant backup and restore, zero noisy-neighbor resource contention.
• Cons: Operational nightmare at scale. Running database schema migrations across 2,000 separate databases takes hours, and idle connection pools exhaust RAM.
• Verdict: Only justified for high-compliance enterprise tiers (FedRAMP, healthcare, banking) paying $50k+/year.

2. Schema-per-Tenant

• How it works: One shared database, but each customer gets a dedicated PostgreSQL namespace (CREATE SCHEMA tenant_123).
• Pros: Logical table isolation without separate connection pools.
• Cons: PostgreSQL is not designed for tens of thousands of schemas. The internal catalog tables (pg_class, pg_attribute) become bloated, autovacuum degrades, and migrations still require running across thousands of schemas sequentially.

3. Shared Schema with Row Level Security (RLS) — The Modern Standard

• How it works: All tenants share the same tables (public.organizations, public.projects, public.invoices). Every tenant-scoped table has a tenant_id foreign key column. The PostgreSQL kernel enforces access via Row Level Security (RLS).
• Pros: Instant tenant onboarding (INSERT INTO organizations), single-step schema migrations, unified analytics, cost-efficient resource pooling.
• Cons: Requires rigorous RLS policy testing and disciplined session context setting.

---

Production PostgreSQL DDL Blueprint

Here is the exact DDL to set up a hardened, production-grade multi-tenant schema in PostgreSQL:

-- 1. Enable UUID Extension
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- 2. Organizations / Tenants Table
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(64) NOT NULL UNIQUE,
  plan VARCHAR(32) NOT NULL DEFAULT 'free',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. Users Table (Global Identity)
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 4. Memberships Table (Tenant-to-User Junction)
CREATE TABLE organization_members (
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role VARCHAR(32) NOT NULL DEFAULT 'member', -- 'owner', 'admin', 'member'
  joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (organization_id, user_id)
);

-- 5. Tenant-Scoped Resource Table (Projects)
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Critical Index: All queries filter by organization_id
CREATE INDEX idx_projects_org_id ON projects(organization_id);

-- Composite Index for tenant-scoped sorting/filtering
CREATE INDEX idx_projects_org_created ON projects(organization_id, created_at DESC);

---

Hardening with Row Level Security (RLS)

To guarantee that queries cannot leak cross-tenant records, we enable RLS on tenant-scoped tables:

-- 1. Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- 2. Force RLS for table owners too (prevents bypass by application DB user)
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

-- 3. Define the Security Policy using session variables
CREATE POLICY tenant_isolation_policy ON projects
  AS RESTRICTIVE
  FOR ALL
  USING (organization_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID)
  WITH CHECK (organization_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID);

How the Application Sets Context in Connection Pools

When checking out a connection from your database pool (PgBouncer, Supabase, or node-postgres), you set the session variable within the active transaction:

// Executed at the beginning of every HTTP request:
await db.transaction(async (tx) => {
  // Bind tenant ID to this transaction
  await tx.execute(
    sql`SELECT set_config('app.current_tenant_id', ${currentTenantId}, true)`
  );

  // Any subsequent query is physically constrained by Postgres:
  // Even if you write "SELECT * FROM projects", only current tenant records are returned!
  const tenantProjects = await tx.select().from(projects);
  return tenantProjects;
});

---

Modeling in Drizzle ORM

Here is how this architecture translates into idiomatic Drizzle TypeScript definitions:

// src/db/schema/saas.ts
import { pgTable, uuid, varchar, text, timestamp, index } from "drizzle-orm/pg-core";

export const organizations = pgTable("organizations", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  slug: varchar("slug", { length: 64 }).notNull().unique(),
  plan: varchar("plan", { length: 32 }).default("free").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
});

export const projects = pgTable("projects", {
  id: uuid("id").primaryKey().defaultRandom(),
  organizationId: uuid("organization_id")
    .notNull()
    .references(() => organizations.id, { onDelete: "cascade" }),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => [
  index("idx_projects_org_id").on(table.organizationId),
  index("idx_projects_org_created").on(table.organizationId, table.createdAt),
]);

---

Inspecting in Sepio

You can visually inspect, customize, and clone this complete SaaS architecture in Sepio’s Schema Library:

1. Open the Sepio Schema Library.
2. Select the SaaS Multi-Tenant Template.
3. Explore the visual foreign keys, composite indexes, and RLS constraint annotations.
4. Export ready-to-deploy SQL migrations or Drizzle ORM files with one click.

---

Frequently Asked Questions

Does Row Level Security impact query performance?

When properly indexed, RLS has negligible overhead (< 2–3%). Because every policy checks organization_id = ..., having a B-Tree index on organization_id (or composite (organization_id, id)) ensures PostgreSQL converts the policy check into an efficient index scan.

What happens if app.current_tenant_id is not set?

In our policy definition above, we use NULLIF(current_setting(..., true), '')::UUID. If the application fails to set the tenant ID, the expression resolves to NULL. Because organization_id = NULL evaluates to FALSE in SQL three-valued logic, PostgreSQL safely returns zero rows, preventing any accidental leak.

How does PgBouncer transaction pooling work with set_config?

In transaction-mode connection pooling (recommended for high concurrency), the third argument in set_config('app.current_tenant_id', value, true) is critical: the true parameter makes the variable local to the current transaction only. When the transaction commits or rolls back, PostgreSQL automatically resets the setting, preventing context leakage to subsequent pool requests.