The Multi-tenancy Decision That Haunts You
The architecture decision you make on day one of a SaaS product is one you'll live with for years. We've made both choices across different projects and migrated between them once (never again). Here's everything we know.
The Three Approaches
1. Separate Database per Tenant — Each customer gets their own database instance. Maximum isolation, easy compliance, nightmare operations at scale.
2. Schema-per-Tenant (PostgreSQL) — Each tenant gets a dedicated schema within the same database. Good isolation, manageable operations, migration complexity.
3. Shared Schema with Row-Level Security — All tenants share tables, isolated by a tenant_id column enforced via PostgreSQL RLS policies. Best for scale, lowest operational overhead.
When Schema-per-Tenant Wins
Use schema-per-tenant when your customers have compliance requirements (HIPAA, SOC 2, GDPR with data residency), need customized database schemas per tenant, or when you have fewer than 1,000 tenants with high revenue per account.
-- Creating a new tenant schema
CREATE SCHEMA tenant_acme;
-- Each tenant gets identical table structure
CREATE TABLE tenant_acme.users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);The migration story for each schema change is painful — you're running ALTER TABLE across potentially hundreds of schemas.
When Row-Level Security Wins
RLS is the right choice for most B2B SaaS applications targeting SMB customers, where you'll have thousands of tenants and need to move fast.
-- Enable RLS on the users table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- Policy: users can only see rows matching their tenant
CREATE POLICY tenant_isolation ON users
USING (tenant_id = current_setting('app.current_tenant')::UUID);
-- Set the tenant context at the start of each request
SET LOCAL app.current_tenant = 'acme-uuid-here';With RLS, a single migration touches one table. At 10,000 tenants, this is the only sane approach.
The Hybrid Approach We Now Recommend
For new SaaS products, we start with RLS (shared schema) but design for future tenant isolation. We keep a tenant_id on every table, use RLS for data isolation, and define clear interfaces between tenant-specific and shared data. If a large enterprise customer needs schema isolation later, we can offer it as a premium tier without rebuilding the core product.
Performance Implications
RLS adds a predicate to every query. With proper indexing on tenant_id columns, the overhead is negligible. Without it, you'll see full table scans.
-- Essential — index every tenant_id column
CREATE INDEX CONCURRENTLY idx_users_tenant_id ON users(tenant_id);
CREATE INDEX CONCURRENTLY idx_orders_tenant_id ON orders(tenant_id);At FundFlow, we handle 50,000 tenant rows in a single table with sub-10ms query times after proper indexing.
The Migration We'll Never Do Again
We once migrated a 200-tenant product from schema-per-tenant to shared schema RLS. It took 3 engineers 6 weeks, required 4 hours of downtime, and produced 47 database migrations. Design your multi-tenancy strategy correctly from the start.
Our Recommendation
For new SaaS: start with shared schema RLS, index every tenant_id, set the tenant context in your application middleware, and write integration tests that verify isolation. Reserve schema-per-tenant for enterprise tiers with genuine compliance requirements.
