What are Multi-Tenant Database Architecture Patterns?
Compare shared-schema, schema-per-tenant, and database-per-tenant multi-tenant patterns and their isolation trade-offs.
Expected Interview Answer
Multi-tenant database architecture describes the strategies a SaaS system uses to store many customers (tenants) in a shared database platform, ranging from one dedicated database per tenant to a fully shared schema with a tenant-ID column, each trading isolation for operational simplicity differently.
At one extreme, "database per tenant" gives each customer a fully isolated database — strongest security and easiest per-tenant backup/restore, but expensive to operate at thousands of tenants because every schema migration must run thousands of times. At the other extreme, a "shared schema" puts every tenant in the same tables behind a `tenant_id` column enforced by row-level security or application filters, which is cheap to run and easy to migrate but risks cross-tenant data leaks if a single query forgets the filter. "Schema per tenant" sits in between, isolating tenants by schema/namespace within one database instance. Real systems often mix tiers: small tenants share, large or regulated tenants get dedicated isolation.
- Lets teams match isolation level to compliance and size per tenant
- Shared-schema tiers keep infrastructure cost low at scale
- Dedicated tiers satisfy strict regulatory or noisy-neighbor requirements
- Hybrid tiering supports growth from free-tier to enterprise customers
AI Mentor Explanation
A cricket academy can house every trainee in one shared dormitory with lockers labeled by name, or build a separate cottage for each trainee, or give each trainee a private room within one shared building. The shared dormitory is cheapest to run but a mislabeled locker exposes another trainee’s gear. Multi-tenant database patterns offer this same range: a shared table filtered by tenant ID, a dedicated schema per tenant within one instance, or a fully separate database per tenant.
Step-by-Step Explanation
Step 1
Assess isolation requirements
Determine per-tenant compliance, security, and noisy-neighbor tolerance before picking a pattern.
Step 2
Choose a base pattern
Select shared-schema, schema-per-tenant, or database-per-tenant as the default tier for most tenants.
Step 3
Enforce tenant boundaries
Use row-level security, mandatory tenant_id filters, or connection-level isolation to prevent cross-tenant leaks.
Step 4
Plan for tiering and migration
Allow upgrading a tenant to a stricter isolation tier as they grow or require compliance guarantees.
What Interviewer Expects
- Comparison of shared-schema, schema-per-tenant, and database-per-tenant patterns
- Understanding of the isolation-vs-operational-cost trade-off
- Awareness of row-level security or mandatory tenant filters as leak prevention
- Ability to justify tiering tenants by size or compliance need
Common Mistakes
- Assuming one pattern fits every tenant regardless of scale or compliance
- Forgetting to enforce the tenant filter at the database layer, not just application code
- Ignoring migration cost when choosing database-per-tenant at scale
- Not considering hybrid tiering for mixed customer sizes
Best Answer (HR Friendly)
“Multi-tenant database architecture is about deciding how much isolation each customer gets: a shared table with a tenant-ID column is cheap but riskier, a dedicated schema per tenant is a middle ground, and a fully separate database per tenant is the most isolated but costliest to operate. Most SaaS companies mix these tiers based on customer size and compliance needs.”
Code Example
-- Shared table with a tenant_id column
CREATE TABLE Orders (
order_id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL,
customer_name TEXT,
total NUMERIC
);
-- Enable row-level security so every query is scoped to one tenant
ALTER TABLE Orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON Orders
USING (tenant_id = current_setting('app.current_tenant')::UUID);Follow-up Questions
- How does row-level security prevent cross-tenant data leaks?
- When would you migrate a tenant from shared-schema to a dedicated database?
- How do you run schema migrations across thousands of tenant databases?
- What is a "noisy neighbor" problem in multi-tenant systems?
MCQ Practice
1. Which multi-tenant pattern gives the strongest data isolation per tenant?
Database-per-tenant gives each tenant a fully separate database, the strongest isolation among the common patterns.
2. What is the main operational cost of database-per-tenant at scale?
Every schema change must be applied to each tenant database individually, which becomes expensive with thousands of tenants.
3. What mechanism helps prevent cross-tenant leaks in a shared-schema design?
Row-level security enforces tenant scoping at the database layer, so even a missed application filter cannot expose other tenants.
Flash Cards
What is shared-schema multi-tenancy? — All tenants share the same tables, distinguished by a tenant_id column.
What is database-per-tenant? — Each tenant gets a fully separate, isolated database instance.
What is schema-per-tenant? — Each tenant gets its own schema/namespace inside one shared database instance.
Why tier multi-tenant patterns? — To balance operational cost against isolation and compliance needs across tenants of different sizes.