Multi-Tenancy Patterns: Database Isolation, Row-Level Security, and Tenant Architecture

Multi-tenancy is the foundational architectural decision for B2B SaaS: how do you serve multiple customers (tenants) from a single application while keeping their data isolated, secure, and independently manageable?

There are three main patterns, each with different isolation guarantees, operational complexity, and cost profiles. The right choice depends on your customers' compliance requirements, your scale goals, and how much you're willing to pay for isolation.

---

The Three Patterns

---

Pattern 1: Shared Database, Shared Schema

All tenants' data in the same tables, separated by a tenant_id column. The simplest to operate; the most common for startups.

-- Every table has tenant_id
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tasks (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    project_id  UUID NOT NULL REFERENCES projects(id),
    title       TEXT NOT NULL,
    assigned_to UUID REFERENCES users(id)
);

-- Mandatory: index on tenant_id for every table
CREATE INDEX idx_projects_tenant ON projects(tenant_id);
CREATE INDEX idx_tasks_tenant ON tasks(tenant_id, project_id);

The fatal mistake: forgetting WHERE tenant_id = ? in a query. One missing filter leaks data to the wrong tenant. This is why PostgreSQL Row-Level Security (RLS) is mandatory for this pattern.

---

PostgreSQL Row-Level Security (RLS)

RLS enforces tenant isolation at the database level — even if your application code forgets WHERE tenant_id = ?, the database returns only rows belonging to the current tenant.

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

-- Create policy: users can only see rows matching their tenant_id
CREATE POLICY tenant_isolation ON projects
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

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

-- Create a role that can't bypass RLS (for application connections)
CREATE ROLE app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- app_user does NOT have BYPASSRLS privilege

-- Superuser/admin role that bypasses RLS (for migrations, admin tasks)
CREATE ROLE admin_user BYPASSRLS;

// middleware/tenant.ts — set tenant context on every request
import { FastifyRequest, FastifyReply } from 'fastify';
import { db } from '@/lib/db';

export async function tenantMiddleware(
  request: FastifyRequest,
  reply: FastifyReply
) {
  // Extract tenant from JWT, subdomain, or API key
  const tenantId = extractTenantId(request);

  if (!tenantId) {
    return reply.code(401).send({ error: 'Tenant not identified' });
  }

  // Attach to request for use in route handlers
  request.tenantId = tenantId;
}

// lib/db.ts — set PostgreSQL session variable before every query
import { Pool } from 'pg';

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

export class TenantDb {
  constructor(private tenantId: string) {}

  async query<T>(sql: string, params: unknown[] = []): Promise<T[]> {
    const client = await pool.connect();
    try {
      // Set tenant context — RLS policy reads this
      await client.query(
        `SET LOCAL app.current_tenant_id = '${this.tenantId}'`
      );
      const result = await client.query(sql, params);
      return result.rows;
    } finally {
      client.release();
    }
  }
}

// In route handlers
app.get('/projects', async (request, reply) => {
  const db = new TenantDb(request.tenantId);
  // RLS automatically adds WHERE tenant_id = 'request.tenantId'
  const projects = await db.query('SELECT * FROM projects ORDER BY created_at DESC');
  return projects;
});

Testing RLS is working:

-- Set tenant context
SET app.current_tenant_id = 'tenant-a-uuid';

-- Should return only tenant A's projects
SELECT COUNT(*) FROM projects;  -- Should be 5, not 5000

-- Verify cross-tenant data is hidden
SET app.current_tenant_id = 'tenant-b-uuid';
SELECT * FROM projects WHERE id = 'a-project-from-tenant-a';  -- Should return 0 rows

---

Pattern 2: Schema-per-Tenant

Each tenant gets their own PostgreSQL schema. Tables are identical across schemas; tenant isolation is enforced by schema ownership.

-- Create schema for new tenant
CREATE SCHEMA tenant_abc123;

-- Create tables in tenant's schema
CREATE TABLE tenant_abc123.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW()
    -- Note: no tenant_id needed — it's implicit in the schema
);

-- Set search_path at connection time to scope all queries to this tenant
SET search_path = tenant_abc123, public;

-- Now this query only touches tenant_abc123.projects
SELECT * FROM projects;

// lib/tenantPool.ts — per-tenant connection pools
import { Pool } from 'pg';

const tenantPools = new Map<string, Pool>();

function getTenantPool(tenantId: string): Pool {
  if (!tenantPools.has(tenantId)) {
    const pool = new Pool({
      connectionString: process.env.DATABASE_URL,
      // Set search_path at connection level
      options: `-c search_path=tenant_${tenantId},public`,
    });
    tenantPools.set(tenantId, pool);
  }
  return tenantPools.get(tenantId)!;
}

