Young Gao

Posted on Mar 21

Multi-Tenant Architecture: Database Per Tenant vs Shared Schema — Pros and Cons (2026)

#backend #database #architecture #saas

Every startup begins with one database. Then you land your second enterprise client, and suddenly you're Googling "multi-tenant architecture" at 2am.

Let's break down the three models, when each makes sense, and how to implement them without losing sleep.

The Three Models

Model 1: Shared Database, Shared Schema
All tenants share the same tables. A tenant_id column distinguishes data.

Model 2: Shared Database, Separate Schemas
One database, but each tenant gets their own PostgreSQL schema (tenant_a.users, tenant_b.users).

Model 3: Separate Databases
Each tenant gets a completely isolated database instance.

Here's the tradeoff at a glance:

	Shared Schema	Separate Schema	Separate DB
Cost	Lowest	Medium	Highest
Isolation	Weakest	Moderate	Strongest
Complexity	Low	Medium	High
Onboarding speed	Instant	Seconds	Minutes
Compliance	Harder	Easier	Easiest

Most teams should start with Model 1 and graduate to Model 2 or 3 when contractual or regulatory pressure demands it.

Tenant Context Injection

Before anything else, you need middleware that resolves the current tenant. Every downstream query depends on it.

// middleware/tenant.ts
import { Request, Response, NextFunction } from 'express';

interface TenantContext {
  tenantId: string;
  schema?: string;
}

declare global {
  namespace Express {
    interface Request {
      tenant: TenantContext;
    }
  }
}

export async function tenantMiddleware(
  req: Request, res: Response, next: NextFunction
) {
  const tenantId = req.headers['x-tenant-id'] as string
    ?? extractFromSubdomain(req.hostname);

  if (!tenantId) {
    return res.status(400).json({ error: 'Tenant not identified' });
  }

  const tenant = await tenantStore.get(tenantId);
  if (!tenant) {
    return res.status(404).json({ error: 'Unknown tenant' });
  }

  req.tenant = { tenantId, schema: tenant.schema };
  next();
}

function extractFromSubdomain(hostname: string): string | null {
  const parts = hostname.split('.');
  return parts.length >= 3 ? parts[0] : null;
}

This runs early in your middleware chain. Everything after it can trust req.tenant exists.

Model 1: Row-Level Security in PostgreSQL

Shared schema is the most common starting point. The danger is a missing WHERE tenant_id = ? leaking data across tenants. PostgreSQL's Row-Level Security (RLS) eliminates that risk at the database level.

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: rows visible only when tenant_id matches session var
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Force RLS even for table owners
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

Set the session variable at the start of every request:

// db/connection.ts
import { Pool, PoolClient } from 'pg';

const pool = new Pool();

export async function withTenant<T>(
  tenantId: string,
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query(
      `SET LOCAL app.current_tenant = '${tenantId}'`
    );
    return await fn(client);
  } finally {
    client.release();
  }
}

Usage:

app.get('/orders', async (req, res) => {
  const orders = await withTenant(req.tenant.tenantId, async (client) => {
    // No WHERE clause needed — RLS handles it
    const result = await client.query('SELECT * FROM orders');
    return result.rows;
  });
  res.json(orders);
});

Even if a developer forgets to filter by tenant, RLS blocks cross-tenant access. Defense in depth.

Model 2: Schema-Per-Tenant

When clients demand stronger isolation but you don't want to manage separate databases:

// db/schema-router.ts
export async function withTenantSchema<T>(
  schema: string,
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query(`SET search_path TO '${schema}', public`);
    return await fn(client);
  } finally {
    await client.query(`SET search_path TO public`);
    client.release();
  }
}

// Tenant onboarding: clone schema from template
export async function provisionTenant(tenantId: string) {
  const schema = `tenant_${tenantId}`;
  const client = await pool.connect();
  try {
    await client.query(`CREATE SCHEMA ${schema}`);
    // Run migrations against the new schema
    await client.query(`SET search_path TO '${schema}'`);
    await runMigrations(client);
  } finally {
    client.release();
  }
}

Every tenant's tables are physically separated. pg_dump a single schema for easy per-tenant backups.

Model 3: Separate Databases

For regulated industries (healthcare, finance) or enterprise clients who require it contractually:

// db/connection-manager.ts
import { Pool } from 'pg';

class TenantConnectionManager {
  private pools = new Map<string, Pool>();

  getPool(tenantId: string): Pool {
    if (!this.pools.has(tenantId)) {
      const config = tenantConfigStore.get(tenantId);
      this.pools.set(tenantId, new Pool({
        host: config.dbHost,
        database: config.dbName,
        user: config.dbUser,
        password: config.dbPassword,
        max: 10, // keep pool small per tenant
      }));
    }
    return this.pools.get(tenantId)!;
  }

  async evict(tenantId: string) {
    const pool = this.pools.get(tenantId);
    if (pool) {
      await pool.end();
      this.pools.delete(tenantId);
    }
  }
}

export const connections = new TenantConnectionManager();

Cap your pool sizes. With 100 tenants at max: 20 each, you're holding 2,000 connections. That kills your database server.

Data Migration Challenges

Migrations are where multi-tenancy gets painful.

Shared schema: Standard migrations. One execution, all tenants updated. Simple.

Separate schemas/databases: You must run every migration against every tenant. Sequentially is safe but slow. In parallel, you risk partial failures.

// migrate-all-tenants.ts
async function migrateAllTenants() {
  const tenants = await tenantStore.listAll();
  const results = await Promise.allSettled(
    tenants.map(async (t) => {
      console.log(`Migrating ${t.id}...`);
      const pool = connections.getPool(t.id);
      await runMigrations(pool);
      return t.id;
    })
  );

  const failed = results.filter(r => r.status === 'rejected');
  if (failed.length > 0) {
    console.error(`${failed.length} tenants failed migration`);
    // Alert, don't proceed with deploy
  }
}

Track migration state per tenant. A global schema_migrations table won't cut it when tenants can be at different versions (think staged rollouts).

Choosing Your Model

Use shared schema when:

You're early stage with < 100 tenants
Tenants are individuals or small teams
Speed of development matters more than isolation

Use separate schemas when:

Some clients need data isolation guarantees
You want per-tenant backup/restore
You're on PostgreSQL (schema support is excellent)

Use separate databases when:

Regulatory compliance requires physical isolation
Clients need their own connection strings
You're willing to pay the operational cost

Hybrid approaches work too. Small tenants share a schema. Enterprise tenants get their own database. Route in the middleware based on tenant tier.

Common Mistakes

1. Forgetting RLS on new tables. Add a CI check that verifies every table with a tenant_id column has RLS enabled. One missed table is a data breach.

2. Not testing tenant isolation. Write integration tests that insert data as Tenant A, then query as Tenant B. Assert zero rows. Run this in CI, not just once.

3. Global connection pool exhaustion. Separate-database models multiply connection counts fast. Use PgBouncer or a similar pooler between your app and database instances.

4. Tenant-unaware caching. Your Redis cache key must include the tenant ID. orders:123 is a leak. tenant_abc:orders:123 is safe.

5. Running migrations during peak traffic. Schema-per-tenant migrations lock tables. Run them during maintenance windows or use zero-downtime migration techniques (add column, backfill, swap).

6. Hardcoding tenant resolution. Subdomain extraction works until it doesn't (custom domains, API keys, JWTs). Build tenant resolution as a pluggable strategy from day one.

Part of my Production Backend Patterns series. Follow for more practical backend engineering.

DEV Community