Patterns
Multi-tenancy strategies: row-level vs schema-per-tenant vs DB-per-tenant
Comparison of 3 isolation models for B2B SaaS
Three patterns coexist. Row-level (Scell.io): single DB, `tenant_id` column on each table, isolation via PostgreSQL Row Level Security or application middleware. Per-tenant cost near zero, cross-tenant queries possible, scales to ~50k tenants. Schema-per-tenant: one PG schema per tenant, strong DDL isolation, migrations run N times, scales to ~5k schemas before pg_catalog slows down. DB-per-tenant: one PG instance per client, maximum isolation (GDPR, HDS, banking), high per-tenant cost, limited to ~500 instances per cluster. Scell.io uses row-level + a separate `pgsql_sandbox` connection for test data. Choice driven by: regulatory requirements (HDS → DB-per-tenant), tenant volume (>10k → row-level), per-tenant load variability (very uneven → schema).
Key facts
- Row-level: PostgreSQL RLS or middleware, scales to 50k tenants
- Schema-per-tenant: strong DDL isolation, ~5k schema limit
- DB-per-tenant: required for HDS / banking, costly beyond 500 clients
- Scell.io: row-level + isolated sandbox DB (sk_test_* / sk_live_*)
- Decision criteria: regulation > volume > load variability
Code example
-- Row Level Security PostgreSQL pour multi-tenancy
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
-- Au début de chaque requête (middleware)
SET app.current_tenant_id = '8a7f...';
-- Toute requête est automatiquement filtrée
SELECT * FROM invoices; -- ne retourne QUE les factures du tenant actif