Multi-Tenant Database Design
Architectural patterns for storing multiple customers' data in shared database infrastructure while ensuring isolation, security, and performance fairness.
Description
Multi-tenant database design addresses how SaaS applications store data for multiple customers (tenants) on shared infrastructure. The three primary patterns are: shared database with shared schema (all tenants in the same tables, distinguished by a tenant_id column), shared database with separate schemas (each tenant gets their own PostgreSQL schema within the same database), and separate databases (each tenant gets their own database instance).
Shared schema with tenant_id is the most common and operationally simplest approach. All data lives in the same tables with a tenant_id column on every table. Row-Level Security (RLS) in PostgreSQL can enforce tenant isolation at the database level: CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id')::uuid). This prevents application bugs from leaking data across tenants. The downside is that noisy neighbors can impact performance, and per-tenant backup/restore is complex.
Schema-per-tenant provides stronger isolation (each tenant has their own set of tables) while sharing the same database server. Migrations must be applied to every schema, which adds operational complexity. Separate databases provide the strongest isolation and simplest per-tenant operations but are the most expensive and hardest to manage at scale. Many SaaS companies start with shared schema and migrate high-value or compliance-sensitive tenants to separate schemas or databases as needed.
Prompt Snippet
Implement shared-schema multi-tenancy with a tenant_id UUID column on every table, enforced by PostgreSQL Row-Level Security (RLS). Enable RLS with ALTER TABLE orders ENABLE ROW LEVEL SECURITY and create policies using current_setting('app.tenant_id'). Set the tenant context per request: SET LOCAL app.tenant_id = $1 at the start of each transaction. Add a composite index on (tenant_id, id) as the primary access pattern. Create a tenant_id NOT NULL constraint and a CHECK constraint on every table. For noisy-neighbor protection, implement per-tenant query rate limiting at the application layer and monitor per-tenant query volume via pg_stat_statements with tenant_id injected as a query comment.Tags
Related Terms
Database Sharding
Distributing data across multiple database instances by splitting rows based on a shard key, enabling horizontal scaling beyond the capacity of a single server.
Database Indexing Strategies
Creating and managing indexes to accelerate query performance while balancing write overhead and storage costs.
Connection String Security
Protecting database connection strings containing credentials from exposure in code, logs, and configuration files.