Designing Multi-Tenant SaaS Databases: Row-Level Security vs Schema-per-Tenant
The definitive guide to multi-tenant database architecture. Compare shared-table Row-Level Security (RLS), isolated schema-per-tenant, and dedicated database clusters for enterprise B2B SaaS.
1. The Multi-Tenancy Conundrum: Isolation vs Operational Cost
When architecting a B2B SaaS platform, tenant isolation is your most critical security mandate. If Tenant A can accidentally view, modify, or delete Tenant B's data due to a missing `WHERE tenant_id = ...` clause in an API query, your company faces catastrophic brand damage and regulatory liability.
Engineering teams generally choose between three primary architectures:
1. Shared Database, Shared Schema with Row-Level Security (RLS).
2. Shared Database, Isolated Schema-per-Tenant (`schema_tenant_123`).
3. Isolated Database-per-Tenant (Sovereign Database Clusters).
Each approach carries distinct trade-offs in operational complexity, infrastructure spend, migration velocity, and compliance boundaries.
2. Deep Dive: Shared Schema with PostgreSQL Row-Level Security (RLS)
In 2026, Shared Schema with PostgreSQL Row-Level Security (RLS) has become the gold standard for 90% of B2B SaaS platforms. Rather than relying on application developers to remember to include `WHERE tenant_id = ?` in every ORM call, the database engine enforces tenant filtering at the kernel level.
Even if a rogue SQL injection vulnerability or bug occurs in your application code, PostgreSQL automatically intercepts the query and strips any rows that do not match the authenticated tenant session variable.
Here is an enterprise-tested PostgreSQL RLS policy configuration:
-- 1. Enable RLS on core tables
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
-- 2. Define session context variable
-- Application middleware sets this on every connection:
-- SELECT set_config('app.current_tenant_id', 'org_881923a', true);
-- 3. High-performance RLS Policy
CREATE POLICY tenant_isolation_projects ON projects
FOR ALL
USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID)
WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID);
-- 4. Mandatory index to prevent full-table sequential scans
CREATE INDEX idx_projects_tenant_id ON projects (tenant_id);3. When Schema-per-Tenant Makes Sense
In Schema-per-Tenant architecture, all tenants share a PostgreSQL cluster, but each customer has a dedicated namespace (`CREATE SCHEMA tenant_acme`).
Advantages:
• Easy backup and restore of individual customer accounts.
• Clear physical separation satisfying certain mid-market enterprise audits.
Disadvantages:
• Database migrations become nightmarish: running a migration on 5,000 schemas requires running 5,000 separate DDL statements, often taking hours.
• PostgreSQL catalog memory bloat degrades performance when exceeding 2,000 schemas.
4. The Enterprise Tier: Sovereign Dedicated Databases
For Fortune 500 enterprise clients who demand dedicated encryption keys (CMEK), custom disaster recovery targets, and guaranteed physical hardware isolation, offer an Enterprise Sovereign Tier.
Using automated infrastructure-as-code (Terraform / Crossplane), your platform provisions a dedicated PostgreSQL instance on demand, configuring an independent VPC peering connection and streaming private audit logs directly to the customer's SIEM.
Does Row-Level Security add query latency?
When properly indexed on `tenant_id`, PostgreSQL's query optimizer treats the RLS policy as an indexed filter condition, introducing less than 1% query overhead.
How do background worker jobs handle RLS?
Background jobs should run under a dedicated `service_role` user that bypasses RLS (`ALTER USER worker_role BYPASSRLS`), or explicitly set `app.current_tenant_id` for each processed task.
Can Supabase RLS be used for B2B SaaS?
Yes! Supabase natively leverages PostgreSQL RLS, integrating JWT claims directly with `auth.jwt() ->> 'tenant_id'` for seamless multi-tenant enforcement.