Applies toPostgreSQL 13 and later (self-managed and managed services); Citus for distributed multi-tenant workloads
Three models
| Database per tenant | Schema per tenant | Shared tables + tenant_id | |
|---|---|---|---|
| Isolation | Strongest | Medium | Logical (needs RLS or careful code) |
| Tenants per server | Tens to hundreds | Hundreds to low thousands | Many thousands+ |
| Migrations | Run per database | Run per schema | Once |
| Per-tenant restore | Easy | Moderate | Hard (need filtered export or PITR copy) |
| Connection pooling | Harder (per-database pools) | Moderate (search_path) | Easy |
| Cross-tenant reporting | Hard | Moderate | Easy |
| Catalog bloat | Low per DB | Can be large with many schemas | Low |
Choosing
| Situation | Model |
|---|---|
| Few large tenants with contractual isolation, custom versions | Database per tenant (or even server per tenant) |
| Moderate tenant count, need some isolation | Schema per tenant |
| Many small tenants, same schema | Shared tables with RLS |
| Mix of large and small | Hybrid: large tenants dedicated, small ones shared |
Shared tables with row-level security
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.tenant_id')::bigint)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);
-- application sets the tenant per transaction
BEGIN;
SET LOCAL app.tenant_id = '42';
SELECT * FROM invoices; -- only tenant 42 rows
COMMIT;
- The application role must not own the tables or have
BYPASSRLS. - With transaction pooling (PgBouncer), use
SET LOCALinside the transaction. - Put
tenant_idfirst in composite indexes and primary keys.
Noisy neighbours
- Per-tenant statement timeouts and connection limits (roles per tenant tier).
- Monitor by tenant (pg_stat_statements can't split by tenant; log application-level metrics).
- Move large tenants to dedicated databases when they outgrow shared resources.
Scale-out
When one server isn't enough, shard by tenant: application-level routing to several servers, or Citus distributing tables by tenant_id.
Per-tenant operations
| Task | Plan |
|---|---|
| Restore one tenant | Restore a PITR copy, extract tenant rows, merge back carefully |
| Delete tenant data (offboarding, erasure) | Scripted deletes by tenant_id, or drop schema/database |
| Data residency | Tenant placement by region |
Warning
Missing a
tenant_idfilter in one query leaks data between customers. With shared tables, rely on RLS as a safety net, not only on application code.
Checklist
- Model chosen by tenant count, size and isolation needs
- RLS forced on shared tables; app role can't bypass
- Indexes lead with tenant_id
- Noisy neighbour controls
- Per-tenant restore and deletion procedures
- Scale-out path planned