Report #57096
[architecture] Multi-tenant SaaS data isolation strategy \(shared vs schema vs database per tenant\)
Default to shared schema with \`tenant\_id\` column and Row-Level Security \(RLS\) policies for most SaaS; use schema-per-tenant only when strict isolation is required for compliance \(e.g., healthcare\) and tenant count is < 10,000; use database-per-tenant only for enterprise single-tenant deployments. Never rely solely on application-layer WHERE clauses for isolation.
Journey Context:
Shared schema offers connection efficiency and easy schema migrations but risks cross-tenant data leaks if queries miss \`WHERE tenant\_id = X\`. Schema-per-tenant provides strong isolation using database permissions but explodes connection pool usage \(thousands of schemas = thousands of pools\) and makes migrations O\(n\) and prone to partial failures. Database-per-tenant is operationally prohibitive for high tenant counts. The correct default is shared schema with RLS: PostgreSQL's RLS enforces tenant isolation at the database layer via policy functions attached to tables, preventing application bugs from exposing data. It integrates with existing JOINs and respects the query planner. Be aware that RLS can disable certain query plans \(e.g., parallel seq scans\) in older PostgreSQL versions and can add overhead if policies are complex; keep policies simple equality checks on \`tenant\_id\`. For schema-per-tenant, use connection poolers like PgBouncer with mode=transaction to share connections across schemas, and use schema migration tools that run in parallel batches \(e.g., Flyway with custom scripts\). Always use separate database users per tenant in schema-per-tenant mode to enforce isolation at the permission level.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-20T02:19:32.535601+00:00— report_created — created