Skip to main content

Multi-Tenancy: Pooled vs Siloed

Design data isolation and resource allocation for SaaS systems

TL;DR​

Pooled tenancy: all tenants in one database with tenant_id column enforced at query-time and database-level via row-level security (cost-effective, complex isolation). Siloed tenancy: separate database per tenant (simple isolation via database boundary, higher infrastructure cost). Hybrid: pooled for small tenants (S), siloed for enterprise customers (E) who require data residency or compliance guarantees. Choose based on isolation requirements, compliance obligations, cost sensitivity, and blast radius tolerance. Real-world architectures typically use 80% pooled + 20% siloed.

Learning Objectives​

  • Understand multi-tenancy patterns (pooled, siloed, hybrid) and isolation models
  • Design tenant_id enforcement at query, ORM, and database levels
  • Implement row-level security (RLS) policies to prevent data leaks
  • Recognize trade-offs between cost, complexity, compliance, and scale
  • Choose pooled, siloed, or hybrid approach based on customer tier and regulations
  • Estimate infrastructure cost and operational complexity for each model
  • Design for tenant blast radius: contain failures to single tenant or group
  • Handle tenant onboarding, migration, and offboarding safely

Strategies Compared​

Pooled Tenancy
  1. All tenants in one database
  2. Tenant column in every table
  3. Lower infrastructure cost
  4. Efficient resource utilization
  5. Requires strong isolation enforcement
Siloed Tenancy
  1. Separate database per tenant
  2. Complete data isolation
  3. Compliance/regulatory advantage
  4. Higher infrastructure cost
  5. Easier to scale individual tenants

Patterns and Pitfalls​

Design Checklist​

  • Multi-tenancy strategy chosen (pooled/siloed/hybrid)
  • Tenant isolation enforced at database level
  • Row-level security policies in place
  • All queries filter by tenant_id
  • Joins don't cross tenant boundaries
  • Audit logging includes tenant_id
  • Backup/restore process respects tenants
  • Compliance requirements documented
  • Cost model matches strategy
  • Data migration/onboarding process clear

Self-Check​

  • What's the advantage of siloed vs pooled tenancy?
  • How do you prevent tenant data leaks in pooled systems?
  • When would you use hybrid multi-tenancy?
  • What's a tenant blast radius and how do you limit it?
One Takeaway

Pooled tenancy is cost-effective but requires disciplined isolation enforcement; siloed is simpler but expensive. Use hybrid for SaaS: pooled for small customers, siloed for enterprise.

Motivating Scenario​

Your SaaS platform grows from 5 enterprise customers to 50,000 small businesses. Early pooled model worked: one database, tenant_id in every table, cost ~$500/month. Now storage is 500GB, and a bug in the order service exposed one customer's data to another. Enterprise customers demand data residency compliance (GDPR, HIPAA); isolation bugs are unacceptable. You shift to hybrid: small customers pooled (99% of them) with strict row-level security, enterprise customers siloed (1% but 40% of revenue) with dedicated databases. Cost rises to $2,000/month, but blast radius shrinks: a pooled customer's outage affects 500 others, an enterprise's outage affects only themselves. Complexity increases (managing 50 separate schemas/databases), but compliance and reliability improve. The trade-off is unavoidable; your choice determines risk profile, margins, and scalability.

Core Concepts​

Tenant Isolation Boundaries: Physical (separate database/server), logical (schema/schema per tenant), or application-level (tenant_id column, RLS). Physical isolation is simple but costly; application-level is efficient but requires discipline.

Tenant Blast Radius: How many other tenants are affected by a single tenant's outage or security breach. Pooled: all tenants affected. Siloed: only that tenant. Hybrid: mitigates by tier.

Row-Level Security (RLS): Database-enforced policy that automatically filters rows by tenant_id. Prevents application bugs from leaking data; acts as safety net.

Tenant-Aware ORM: ORM automatically injects tenant_id into WHERE clauses and enforces filters at query time. Reduces human error.

Compliance and Regulatory Demands: GDPR (right to be forgotten), HIPAA (audit trails, data residency), SOC 2 (access controls, encryption at rest). Siloed model simplifies compliance; pooled requires additional controls.

Practical Example​

-- Tenant-aware schema
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    name TEXT NOT NULL,
    email TEXT,
    UNIQUE (tenant_id, email)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    customer_id INT NOT NULL REFERENCES customers(id),
    amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (tenant_id, customer_id) REFERENCES customers(tenant_id, id)
);

