Multi-Tenant SaaS Architecture in 2026: Shared vs Isolated DB, RLS, and Tenant Routing

Multi-tenancy means one instance of your application serves multiple customers (tenants), with their data isolated from each other. How you implement that isolation determines your infrastructure cost, operational complexity, data security guarantees, and how far you can scale before hitting limits.

The three models — shared database, schema-per-tenant, and database-per-tenant — each make different tradeoffs. Most SaaS products start with shared database and move toward isolation selectively for enterprise customers who require it. This post covers all three models with implementation patterns.

---

Multi-Tenancy Model Comparison

Decision guide:

• Most B2B SaaS: shared DB with RLS (up to hundreds of tenants)
• Enterprise customers with data residency requirements: schema or database per tenant
• Financial/healthcare: database per tenant for regulatory compliance
• Never build isolation you don't need yet — migrate up as requirements demand

---

Model 1: Shared Database with Row-Level Security

Every table has a tenant_id column. PostgreSQL Row-Level Security policies enforce that queries only see data for the current tenant, enforced at the database level — not just the application layer.

-- Enable RLS on every table that contains tenant data
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- Create policies: each tenant sees only their rows
-- The current tenant is set via a session-level variable
CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation ON products
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Service role bypasses RLS (for migrations, admin queries)
-- Application role is subject to RLS
CREATE ROLE app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

CREATE ROLE service_user BYPASSRLS;
GRANT ALL ON ALL TABLES IN SCHEMA public TO service_user;

// src/lib/db.ts — Tenant-scoped database client

import { Pool, PoolClient } from 'pg';

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

// Wrap a database operation with tenant context
export async function withTenantContext<T>(
  tenantId: string,
  operation: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();

  try {
    // Set the tenant context for this connection
    await client.query(
      `SELECT set_config('app.current_tenant_id', $1, true)`,
      [tenantId],
    );
    // true = local (only for this transaction) — prevents leakage

    return await operation(client);
  } finally {
    client.release();
  }
}

// Usage:
// const orders = await withTenantContext(tenantId, (db) =>
//   db.query('SELECT * FROM orders LIMIT 20')
// );
// → RLS automatically filters to only this tenant's orders

Middleware: Resolve Tenant from Request

// src/middleware/tenant.ts
import { FastifyRequest, FastifyReply } from 'fastify';

declare module 'fastify' {
  interface FastifyRequest {
    tenantId: string;
    tenant: Tenant;
  }
}

export async function tenantMiddleware(
  req: FastifyRequest,
  reply: FastifyReply,
): Promise<void> {
  // Strategy 1: Subdomain routing (acme.myapp.com → tenant: acme)
  const host = req.hostname;
  const subdomain = host.split('.')[0];

  // Strategy 2: Custom header (for API clients)
  const tenantHeader = req.headers['x-tenant-id'] as string | undefined;

  // Strategy 3: JWT claim (for authenticated requests)
  const jwtTenantId = (req as any).user?.tenantId;

  const tenantSlug = subdomain !== 'www' && subdomain !== 'api'
    ? subdomain
    : tenantHeader ?? null;

  if (!tenantSlug && !jwtTenantId) {
    return reply.status(400).send({ error: 'Tenant not identified' });
  }

  // Resolve tenant from slug (cached in Redis)
  const tenant = await resolveTenant(tenantSlug ?? jwtTenantId);

  if (!tenant || tenant.status !== 'active') {
    return reply.status(404).send({ error: 'Tenant not found or inactive' });
  }

  req.tenantId = tenant.id;
  req.tenant = tenant;
}

async function resolveTenant(slugOrId: string): Promise<Tenant | null> {
  const cacheKey = `tenant:${slugOrId}`;
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  const { rows } = await adminDb.query<Tenant>(
    `SELECT * FROM tenants WHERE slug = $1 OR id = $1 LIMIT 1`,
    [slugOrId],
  );

  if (rows[0]) {
    await redis.setex(cacheKey, 300, JSON.stringify(rows[0]));  // Cache 5 min
  }

  return rows[0] ?? null;
}

---

Model 2: Schema-Per-Tenant

Each tenant gets their own PostgreSQL schema. All tables exist in each schema; queries are namespaced by search_path:

-- Create schema for a new tenant
CREATE SCHEMA tenant_acme;

-- Apply migrations to tenant schema
SET search_path TO tenant_acme;

CREATE TABLE users (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email      TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    -- same structure as other tenant schemas
);

-- No tenant_id column needed — schema provides isolation

// src/lib/schema-tenant-db.ts
export async function withSchema<T>(
  tenantSchema: string,
  operation: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    // Validate schema name (prevent injection)
    if (!/^tenant_[a-z0-9_]+$/.test(tenantSchema)) {
      throw new Error(`Invalid schema name: ${tenantSchema}`);
    }

    await client.query(`SET search_path TO ${tenantSchema}, public`);
    return await operation(client);
  } finally {
    client.release();
  }
}

