How would you design a database schema to support multi-tenancy?
sql-sen-004
Your answer
Answer as you would in a real interview — explain your thinking, not just the conclusion.
Model answer
There are three canonical approaches. Database-per-tenant gives the strongest isolation and is simplest to back up or restore for one tenant, but operational overhead scales linearly with tenant count. Schema-per-tenant (PostgreSQL schemas or SQL Server schemas) provides logical isolation with less overhead, and you can apply row-level security per schema, but migrations must be fanned out to all schemas. Shared schema with a tenant_id column is the most resource-efficient and is my default for SaaS products with hundreds or thousands of tenants; the tradeoff is that every table must have tenant_id, every query must filter on it, and every index should include it as the leading column to prevent cross-tenant scans. I enforce this with Row Level Security in PostgreSQL (SET LOCAL app.tenant_id = $1; and a policy checking current_setting('app.tenant_id')) so application code cannot accidentally leak data. I also add a tenant_id foreign key constraint rather than relying on application logic alone.
Code example
-- Shared schema: every table carries tenant_id
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES tenants(id),
customer_id BIGINT NOT NULL,
total NUMERIC(12, 2),
created_at TIMESTAMPTZ DEFAULT now()
);
-- Leading column in every index must be tenant_id
CREATE INDEX idx_orders_tenant_customer
ON orders(tenant_id, customer_id);
-- PostgreSQL Row Level Security
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::UUID);
-- Set at the start of each request (e.g. in middleware)
SET LOCAL app.tenant_id = '3f2504e0-4f89-11d3-9a0c-0305e82c3301';Follow-up
How would you handle a situation where one tenant is generating 80% of the query load and starving others? What is 'noisy neighbour' mitigation in the context of shared-schema multi-tenancy?