CREATE INDEX idx_orders_tenant ON orders(tenant_id);
CREATE INDEX idx_customers_tenant ON customers(tenant_id);

-- Row-Level Security: automatic filtering
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: users can only access their tenant's data
CREATE POLICY customers_isolation ON customers
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY orders_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Enforce at connection time
SET app.current_tenant = 'a1b2c3d4-e5f6-47g8-h9i0-j1k2l3m4n5o6';  -- Customer A

SELECT * FROM customers;  -- Only returns rows with tenant_id = a1b2c3d4...
SELECT * FROM orders;     -- Only returns orders for that tenant

-- Application code sets tenant context on every query
-- PostgreSQL connection pool:
-- 1. Get connection from pool
-- 2. SET app.current_tenant = :tenant_id
-- 3. Execute query (RLS filters automatically)
-- 4. Return connection to pool

Patterns & Pitfalls (Expanded)​

Pattern: Tenant Column Everywhere, Enforced at Query Level
Add tenantid to every table. Always filter: WHERE tenantid = context.tenant. Use database views or application layer to abstract. Reduces bugs vs magic tenant context.
Pattern: Row-Level Security as Safety Net
Enable RLS on all tenant tables. Automatic filtering prevents bugs in queries or joins that forget tenant_id. Acts as last line of defense against data leaks.
Pattern: Schema Per Tenant (Pooled Alternative)
Same tables in separate schemas per tenant. Queries: SET searchpath TO schemaacme; SELECT * FROM orders; Isolation via schema boundary; simpler than RLS, but more management overhead.
Pattern: Hybrid Tiered Approach
Pooled for small customers (80%, low risk), siloed for enterprise (20%, high revenue/compliance). Route at connection time. Cost-effective; scales to 100k+ tenants.
Pitfall: Tenant Leak via Forgot WHERE Clause
Bug in service: SELECT * FROM orders; returns all tenants' data. RLS prevents this. Application-level filtering alone is insufficient; always add RLS.
Pitfall: Cross-Tenant Joins
Joining customers to orders without tenantid filter can leak data. Always: JOIN orders ON orders.customerid = customers.id AND orders.tenantid = customers.tenantid.
Pitfall: Backup/Restore Exposes Data
Logical backup of pooled DB exports all tenants. Use logical dumps filtered by tenant_id, or physical snapshots. Verify backups don't contain other tenants' data.
Pitfall: Expensive Siloed Overhead
Each siloed database costs $500-2000/month (licensing, storage, backups, monitoring). 50 tenants → $25k/month. Use hybrid: 49 pooled + 1 siloed for true enterprise.

Extended Design Checklist​

  • Multi-tenancy strategy chosen (pooled/siloed/hybrid) based on compliance and cost?
  • Tenant isolation enforced at database level (RLS, schema) and application level (WHERE clauses)?
  • Row-level security policies correctly filter all tenant tables?
  • All queries filter by tenant_id; no unintended cross-tenant joins?
  • Tenant context set on every request (header, JWT claim, or session)?
  • Foreign key constraints prevent orphaned rows across tenants?
  • Audit logging includes tenant_id on all data modifications?
  • Backup/restore process respects tenant boundaries; backups encrypted?
  • Compliance requirements documented (GDPR, HIPAA, SOC 2, data residency)?
  • Cost model matches strategy; estimated cost per tenant at scale?
  • Data migration/onboarding process clear (pooled → siloed, tenant offboarding)?
  • Monitoring and alerting aware of tenant blast radius (per-tenant metrics)?
  • Tenant-aware ORM or query builder prevents accidental unfiltered queries?

Self-Check (Extended)​

  • How does row-level security prevent tenant data leaks in pooled systems?
  • When would you choose siloed tenancy despite higher cost?
  • How do you migrate a tenant from pooled to siloed without downtime?
  • What is tenant blast radius, and why does it matter?
  • How do you ensure backups don't expose data across tenants?
  • What compliance regulations require siloed or schema-per-tenant isolation?

One Takeaway (Extended)​

Pooled tenancy is cost-effective but requires disciplined isolation enforcement at application and database levels. Siloed is simpler but expensive. Hybrid (pooled for SMBs, siloed for enterprise) balances cost, compliance, and risk—use it for SaaS at scale. Always include RLS as a safety net; application-level filtering alone is insufficient. Tenant blast radius is a key decision criterion: tolerate cross-tenant impact for small customers, isolate for high-value or regulated ones.

Next Steps​

References​