Multi-Tenant Database Design: Scaling SaaS Platforms Beyond Operational Debt

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Tenant-first composite index
CREATE INDEX idx_projects_tenant_created ON projects (tenant_id, created_at DESC);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

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

-- Grant policy enforcement to application role
GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO app_user;

import { AsyncLocalStorage } from 'async_hooks';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const tenantContext = new AsyncLocalStorage<string>();

export function withTenant<T>(tenantId: string, fn: () => Promise<T>): Promise<T> {
  return tenantContext.run(tenantId, fn);
}

export function getTenantId(): string {
  const id = tenantContext.getStore();
  if (!id) throw new Error('Tenant context not initialized');
  return id;
}

eSQL RLS.

import { PoolClient } from 'pg';

export function setupTenantMiddleware(client: PoolClient) {
  const originalQuery = client.query.bind(client);
  
  client.query = async (text: any, params?: any) => {
    const tenantId = getTenantId();
    // Set session variable for RLS evaluation
    await originalQuery(`SET app.current_tenant = '${tenantId}'`);
    return originalQuery(text, params);
  };
  
  return client;
}

Step 5: Connection Pool Configuration

Pooled architectures concentrate tenant queries on a single connection pool. Misconfigured pools cause starvation under burst traffic. Use PgBouncer in transaction mode with explicit session limits.

# pgbouncer.ini
[databases]
saas_db = host=localhost port=5432 dbname=saas_db

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50
reserve_pool_size = 10
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits

Step 6: Query Routing & Background Jobs

Background workers must explicitly initialize tenant context before processing jobs. Omitting this causes cross-tenant data writes or RLS violations.

import { jobsQueue } from './queue';

jobsQueue.process('generate-report', async (job) => {
  const { tenantId, reportId } = job.data;
  
  return withTenant(tenantId, async () => {
    const report = await db.query.reports.findFirst({
      where: eq(reports.id, reportId)
    });
    // RLS automatically filters by tenant_id
    return generatePDF(report);
  });
});

Architecture Decisions & Rationale

• RLS over application filters: Database-enforced isolation survives ORM updates, raw queries, and direct DB access. Application-level WHERE clauses are fragile and easily bypassed.
• AsyncLocalStorage over request object threading: Eliminates prop-drilling, prevents context leakage in async callbacks, and aligns with Node.js execution model.
• Tenant-first indexes: Ensures B-tree scans remain bounded. PostgreSQL query planner uses leading index columns for partition pruning and index-only scans.
• PgBouncer transaction mode: Reduces connection overhead while maintaining session isolation. Session mode breaks RLS state across pooled connections.

Pitfall Guide

1. Missing tenant_id in Composite Indexes

Placing tenant_id as a secondary index column forces PostgreSQL to scan the entire index for cross-tenant ranges. Always lead with tenant_id in tenant-scoped tables. Fix: CREATE INDEX idx_table_tenant_col ON table (tenant_id, col);

2. RLS Bypass via ORM Raw Queries

Drizzle, Prisma, and TypeORM allow raw SQL execution that skips query builders. If raw queries omit SET app.current_tenant, RLS fails silently. Fix: Enforce middleware at the connection pool level, not the ORM layer. Audit raw query usage in CI.

3. Context Leakage in Background Jobs

AsyncLocalStorage is request-scoped. Jobs processed outside HTTP context lose tenant binding. Fix: Explicitly wrap job handlers in withTenant(tenantId, fn). Never rely on implicit context in workers.

4. Connection Pool Starvation

Pooled architectures concentrate traffic on fewer connections. Burst traffic causes timeout waiting for connection errors. Fix: Use PgBouncer transaction mode, set reserve_pool_size, and monitor pg_stat_activity for wait events.

5. Cross-Tenant Analytics Deadlocks

Running unbounded COUNT(*) or SUM() across all tenants without tenant_id filters causes lock contention and temp table spill. Fix: Enforce tenant-scoped analytics via materialized views or ClickHouse/BigQuery sync. Never run cross-tenant queries on the primary OLTP pool.

6. Backup Granularity Misalignment

Pooled databases backup entire clusters. Tenant-scoped restores require logical dumps or PITR replay. Fix: Implement tenant-aware logical exports (pg_dump --schema=public --where="tenant_id = '...'") for compliance, and use physical backups for DR.

7. Over-Provisioning Siloed Databases Prematurely

Creating separate databases per tenant before hitting compliance or scale thresholds multiplies operational overhead. Fix: Start with pooled RLS. Migrate to bridge or silo only when audit requirements or tenant count (>50k) justify the cost.

Production Bundle

Action Checklist

• Enable PostgreSQL Row-Level Security on all tenant-scoped tables
• Implement AsyncLocalStorage for request-bound tenant context
• Add tenant-first composite indexes to every tenant-scoped table
• Configure PgBouncer in transaction mode with reserve pool sizing
• Wrap all background job handlers in explicit withTenant() calls
• Audit raw SQL execution paths for RLS context injection
• Set up tenant-scoped logical backup scripts for compliance exports
• Monitor pg_stat_activity for connection wait events and lock contention

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
<10k tenants, standard SaaS	Pool (Shared Schema + RLS)	Lowest operational overhead, fastest iteration	Low
10k-50k tenants, mixed compliance	Bridge (Separate Schemas)	Isolates noisy tenants, simplifies tenant-scoped backups	Medium
>50k tenants or strict regulatory	Silo (Separate DBs)	Physical isolation meets SOC2/HIPAA audit requirements	High
Multi-region deployment	Pool + Geo-Partitioning	Reduces cross-region latency while maintaining logical isolation	Medium-High
High-write analytics tenant	Pool + Materialized Views	Offloads heavy reads from OLTP without schema fragmentation	Low

Configuration Template

PostgreSQL RLS Setup

-- Enable RLS globally
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- Base isolation policy
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Admin override policy (optional)
CREATE POLICY admin_access ON projects
  USING (current_setting('app.is_admin', true) = 'true');

-- Grant execution to app role
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Node.js Context & Pool Initialization

import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';
import { AsyncLocalStorage } from 'async_hooks';

export const tenantContext = new AsyncLocalStorage<string>();

export const pool = new Pool({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

export const db = drizzle(pool);

export function withTenant<T>(tenantId: string, fn: () => Promise<T>): Promise<T> {
  return tenantContext.run(tenantId, fn);
}

// Middleware for Express/Fastify
export function tenantMiddleware(req: any, res: any, next: any) {
  const tenantId = req.headers['x-tenant-id'] as string;
  if (!tenantId) return res.status(401).json({ error: 'Missing tenant header' });
  
  return withTenant(tenantId, async () => {
    const client = await pool.connect();
    await client.query(`SET app.current_tenant = '${tenantId}'`);
    req.dbClient = client;
    res.on('finish', () => client.release());
    next();
  });
}

Quick Start Guide

1. Initialize PostgreSQL with RLS: Run the RLS configuration template against your target database. Create the app_user role and assign table permissions.
2. Deploy PgBouncer: Configure pgbouncer.ini with transaction pooling, point it to your PostgreSQL instance, and start the service on port 6432.
3. Bootstrap Node.js Context: Install pg, drizzle-orm, and async_hooks. Copy the context and middleware template into your application entry point.
4. Attach Tenant Header: Send x-tenant-id: <uuid> with every API request. Verify RLS enforcement by querying a table without the header (should return 0 rows).
5. Validate Isolation: Insert tenant-scoped records, switch headers, and confirm cross-tenant visibility is blocked. Monitor pg_stat_activity to verify session variable injection.