// Provision a new tenant schema
export async function provisionTenantSchema(tenantSlug: string): Promise<void> {
  const schemaName = `tenant_${tenantSlug.replace(/-/g, '_')}`;

  // Create schema
  await adminDb.query(`CREATE SCHEMA IF NOT EXISTS ${schemaName}`);

  // Run all migrations in the new schema
  await runMigrationsInSchema(schemaName);
}

---

Model 3: Database-Per-Tenant

Each tenant gets a separate database (or RDS instance). Required for strict data residency (EU customers on EU database, US on US database):

// src/lib/tenant-db-router.ts
// Route each tenant to their own database

interface TenantDatabaseConfig {
  tenantId: string;
  connectionString: string;
  region: 'us-east-1' | 'eu-west-1' | 'ap-southeast-1';
}

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

  async getPool(tenantId: string): Promise<Pool> {
    if (this.pools.has(tenantId)) {
      return this.pools.get(tenantId)!;
    }

    const config = await this.loadTenantDbConfig(tenantId);
    const pool = new Pool({
      connectionString: config.connectionString,
      max: 5,   // Smaller pool per tenant — many tenants, fewer connections each
      idleTimeoutMillis: 30000,
    });

    this.pools.set(tenantId, pool);
    return pool;
  }

  private async loadTenantDbConfig(tenantId: string): Promise<TenantDatabaseConfig> {
    const { rows } = await controlPlaneDb.query<TenantDatabaseConfig>(
      `SELECT tenant_id, connection_string, region
       FROM tenant_databases WHERE tenant_id = $1`,
      [tenantId],
    );

    if (!rows[0]) throw new Error(`No database configured for tenant ${tenantId}`);
    return rows[0];
  }

  // Provision new tenant database (called during onboarding)
  async provisionTenantDatabase(tenantId: string, region: string): Promise<string> {
    // Create RDS instance via Terraform/CDK or AWS SDK
    // For small tenants: create a separate database on a shared RDS instance
    const { rows } = await controlPlaneDb.query<{ connection_string: string }>(
      `INSERT INTO tenant_databases (tenant_id, region)
       VALUES ($1, $2) RETURNING connection_string`,
      [tenantId, region],
    );
    return rows[0].connection_string;
  }
}

---

Tenant Data Migration Strategy

When upgrading a tenant from shared → isolated:

// src/scripts/migrate-tenant-to-isolated.ts
export async function migrateTenantToSchema(tenantId: string): Promise<void> {
  // 1. Provision new schema
  const schemaName = `tenant_${tenantId.replace(/-/g, '_')}`;
  await provisionTenantSchema(schemaName);

  // 2. Copy data (with maintenance mode)
  await setTenantMaintenanceMode(tenantId, true);

  try {
    const tables = ['users', 'orders', 'products', 'invoices', 'events'];

    for (const table of tables) {
      await adminDb.query(`
        INSERT INTO ${schemaName}.${table}
        SELECT * FROM public.${table}
        WHERE tenant_id = $1
      `, [tenantId]);
    }

    // 3. Verify row counts match
    for (const table of tables) {
      const { rows: [source] } = await adminDb.query(
        `SELECT count(*) FROM public.${table} WHERE tenant_id = $1`,
        [tenantId],
      );
      const { rows: [dest] } = await adminDb.query(
        `SELECT count(*) FROM ${schemaName}.${table}`,
      );

      if (source.count !== dest.count) {
        throw new Error(`Row count mismatch in ${table}: ${source.count} vs ${dest.count}`);
      }
    }

    // 4. Switch routing to new schema
    await updateTenantIsolationMode(tenantId, 'schema', schemaName);

  } finally {
    await setTenantMaintenanceMode(tenantId, false);
  }
}

---

Cross-Tenant Admin Queries (Bypass RLS)

// src/admin/analytics.ts — Cross-tenant admin queries (service role)

// IMPORTANT: always use adminDb, never the tenant-scoped client
const adminDb = new Pool({
  connectionString: process.env.DATABASE_URL_SERVICE_ROLE!, // BYPASSRLS role
  max: 5,
});

export async function getPlatformMRRByTenant(): Promise<TenantMRR[]> {
  const { rows } = await adminDb.query<TenantMRR>(`
    SELECT
      t.id,
      t.name,
      t.slug,
      SUM(s.mrr_cents) AS total_mrr_cents
    FROM tenants t
    JOIN subscriptions s ON s.tenant_id = t.id
    WHERE s.status = 'active'
    GROUP BY t.id, t.name, t.slug
    ORDER BY total_mrr_cents DESC
  `);
  return rows;
}

---

Working With Viprasol

We design and implement multi-tenant architectures for SaaS products — from shared-database RLS through schema-per-tenant isolation and database-per-tenant for enterprise customers.

What we deliver:

• Tenant isolation model selection based on your compliance and cost requirements
• PostgreSQL RLS policy design and implementation
• Tenant routing middleware for subdomain, header, and JWT resolution
• Tenant provisioning automation (schema or database creation on signup)
• Migration tooling for upgrading tenants from shared to isolated models

→ Discuss your multi-tenant architecture → SaaS development services

---

See Also

• Database Design Patterns
• PostgreSQL Full-Text Search
• SaaS Onboarding Engineering
• Data Privacy Engineering and GDPR
• Database Replication and High Availability