export async function queryForTenant<T>(
  tenantId: string,
  sql: string,
  params: unknown[] = []
): Promise<T[]> {
  const pool = getTenantPool(tenantId);
  const result = await pool.query(sql, params);
  return result.rows;
}

Migrations with schema-per-tenant: You need to apply migrations to every tenant schema:

// scripts/migrate-all-tenants.ts
import { getAllTenants } from '@/lib/tenants';
import { runMigrations } from '@/lib/migrations';

async function migrateAllTenants() {
  const tenants = await getAllTenants();

  for (const tenant of tenants) {
    console.log(`Migrating tenant: ${tenant.id}`);
    try {
      await runMigrations(`tenant_${tenant.id}`);
    } catch (err) {
      console.error(`Migration failed for tenant ${tenant.id}:`, err);
      // Log and continue — don't let one tenant's failure block others
    }
  }
}

Schema-per-tenant works up to ~1,000–5,000 tenants before PostgreSQL performance degrades from schema management overhead.

---

Pattern 3: Database-per-Tenant

Each tenant gets their own PostgreSQL database instance. Maximum isolation; maximum cost.

// lib/tenantRouter.ts — route to per-tenant database
import { Pool } from 'pg';

interface TenantConfig {
  id: string;
  databaseUrl: string;  // Unique URL per tenant
  region: string;
}

const tenantConfigs = new Map<string, TenantConfig>();
const tenantPools = new Map<string, Pool>();

async function getTenantConfig(tenantId: string): Promise<TenantConfig> {
  if (!tenantConfigs.has(tenantId)) {
    // Fetch from control plane database (separate from tenant data)
    const config = await controlPlaneDb.query(
      'SELECT * FROM tenant_configs WHERE id = $1',
      [tenantId]
    );
    tenantConfigs.set(tenantId, config.rows[0]);
  }
  return tenantConfigs.get(tenantId)!;
}

export async function getTenantDb(tenantId: string): Promise<Pool> {
  if (!tenantPools.has(tenantId)) {
    const config = await getTenantConfig(tenantId);
    const pool = new Pool({ connectionString: config.databaseUrl });
    tenantPools.set(tenantId, pool);
  }
  return tenantPools.get(tenantId)!;
}

Cost reality: Each RDS PostgreSQL instance starts at ~$15/month (t3.micro). At 100 enterprise tenants: $1,500/month just for database instances, before storage, backups, or read replicas. Reserved instances reduce this; Neon or PlanetScale's serverless pricing can help.

---

Hybrid Approach (Common at Scale)

Most mature SaaS products use a hybrid:

Free/Starter tier:    Shared DB + RLS (hundreds of tenants per instance)
Pro tier:             Shared DB + schema-per-tenant
Enterprise tier:      Dedicated database instance

Routing: Control plane determines which pattern applies per tenant

This optimizes cost for the majority (shared) while meeting enterprise requirements (isolation) without requiring separate deployments.

---

Tenant Onboarding Automation

// lib/tenantProvisioning.ts
async function provisionNewTenant(
  tenantId: string,
  plan: 'starter' | 'pro' | 'enterprise'
): Promise<void> {
  switch (plan) {
    case 'starter':
      // Just insert into tenants table — shared DB handles the rest
      await controlPlaneDb.query(
        'INSERT INTO tenants (id, plan) VALUES ($1, $2)',
        [tenantId, plan]
      );
      break;

    case 'pro':
      // Create schema and run migrations
      await controlPlaneDb.query(`CREATE SCHEMA IF NOT EXISTS tenant_${tenantId}`);
      await runMigrations(`tenant_${tenantId}`);
      break;

    case 'enterprise':
      // Provision RDS instance (via Terraform or AWS SDK)
      const dbUrl = await provisionRDSInstance(tenantId);
      await runMigrations('public', dbUrl);
      await controlPlaneDb.query(
        'INSERT INTO tenant_configs (id, database_url) VALUES ($1, $2)',
        [tenantId, dbUrl]
      );
      break;
  }
}

---

Working With Viprasol

We design multi-tenant architectures for B2B SaaS products — choosing the right isolation pattern for your compliance requirements, implementing RLS policies, building tenant provisioning pipelines, and migrating single-tenant products to multi-tenant at scale.

→ Talk to our team about multi-tenant architecture for your SaaS.

---

See Also

• PostgreSQL Performance Optimization — indexing and query optimization for multi-tenant data
• SaaS Metrics and KPIs — measuring a multi-tenant business
• Database Sharding — horizontal scaling beyond multi-tenancy
• Fintech Compliance Software — compliance requirements that drive isolation choices
• Web Development Services — SaaS architecture and development