Multi-Tenant SaaS Database Design

If you are building a SaaS product, multi-tenancy is not optional. Get it wrong early and you pay for it in painful migrations.

Pattern 1: Shared Database, Shared Schema

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name TEXT NOT NULL,
  status TEXT DEFAULT 'active'
);
CREATE INDEX idx_projects_tenant_id ON projects(tenant_id);

Pros

• Lowest operational overhead

• Easiest cross-tenant analytics

• Simple connection pooling

Cons

• Data leakage risk without RLS

• Noisy neighbor problem

• Harder per-tenant backup/GDPR deletion

Pattern 2: Shared Database, Separate Schemas

CREATE SCHEMA acme;
CREATE TABLE acme.projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL, status TEXT DEFAULT 'active'
);
-- SET search_path TO acme;

Pros

• Strong logical isolation

• Per-tenant schema customization

• Easier per-tenant backup with pg_dump --schema

Cons

• Schema proliferation at scale

• Migrations must run across every schema

• Cross-tenant analytics requires UNION ALL

Pattern 3: Separate Databases

CREATE DATABASE tenant_acme;
CREATE DATABASE tenant_globex;

Maximum isolation. GDPR deletion is a single DROP DATABASE.

Row-Level Security (RLS)

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

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

-- Set context before queries:
SET LOCAL app.current_tenant_id = '3f2504e0-4f89-11d3-9a0c-0305e82c3301';
SELECT * FROM projects WHERE status = 'active';
-- PostgreSQL automatically adds tenant_id filter

Writing Tenant-Aware Queries Faster

AI2SQL understands your schema and generates tenant-aware queries with correct tenant_id filters and JOIN conditions.

Choosing the Right Pattern

• Shared schema + RLS: Start here. Scales to thousands of tenants.

• Separate schemas: When enterprise customers demand isolation.

• Separate databases: Compliance requirements mandate physical isolation.

Many mature SaaS companies use a hybrid approach. AI2SQL can accelerate generating complex tenant-aware queries.