1. The Hidden Cost of Schema-Per-Tenant
Running `prisma migrate deploy` or `flyway migrate` across 2,000 separate schemas can take over 45 minutes, creating dangerous lock contention. Furthermore, shared connection pools like PgBouncer struggle when connections must frequently execute `SET search_path` before every query.
2. Bulletproof RLS Implementation Pattern
Instead of relying on application-level `WHERE tenant_id = ?` clauses which developers can forget in complex joins, we enforce database-level policies:
CREATE POLICY tenant_isolation_policy ON organizations
AS RESTRICTIVE USING (tenant_id = current_setting('app.current_tenant_id')::uuid);Every API request establishes its tenant context at transaction start via `SET LOCAL app.current_tenant_id = '...'`, ensuring absolute cryptographic separation.3. Indexing Rules for RLS Performance
Every composite index must lead with `(tenant_id, created_at, ...)`. This enables the Postgres query planner to prune 99.9% of index pages before evaluating secondary filter expressions.
Production Checklist & Takeaways
- Use composite indexes with `tenant_id` as the leading column on all shared tables.
- Use `SET LOCAL` so tenant context automatically clears when connection returns to the pool.
- Audit RLS policies with automated regression tests that verify cross-tenant queries fail with 0 rows.