Schema-per-Tenant vs Shared-Schema: Which to Choose?
Compare schema-per-tenant and shared-schema multi-tenant database designs, their trade-offs, and when to use each.
Expected Interview Answer
Schema-per-tenant gives each tenant their own set of tables (a namespace) inside one shared database instance, while shared-schema puts every tenant’s rows into the same tables distinguished only by a tenant_id column — schema-per-tenant offers stronger isolation and simpler per-tenant restore, while shared-schema is cheaper to operate and migrate at very large tenant counts.
With schema-per-tenant, a query never needs a tenant filter because the tenant’s schema already scopes it, and restoring or exporting one tenant is just dumping their schema, but running a migration means altering the same tables across every tenant’s schema, and connection pooling gets harder as schema count grows into the thousands. With shared-schema, one migration touches one set of tables regardless of tenant count, and connection pooling is trivial, but every query must reliably include the tenant_id filter (ideally enforced by row-level security), and a single noisy tenant can degrade performance for everyone sharing the same tables.
- Schema-per-tenant: strong isolation and easy single-tenant backup/restore
- Shared-schema: cheap to run and simple to migrate at massive tenant scale
- Schema-per-tenant avoids relying on query-level tenant filtering
- Shared-schema keeps connection pool usage predictable regardless of tenant count
AI Mentor Explanation
Schema-per-tenant is like giving every cricket club its own labeled kit room within one shared clubhouse — nothing gets mixed up, and clearing out one club’s gear at season end is simple. Shared-schema is like storing every club’s kit in one giant shared rack, sorted only by a name tag on each item, which is cheaper to build but risks mixing kits if a tag is misread. The choice between them is exactly the isolation-versus-cost trade-off engineers weigh for tenant data.
Step-by-Step Explanation
Step 1
Estimate tenant count and growth
Schema-per-tenant becomes unwieldy for connection pooling and migrations beyond a few thousand tenants.
Step 2
Evaluate isolation requirements
Choose schema-per-tenant when regulatory or contractual isolation is required per tenant.
Step 3
Plan migration tooling
For schema-per-tenant, build automation to apply DDL changes across every tenant schema consistently.
Step 4
Enforce shared-schema safety
If choosing shared-schema, enforce tenant_id filtering with row-level security, not just application code.
What Interviewer Expects
- Clear articulation of the isolation-vs-operational-cost trade-off
- Awareness of migration complexity differences between the two patterns
- Understanding of connection pooling limits with many schemas
- Ability to recommend a pattern based on tenant count and compliance needs
Common Mistakes
- Claiming one pattern is always better without considering tenant scale
- Ignoring connection pool exhaustion risk with thousands of schemas
- Assuming shared-schema is automatically insecure without row-level security
- Underestimating migration tooling effort for schema-per-tenant
Best Answer (HR Friendly)
“Schema-per-tenant gives each customer their own set of tables, which is safer and easier to isolate but harder to migrate and scale to thousands of customers. Shared-schema puts everyone in the same tables with a tenant ID column, which is cheaper and simpler to run at scale but needs careful enforcement so one customer never sees another’s data.”
Code Example
-- Schema-per-tenant: each tenant gets its own schema
CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.orders (order_id SERIAL PRIMARY KEY, total NUMERIC);
CREATE SCHEMA tenant_globex;
CREATE TABLE tenant_globex.orders (order_id SERIAL PRIMARY KEY, total NUMERIC);
-- Shared-schema: one table for every tenant, filtered by tenant_id
CREATE TABLE public.orders (
order_id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL,
total NUMERIC
);
SELECT * FROM public.orders WHERE tenant_id = 'acme-uuid-here';Follow-up Questions
- How many schemas can a typical database instance handle before pooling becomes a problem?
- How would you migrate all tenant schemas consistently after a DDL change?
- What role does row-level security play in a shared-schema design?
- When would you promote a tenant from shared-schema to its own schema?
MCQ Practice
1. What is the main advantage of schema-per-tenant over shared-schema?
Schema-per-tenant isolates each tenant’s tables, making per-tenant restore and isolation straightforward compared to shared tables.
2. What is a common operational challenge of schema-per-tenant at large scale?
Every schema change must be rolled out to each tenant schema, and having many schemas strains connection pooling.
3. In a shared-schema design, what is essential to prevent cross-tenant data leaks?
Since all tenants share the same tables, every query must be scoped by tenant_id, best enforced at the database layer.
Flash Cards
What is schema-per-tenant? — Each tenant gets a dedicated schema (set of tables) inside one shared database instance.
What is shared-schema? — All tenants share the same tables, distinguished by a tenant_id column.
Why is shared-schema cheaper at scale? — One migration and one connection pool serve all tenants, regardless of tenant count.
Why is schema-per-tenant safer? — Queries are naturally scoped to a tenant’s own tables, removing reliance on a filter being present.