Multi-Tenancy

One Database, Many Customers

Multi-tenancy is the pattern where a single application serves multiple customers (tenants) using shared infrastructure. The database is where multi-tenancy gets interesting, because the stakes are high: tenant data must be isolated. A bug that leaks one customer's data to another is a security incident, a trust violation, and possibly a legal liability.

PostgreSQL supports multiple multi-tenancy strategies, each with different trade-offs in isolation, complexity, and operational overhead. The right choice depends on your scale, compliance requirements, and how much operational burden you can afford.

Strategy 1: Shared Schema with tenant_id

The simplest and most common approach. All tenants share the same tables. Every table has a tenant_id column, and every query includes a WHERE tenant_id = ? filter.

CREATE TABLE organizations (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    plan text NOT NULL DEFAULT 'free',
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE projects (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL REFERENCES organizations(id),
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE tasks (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL REFERENCES organizations(id),
    project_id bigint NOT NULL REFERENCES projects(id),
    title text NOT NULL,
    status text NOT NULL DEFAULT 'open',
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Every query filters by tenant_id
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant ON tasks (tenant_id);

-- Application queries always include tenant_id
SELECT * FROM projects WHERE tenant_id = 42;
SELECT * FROM tasks WHERE tenant_id = 42 AND status = 'open';

-- Composite indexes for common queries
CREATE INDEX idx_tasks_tenant_status ON tasks (tenant_id, status);

Strengths

• Simple to implement and understand
• Easy to query across tenants (for admin dashboards, analytics)
• Standard connection pooling and caching work normally
• Single set of migrations for all tenants
• Scales to thousands of tenants without operational overhead

Weaknesses

• No database-level isolation. A missing WHERE clause leaks data.
• Noisy neighbor: one tenant's large query affects all tenants
• Cannot give tenants different database configurations
• Backup and restore is all-or-nothing (cannot restore a single tenant easily)

Strategy 2: Row-Level Security (RLS)

RLS builds on the shared schema approach by making Postgres enforce tenant isolation at the database level. Even if application code forgets the WHERE clause, the database prevents data leakage.

-- Enable RLS on tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Create policies that restrict access by tenant_id
CREATE POLICY tenant_isolation_projects ON projects
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

CREATE POLICY tenant_isolation_tasks ON tasks
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

The application sets the tenant context at the start of each request:

-- At the beginning of each request/transaction
SET LOCAL app.tenant_id = '42';

-- Now all queries are automatically filtered
SELECT * FROM projects;         -- Only returns tenant 42's projects
SELECT * FROM tasks;            -- Only returns tenant 42's tasks

-- Even a bare SELECT * cannot leak data
SELECT * FROM projects;         -- Still filtered by RLS policy

-- For admin users who need cross-tenant access
CREATE ROLE admin_role;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
-- Or grant BYPASSRLS to the admin role
ALTER ROLE admin_role BYPASSRLS;

RLS with INSERT and UPDATE

-- Policy for inserts: ensure tenant_id matches the current tenant
CREATE POLICY tenant_insert_projects ON projects
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

-- Policy for updates: cannot change tenant_id
CREATE POLICY tenant_update_projects ON projects
    FOR UPDATE
    USING (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

-- Policy for deletes
CREATE POLICY tenant_delete_projects ON projects
    FOR DELETE
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

Performance Considerations

RLS adds a filter to every query. Postgres can usually push this filter down efficiently, especially with proper indexes:

-- Ensure tenant_id is the leading column in indexes
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_status ON tasks (tenant_id, status);

-- Check that RLS uses indexes
EXPLAIN SELECT * FROM tasks WHERE status = 'open';
-- Should show: Index Scan using idx_tasks_tenant_status
-- Filter includes: tenant_id = current_setting('app.tenant_id')

Strategy 3: Schema-per-Tenant

Each tenant gets its own PostgreSQL schema. Tables are identical across schemas, but data is completely isolated at the schema level.

-- Create a schema for each tenant
CREATE SCHEMA tenant_42;
CREATE SCHEMA tenant_43;

-- Create tables in each schema
CREATE TABLE tenant_42.projects (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE tenant_43.projects (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

The application sets the search_path to route queries to the correct schema:

-- Set search path at connection/transaction start
SET search_path TO tenant_42, public;

-- Now queries hit the tenant's schema automatically
SELECT * FROM projects;  -- Reads from tenant_42.projects

Strengths

• Strong isolation at the database level (schemas are separate namespaces)
• Easy to back up and restore individual tenants
• Can have per-tenant indexes or configurations
• No risk of cross-tenant data leakage from missing WHERE clauses

Weaknesses

• Schema count scales linearly with tenant count. 10,000 tenants = 10,000 schemas, each with its own set of tables. This stresses the catalog.
• Migrations must be applied to every schema. 100 tenants with 50 tables each means 5000 table alterations per migration.
• Connection pooling is harder. PgBouncer in transaction mode does not preserve search_path across transactions. You need session mode or SET search_path in every transaction.
• Cross-tenant queries require explicit schema references or UNION ALL across schemas.

-- Cross-tenant query (admin dashboard)
SELECT 'tenant_42' AS tenant, count(*) FROM tenant_42.projects
UNION ALL
SELECT 'tenant_43' AS tenant, count(*) FROM tenant_43.projects;

Schema-per-tenant works well for 10-100 tenants. Beyond that, the operational overhead becomes significant.

When to Use Which

For most SaaS applications: start with shared schema + RLS. You get the simplicity of a single schema with database-enforced isolation. Move to schema-per-tenant only if you have a small number of high-value tenants with strict isolation requirements.

The "Forgot WHERE tenant_id = ?" Bug

This is the most dangerous bug in multi-tenant applications. A developer writes a query, forgets the tenant filter, and the query returns (or modifies) data belonging to all tenants.

-- The bug: missing tenant_id filter
SELECT * FROM orders WHERE status = 'pending';
-- Returns pending orders for ALL tenants

-- Worse: an UPDATE without tenant_id
UPDATE orders SET status = 'cancelled' WHERE created_at < '2026-01-01';
-- Cancels orders for ALL tenants

Defenses

1. Row-Level Security (the strongest defense)

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

With RLS, the bare query returns only the current tenant's data regardless of what the developer wrote.

2. Application-level middleware

Set the tenant context at the middleware level so every database call includes it:

-- Middleware pseudocode:
-- 1. Extract tenant_id from JWT or session
-- 2. SET LOCAL app.tenant_id = extracted_id
-- 3. Execute the request handler
-- 4. Commit/rollback transaction

3. Code review and linting

Automated checks that every query against a tenant table includes a tenant_id filter. Some ORMs support default scopes that automatically add the filter.

4. Testing

Create test scenarios with two tenants and verify that tenant A cannot see tenant B's data. Run these tests in CI.

Testing Tenant Isolation

-- Test setup: two tenants with known data
INSERT INTO organizations (id, name) OVERRIDING SYSTEM VALUE VALUES (1, 'Tenant A');
INSERT INTO organizations (id, name) OVERRIDING SYSTEM VALUE VALUES (2, 'Tenant B');

INSERT INTO projects (tenant_id, name) VALUES (1, 'Project Alpha');
INSERT INTO projects (tenant_id, name) VALUES (2, 'Project Beta');

-- Test: Tenant A should not see Tenant B's data
SET LOCAL app.tenant_id = '1';
SELECT count(*) FROM projects;  -- Should return 1

SET LOCAL app.tenant_id = '2';
SELECT count(*) FROM projects;  -- Should return 1

-- Test: cannot insert data for another tenant
SET LOCAL app.tenant_id = '1';
INSERT INTO projects (tenant_id, name) VALUES (2, 'Sneaky Project');
-- Should fail with RLS policy violation

Include these tests in your CI pipeline. Run them against every migration. Tenant isolation is not something you verify once and forget.

Common Pitfalls

• Relying solely on application code for isolation. Application bugs happen. A single missing WHERE clause leaks data. Use RLS as a database-level safety net.
• Not indexing tenant_id as the leading column. Without a (tenant_id, ...) composite index, queries scan all tenants' data before filtering. This kills performance.
• Schema-per-tenant at scale. 10,000 schemas with 50 tables each means 500,000 catalog entries. Postgres catalog performance degrades, migrations take hours, and monitoring becomes a nightmare.
• Forgetting tenant context in background jobs. Web requests set the tenant context, but cron jobs and background workers often do not. Every code path that touches tenant data must set the context.
• Not testing isolation. "We use RLS" is not proof of isolation. Automated tests that verify tenant A cannot access tenant B's data are proof. Run them in CI.
• Allowing tenant_id to be set by the client. The tenant_id should come from the authenticated session, not from request parameters. A client that can set their own tenant_id can access any tenant.
• PgBouncer transaction mode with schema-per-tenant. Transaction mode resets SET commands between transactions. Either use session mode (less efficient) or SET search_path inside every transaction.

Key Takeaways

• Shared schema with tenant_id is the simplest and most common multi-tenancy pattern. It scales to thousands of tenants with minimal overhead.
• Row-Level Security (RLS) adds database-enforced isolation to the shared schema pattern. It prevents data leaks even when application code forgets the tenant filter.
• Schema-per-tenant provides strong isolation but has high operational overhead. Use it for 10-100 high-value tenants with strict compliance requirements.
• The "forgot WHERE tenant_id = ?" bug is the most dangerous bug in multi-tenant systems. RLS is the strongest defense against it.
• Always index tenant_id as the leading column in composite indexes. Without this, cross-tenant scans destroy performance.
• Test tenant isolation in CI. Create two tenants, insert known data, and verify that each tenant can only see their own data.