Multi-Tenancy Database Patterns: Shared Schema vs Isolated — When to Pick What

We've built enough SaaS products to know that multi-tenancy is one of those decisions that feels abstract until it isn't. Until you're staring at a bug at 11pm where tenant A somehow sees tenant B's data, or you're trying to export all of a customer's data for a GDPR request and realizing your queries are going to take 40 minutes because everything is crammed into one massive table.

There are three main approaches to multi-tenancy at the database level: shared schema (one table, tenant_id column), isolated schemas (one schema per tenant, shared database), and isolated databases (one database per tenant). Each one is the right answer in different contexts. Let's talk through all three honestly.

Shared Schema: The Pragmatic Default

Shared schema means every tenant's data lives in the same tables, distinguished by a tenant_id column. It's by far the most common approach for early-stage SaaS, and for good reason — it's the simplest to build, cheapest to run, and easiest to reason about when you're starting.

-- Every table gets a tenant_id
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  assignee_id UUID REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Critical: index every tenant_id column
CREATE INDEX idx_projects_org_id ON projects(organization_id);
CREATE INDEX idx_tasks_org_id ON tasks(organization_id);
CREATE INDEX idx_tasks_project_id ON tasks(project_id);

The danger with shared schema isn't the architecture — it's the discipline it requires. Every query must filter by tenant. Every. Single. One. If you forget a WHERE organization_id = $1 somewhere, you're leaking data across tenants. We've seen this happen. It's not fun.

One pattern that helps a lot in Postgres is Row Level Security (RLS). You define the policy once at the database level, and the database engine enforces it — even if your application code forgets to filter.

-- Enable RLS on every tenant-scoped table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Create a policy that reads the current org from a session variable
CREATE POLICY tenant_isolation ON projects
  USING (organization_id = current_setting('app.current_org_id')::UUID);

CREATE POLICY tenant_isolation ON tasks
  USING (organization_id = current_setting('app.current_org_id')::UUID);

-- In your app, set the session variable before querying
-- (this is per-connection, not per-transaction, so pool carefully)
SELECT set_config('app.current_org_id', $1, true); -- true = local to transaction

RLS is genuinely great, but comes with caveats. Connection poolers like PgBouncer in transaction mode don't play well with session-level settings. You need to set the config variable at the start of every transaction, not just once per connection. Get this wrong and you'll have a very bad day.

Isolated Schemas: The Middle Ground Most People Miss

Postgres has a feature called schemas (not to be confused with database schemas conceptually — these are namespaces within a database). You can give each tenant their own schema — `tenant_abc.projects`, `tenant_xyz.projects` — while still running one Postgres instance. One database, many namespaces.

This approach is underrated. You get strong isolation without the cost of running multiple databases. Migrations are trickier (you have to run them per-schema), but tooling like Flyway and Liquibase handle this. With Drizzle or Prisma you'll need to write a script that loops through tenants.

