Report #80609
[architecture] Multi-tenant SaaS database design: Row-Level Security \(RLS\) vs schema-per-tenant performance and isolation tradeoffs
Use 'tenant\_id' column with PostgreSQL RLS policies and declarative table partitioning by hash\(tenant\_id\) for 95% of cases; it provides strong isolation without connection pool exhaustion, and enables efficient tenant eviction via partition detachment.
Journey Context:
Schema-per-tenant offers perfect logical isolation \(separate tables/indexes\) but fails at scale: each schema consumes shared\_buffers entries \(cache pollution\), and connection pools explode because you cannot share connections across schemas efficiently \(search\_path switching is session-level, not statement-level\). Database-per-tenant is worse for connection limits and backup complexity. Row-Level Security \(RLS\) allows shared tables with policy-based filtering \(e.g., 'CREATE POLICY tenant\_isolation ON orders USING \(tenant\_id = current\_setting\('app.current\_tenant'\)::int\)'\). The trap: RLS is not a performance feature; without proper indexes, it causes sequential scans. The solution combines RLS with declarative partitioning on tenant\_id \(PostgreSQL 11\+\). This provides physical isolation at the storage level \(partition pruning\), allows O\(1\) tenant deletion/eviction via 'ALTER TABLE orders DETACH PARTITION tenant\_123 CONCURRENTLY' \(instant, no table scan\), and maintains cache locality. For horizontal scaling, Citus \(now Microsoft\) extends this to shard-per-tenant across nodes. Critical: RLS policies must be marked 'LEAKPROOF' or use 'SECURITY BARRIER' views to prevent timing attacks, and you must set 'app.current\_tenant' per connection \(or use SET LOCAL for transaction-level\) to avoid cross-tenant leakage in connection pools. Never use RLS without indexes on tenant\_id.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-21T17:54:45.410119+00:00— report_created — created