Choosing Shared Schema with Row-Level Security
For B2B SaaS with a large number of small-to-medium customers, shared schema with PostgreSQL row-level security enforced at the database level is our default recommendation. Application-level WHERE tenant_id = ? filtering is a liability — a single forgotten clause is a data leak. Database-level RLS eliminates this class of bug entirely.
RLS Setup
Enable RLS on every tenant-data table: ALTER TABLE orders ENABLE ROW LEVEL SECURITY. Create the isolation policy: CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting(‘app.tenant_id’)::uuid). Set the session variable on every database connection before executing queries: SET LOCAL app.tenant_id = ‘the-tenant-uuid’. With this configuration, a query that omits a WHERE tenant_id clause still returns only the current tenant’s data.
Indexing Strategy
Every tenant-data table requires a composite index with tenant_id as the leading column. A composite index on (tenant_id, created_at DESC) for the common “latest records for this tenant” query pattern. All additional indexes are also composite — (tenant_id, email) not just (email) — because a single-column index would return rows from all tenants, requiring a filter step the planner cannot push into the index scan.
Safe Migration Patterns
New NOT NULL columns must have a DEFAULT value or be added as nullable and backfilled in a separate transaction before adding the NOT NULL constraint. Never add a NOT NULL column without a default to a large production table — PostgreSQL rewrites every row in the table while holding an exclusive lock. For tables with millions of rows, this means minutes or hours of unavailability.