// Simplified tenant schema provisioning with Postgres
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export async function provisionTenantSchema(tenantSlug: string) {
  const schemaName = `tenant_${tenantSlug.replace(/-/g, '_')}`;
  
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    
    // Create the schema
    await client.query(`CREATE SCHEMA IF NOT EXISTS ${schemaName}`);
    
    // Run your DDL inside the tenant schema
    await client.query(`
      CREATE TABLE IF NOT EXISTS ${schemaName}.projects (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name TEXT NOT NULL,
        created_at TIMESTAMPTZ DEFAULT now()
      )
    `);
    
    await client.query(`
      CREATE TABLE IF NOT EXISTS ${schemaName}.tasks (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        project_id UUID NOT NULL REFERENCES ${schemaName}.projects(id),
        title TEXT NOT NULL,
        created_at TIMESTAMPTZ DEFAULT now()
      )
    `);
    
    await client.query('COMMIT');
    return schemaName;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

// Then in your query layer, set search_path per request
export async function getTenantClient(tenantSlug: string) {
  const schemaName = `tenant_${tenantSlug.replace(/-/g, '_')}`;
  const client = await pool.connect();
  await client.query(`SET search_path TO ${schemaName}, public`);
  return client;
}

The `search_path` trick means your application queries can stay schema-agnostic. You write `SELECT * FROM projects` and Postgres resolves it to the right tenant schema based on the search path. Clean, if you're careful about resetting it between requests.

Isolated Databases: The Enterprise Answer

Full database isolation — one Postgres instance (or cluster) per tenant — is the most operationally complex and expensive approach. It's also the right answer for enterprise customers with strict compliance requirements, or situations where tenants need wildly different performance characteristics.

The main challenge here isn't the architecture, it's the operations. How do you run migrations across 200 tenant databases? How do you monitor them? What happens when a tenant's database is on a different version during a rollout? You need real tooling for this — not a bash script and hope.

// Example: routing queries to the right database per tenant
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

// In-memory cache of db connections (don't create a new one per request)
const connectionCache = new Map<string, ReturnType<typeof drizzle>>();

export function getTenantDb(tenantId: string) {
  if (connectionCache.has(tenantId)) {
    return connectionCache.get(tenantId)!;
  }
  
  // In a real app, you'd look up the connection string from a central registry
  const connectionString = getTenantConnectionString(tenantId);
  const client = postgres(connectionString, { max: 5 }); // smaller pool per tenant
  const db = drizzle(client);
  
  connectionCache.set(tenantId, db);
  return db;
}

function getTenantConnectionString(tenantId: string): string {
  // This could come from your central config database, environment variables,
  // or a secrets manager like AWS Secrets Manager
  const tenantConfig = tenantRegistry.get(tenantId);
  if (!tenantConfig) throw new Error(`Unknown tenant: ${tenantId}`);
  return tenantConfig.databaseUrl;
}

Connection pool management becomes a real concern here. If you naively open 10 connections per tenant and you have 500 tenants, that's 5000 connections. Postgres starts sweating around 300-500 connections without PgBouncer. You need either very small pool sizes per tenant or a smarter connection management strategy.

How to Actually Choose

Stop trying to predict scale you don't have yet. Here's a practical decision tree we actually use:

• Less than 1000 tenants, no compliance requirements, building fast → shared schema with RLS. Ship it.
• 1000-10,000 tenants, some enterprise customers asking about data isolation → isolated schemas in one database. Worth the migration complexity.
• Enterprise-only product, SOC 2 Type II, healthcare or finance → isolated databases. Budget for the ops overhead.
• Hypergrowth SaaS with mixed customer types → hybrid. Big enterprise customers get isolated databases, SMB customers share a schema.

The hybrid approach is underrated: give each tier what it actually needs. Your 10 enterprise customers can have isolated databases. Your 5000 SMB customers can share one. This is how companies like Notion and Linear actually run.

The Migration Problem Nobody Talks About

Whichever pattern you pick, schema migrations become more complex with multi-tenancy. With shared schema, it's straightforward — one migration, done. With isolated schemas or databases, you need to run migrations across N tenants, which introduces ordering, failure, and rollback problems.

The strategy we prefer: run migrations serially with a central migration tracking table. Don't try to parallelize across all tenants at once — if migration #47 fails on tenant 342 out of 500, you need to know exactly where you are and be able to resume.

// Simplified multi-tenant migration runner
import { db as centralDb } from './central-db';
import { getTenantDb } from './tenant-db';
import { migrations } from './migrations';

export async function runMigrationsForAllTenants() {
  const tenants = await centralDb.query.tenants.findMany({
    where: (t, { eq }) => eq(t.status, 'active')
  });
  
  for (const tenant of tenants) {
    try {
      console.log(`Migrating tenant ${tenant.slug}...`);
      const tenantDb = getTenantDb(tenant.id);
      
      // Track which migrations have run per tenant
      const applied = await getAppliedMigrations(tenantDb);
      const pending = migrations.filter(m => !applied.includes(m.name));
      
      for (const migration of pending) {
        await tenantDb.transaction(async (tx) => {
          await migration.up(tx);
          await recordMigration(tx, migration.name);
        });
        console.log(`  ✓ ${migration.name}`);
      }
    } catch (err) {
      // Log and continue — don't let one bad tenant block others
      // But alert immediately
      console.error(`Migration failed for tenant ${tenant.slug}:`, err);
      await alertOncall(`Migration failed: ${tenant.slug}`);
    }
  }
}

The key insight: don't stop all migrations if one tenant fails. Log it, alert, and keep going. You can fix the outlier after. Stopping the whole run means your other tenants are stuck on an old schema while you debug one edge case.

Data Residency and GDPR Considerations

If you have European customers (and you do, or you will), data residency becomes relevant fast. GDPR doesn't technically require data to stay in the EU, but many enterprise customers have internal policies that do. Isolated databases give you the most flexibility here — you can host a tenant's database in Frankfurt while everyone else runs in us-east-1.

With shared schema, you're stuck — all tenant data is in the same database. With isolated schemas in one database, same problem. Data residency is one of the strongest arguments for isolated databases, even if you don't need it today.

For GDPR deletion (the right to be forgotten), isolated schemas and databases are genuinely easier. Drop the schema or database, done. With shared schema, you're writing DELETE queries across every table, hoping you haven't missed a foreign key somewhere. We've handled this for clients and it's always messier than it looks.

What We Actually Ship

For our own projects and the templates we build at peal.dev, we default to shared schema with Postgres RLS. It's the pragmatic choice for most SaaS products — you can build fast, the isolation is enforced at the database level so you can't accidentally leak data in application code, and it doesn't require any operational complexity to run.

The schema looks simple, but the discipline around it matters: always index your tenant_id columns, always enable RLS, and write integration tests that specifically verify a user from tenant A cannot read tenant B's data. That last one sounds obvious but gets skipped constantly.

Write a test that logs in as tenant A and tries to fetch tenant B's resource IDs directly. If it returns data, you have a problem. Ship it as a permanent regression test, not a one-time check.

The architecture decision matters less than the consistency of your implementation. We've seen companies with isolated databases leak data across tenants because someone wrote a reporting query without filtering. We've seen shared schema products run flawlessly for years because the team was rigorous. Pick the pattern that fits your current stage, implement it consistently, and plan the migration path to the next tier if you need it.