Implementing Multi-Tenancy Data Models
1. Using Separate Databases per Tenant
| Pros | Cons |
|---|---|
| Strong isolation | High operational overhead |
| Per-tenant backup/restore | Migrations × N DBs |
| Easy data export | Resource waste for small tenants |
| Best for | Enterprise/regulated tenants |
2. Using Separate Schemas per Tenant
| Pros | Cons |
|---|---|
| Logical isolation within one DB | Schema explosion (1000s) |
| search_path switch per request | Catalog bloat |
| Per-schema backup | Cross-tenant queries harder |
| Best for | 10–100 tenants |
3. Using Shared Schema with Tenant ID
| Pros | Cons |
|---|---|
| Single migration path | Stronger isolation needed (RLS) |
| Efficient resource usage | Noisy-neighbor risk |
| Easy cross-tenant analytics | tenant_id must be on every index/query |
| Best for | SaaS scale (1000s of tenants) |
Example: Shared Schema
CREATE TABLE invoices (
tenant_id BIGINT NOT NULL,
id BIGSERIAL,
amount NUMERIC(10,2),
PRIMARY KEY (tenant_id, id)
) PARTITION BY HASH (tenant_id);
CREATE INDEX ON invoices(tenant_id, created_at DESC);4. Implementing Row-Level Isolation
| Mechanism | Detail |
|---|---|
| RLS policy | Filter on current_setting('app.tenant_id') |
| Set per connection | SET app.tenant_id = '42'; |
| Force RLS | Even table owner respects policy |
| Defense in depth | App-side filter + DB RLS |
5. Designing Tenant-Specific Configurations
| Storage | Use |
|---|---|
| tenant_config table | Key-value or JSONB |
| Feature flags | Per-tenant overrides |
| Resource quotas | Limits per tenant (rows, storage) |
| Region pin | tenant.region for data residency |
6. Modeling Shared Reference Data
| Approach | Detail |
|---|---|
| Global table | No tenant_id — read by all (e.g., country list) |
| Tenant override | Optional tenant-specific row |
| Replication | Copy reference data to per-tenant DBs |
| FDW | Foreign table to shared registry |
7. Implementing Tenant Data Partitioning
| Strategy | Detail |
|---|---|
| HASH(tenant_id) | Even distribution |
| LIST(tenant_id) | Pin big tenants to dedicated partition |
| Citus (PG) | Distributed tables sharded by tenant |
| Indexing | Always lead with tenant_id |
8. Designing for Tenant Scalability
| Concern | Mitigation |
|---|---|
| Noisy neighbor | Rate limits, query timeouts, resource governors |
| Power tenants | Promote to dedicated DB/shard |
| Onboard / offboard | Automated provision & export |
| Per-tenant metrics | Track usage for billing + ops |
9. Handling Cross-Tenant Queries
| Use | Approach |
|---|---|
| Internal analytics | Bypass RLS with elevated role |
| Aggregations | Pre-computed in OLAP warehouse |
| Avoid in tenant API | Strict tenant scoping |
| Audit | Log all cross-tenant access |
10. Ensuring Tenant Data Security
| Control | Detail |
|---|---|
| RLS (mandatory) | Defense in depth |
| Per-tenant encryption keys | BYOK / KMS context |
| Audit per tenant | Trace who accessed what |
| Backup isolation | Restore per-tenant without leaking |
| Data residency | Region-pinned storage |