SaaS Multi-Tenant Schema Design
Multi-tenancy is the defining architectural challenge of SaaS applications. Every table, every query, every migration must account for tenant isolation. Get it wrong and you leak data between tenants — the kind of bug that ends companies. This page covers the three major strategies and provides a production-grade schema for the most common approach.
It follows the order you would design in: requirements and access patterns, the isolation strategy (and what each costs to operate and to migrate away from), the entities and tables, row-level security and how it interacts with connection pools, the hot-path queries and their indexes, the concurrency and noisy-neighbour hot spots, billing and entitlements, audit logs, and finally how the schema evolves as the largest tenants outgrow a shared database. If ER modelling is new, read ER Modeling & Schema Design first; for general OLTP principles see OLTP Database Design.
Requirements & Access Patterns
Assume a B2B project-management product: 25,000 tenants (about 22,000 on the free plan, 2,900 on paid self-serve plans, 100 enterprise contracts), 2 million users, and a heavily skewed distribution where the largest tenant holds 8% of all rows and the median tenant holds a few thousand.
| Operation | Steady rate | Peak rate | Read or write | Latency target | Notes |
|---|---|---|---|---|---|
| Resolve tenant from subdomain / token | 20,000/s | 60,000/s | Read | p99 < 5 ms | Every request; cached |
| API key authentication | 8,000/s | 25,000/s | Read | p99 < 5 ms | Lookup by key hash; cached |
| Permission check | 20,000/s | 60,000/s | Read | p99 < 5 ms | Per request; cached per session |
| Task list for a project (filter, sort, page) | 5,000/s | 15,000/s | Read | p99 < 100 ms | The main screen |
| Create / update task | 1,500/s | 5,000/s | Write | p99 < 150 ms | Writes an audit row too |
| "My tasks" across projects | 1,000/s | 3,000/s | Read | p99 < 150 ms | By assignee within tenant |
| Usage metering events | 50,000/s | 200,000/s | Write | Async | Buffered, aggregated before Postgres |
| Audit log writes | 3,000/s | 10,000/s | Write | Async-tolerant | Append-only, retained for years |
| Audit log search (admin console) | 20/s | 100/s | Read | p99 < 2 s | Per tenant, time-bounded |
| Plan-limit checks (seats, projects) | 200/s | 1,000/s | Read + write | p99 < 50 ms | Must not be bypassable by concurrency |
| Billing run / invoice generation | Monthly batch | Read | Hours | Reads usage aggregates | |
| Tenant provisioning | 10/min | 500/min | Write | Seconds | Spikes after marketing campaigns |
| Cross-tenant support / analytics | Rare | Read | Seconds | Separate role, ideally separate store |
What this shapes:
- Every hot query is scoped to one tenant. That makes
tenant_idthe leading column of nearly every index and, later, the natural shard key. - Tenants are wildly unequal. The schema, the planner statistics, and the capacity plan must all cope with one tenant being 10,000 times bigger than another. This is the noisy-neighbour problem.
- Isolation is a correctness property, not a performance one. One missing predicate is a data breach. The design must make the safe path the default path, enforced by the database.
- High-volume streams (usage, audit) behave differently from the core tables. They are append-only, time-ordered, and the first candidates for partitioning or a different store.
Hot queries and the indexes they need:
| # | Hot query | Index |
|---|---|---|
| Q1 | Tenant by slug | UNIQUE (slug) on tenants |
| Q2 | API key by hash | UNIQUE (key_hash) on api_keys |
| Q3 | User by email within tenant (login) | UNIQUE (tenant_id, email) |
| Q4 | Tasks in a project by status, by due date, paginated | (tenant_id, project_id, status, due_date, id) |
| Q5 | Open tasks assigned to me | partial (tenant_id, assignee_id, due_date) WHERE status <> 'done' |
| Q6 | Roles and permissions for a user | user_roles (user_id) plus roles PK |
| Q7 | Audit log for a tenant, newest first, optional resource filter | (tenant_id, created_at DESC), (tenant_id, resource_type, resource_id) |
| Q8 | Current-period usage for a tenant and metric | (tenant_id, metric, period_start) on an aggregate table |
| Q9 | Active subscription and entitlements for a tenant | subscriptions exclusion index; plan_entitlements PK |
Multi-Tenancy Strategies
graph TD
subgraph "Strategy 1: Shared Database, Shared Schema"
A["All tenants share tables<br/>tenant_id column on every row<br/>RLS enforces isolation"]
end
subgraph "Strategy 2: Shared Database, Separate Schemas"
B["Each tenant gets a PostgreSQL schema<br/>tenant_a.users, tenant_b.users<br/>set search_path per connection"]
end
subgraph "Strategy 3: Separate Databases"
C["Each tenant gets own database<br/>Complete isolation<br/>Independent scaling"]
end
style A fill:#e8f5e9,color:#000
style B fill:#fff3e0,color:#000
style C fill:#fce4ec,color:#000
| Strategy | Isolation | Complexity | Cost | When to Use |
|---|---|---|---|---|
| Shared schema + tenant_id | Low (RLS-enforced) | Low | Lowest | Most SaaS apps, any number of tenants (thousands to millions) |
| Schema-per-tenant | Medium | Medium | Medium | Regulated industries, custom schemas; practical up to a few thousand tenants per database |
| Database-per-tenant | Highest | Highest | Highest | Enterprise customers requiring full isolation |
Correction (2026-11-08)
An earlier version of this table recommended the shared schema for "<1000 tenants". That has it backwards. The shared schema is the model that scales best in tenant count: adding a tenant is inserting a row, and a million tenants is no harder than a thousand. It is schema-per-tenant and database-per-tenant that run into limits as tenants multiply, because every tenant adds catalog entries, migration runs, connections and backups. What limits the shared schema is the size of the largest tenants and the blast radius of a mistake, which is what the rest of this page addresses.
The #1 Multi-Tenant Bug
Forgetting WHERE tenant_id = ? on a single query leaks data across tenants. Row-Level Security (RLS) is your safety net — it enforces filtering at the database level, even if the application forgets.
Comparing the strategies in operation
The three options differ less in how the tables look and more in what they cost to run: schema changes, connections, backups, performance isolation, and moving a tenant later.
| Concern | Shared schema + tenant_id | Schema per tenant | Database per tenant |
|---|---|---|---|
| Add a tenant | INSERT a row | CREATE SCHEMA + run all DDL (seconds) | Provision a database (minutes), or pick from a warm pool |
| Schema migration | Once | N times; one failure leaves tenants on mixed versions | N times across N databases; needs an orchestrator and version tracking per tenant |
| Catalog size | Constant | Grows with N × tables × indexes; pg_dump, autovacuum and planning slow past a few thousand schemas | Per database, fine |
| Connections | One pool for everyone | search_path per checkout; pool still shared | One pool per database; connection count multiplies with tenants |
| Cross-tenant queries (support, analytics) | Plain SQL with a bypass role | UNION ALL over N schemas, or ETL | ETL only |
| Restore one tenant | Hard: point-in-time restore elsewhere, copy rows back | Medium: pg_dump -n schema | Easy: restore that database |
| Delete a tenant (GDPR) | Batched deletes across tables | DROP SCHEMA ... CASCADE | DROP DATABASE |
| Noisy neighbour isolation | Weakest; shared buffers, CPU, locks, vacuum | Weak; same instance | Strong if on separate instances |
| Per-tenant customization | Via JSONB or extension tables | Tenant-specific columns possible (and dangerous) | Anything |
| Data residency | Needs a regional deployment per region | Same | Per-tenant placement |
| Leak risk from a missing filter | Highest, so RLS is mandatory | Low (wrong search_path still leaks) | Lowest |
Migration costs between models are the decision people underestimate:
- Shared to database-per-tenant (moving one big customer out) is a copy: select every row with that
tenant_idfrom every table, load it into a fresh database, then cut over. Because every row carriestenant_idand every foreign key is tenant-scoped, this is mechanical. This is the cheapest direction, and the reason to start shared. - Schema-per-tenant to shared means adding
tenant_idto every table in every schema, merging N copies of each table into one, and resolving id collisions (each schema had its own sequences, soid = 1exists 5,000 times). It is a rewrite of the data layer, usually measured in quarters. - Database-per-tenant to shared has the same id-collision problem plus N connection strings to retire. Teams that start here to get "isolation" often regret it at a few hundred tenants, when a single migration takes a day to roll out.
The pragmatic production pattern is a hybrid: everyone starts in the shared pool; tenants that need it (size, contract, residency) are moved to a dedicated database or shard, using the same schema with tenant_id still on every row. A routing table says where each tenant lives:
CREATE TABLE tenant_placements (
tenant_id UUID PRIMARY KEY REFERENCES tenants(id),
cluster TEXT NOT NULL, -- 'pool-us-1', 'dedicated-acme', 'eu-pool-2'
region TEXT NOT NULL, -- data residency
tier TEXT NOT NULL CHECK (tier IN ('pooled', 'dedicated')),
read_only BOOLEAN NOT NULL DEFAULT FALSE, -- set during a move
moved_at TIMESTAMPTZ
);
This table lives in a small global control-plane database, is cached at every API node, and is consulted before a connection is chosen. Keeping the schema identical across pooled and dedicated placements is what makes moves cheap: the same code, the same migrations, and tenant_id in every row so a tenant can be copied out and back.
Schema Design (Shared Schema + tenant_id)
This is the most common approach. All tenants share the same tables, distinguished by a tenant_id column. PostgreSQL Row-Level Security enforces isolation.
Entity Relationship Overview
erDiagram
TENANTS ||--o{ USERS : has
TENANTS ||--o{ ROLES : defines
TENANTS ||--o{ PROJECTS : owns
TENANTS ||--|| BILLING : pays
USERS }o--o{ ROLES : assigned
USERS ||--o{ AUDIT_LOGS : generates
PROJECTS ||--o{ TASKS : contains
BILLING ||--o{ INVOICES : generates
BILLING ||--o{ USAGE_RECORDS : tracks
TENANTS ||--o{ API_KEYS : has
The extended model with plans, subscriptions and entitlements, and with tenant_id shown as part of the keys that matter:
erDiagram
TENANTS ||--o{ SUBSCRIPTIONS : "subscribes"
PLANS ||--o{ SUBSCRIPTIONS : "chosen in"
PLANS ||--o{ PLAN_ENTITLEMENTS : "grants"
FEATURES ||--o{ PLAN_ENTITLEMENTS : "granted as"
TENANTS ||--o{ ENTITLEMENT_OVERRIDES : "has"
FEATURES ||--o{ ENTITLEMENT_OVERRIDES : "overridden"
TENANTS ||--o{ USAGE_COUNTERS : "meters"
TENANTS ||--o{ PROJECTS : "owns"
PROJECTS ||--o{ TASKS : "contains"
USERS ||--o{ TASKS : "assigned"
TENANTS {
uuid id PK
text slug UK
}
PLANS {
text id PK "free, pro, enterprise"
int price_cents
text interval "month, year"
}
FEATURES {
text key PK "max_users, sso, api_access"
text kind "boolean or limit"
}
PLAN_ENTITLEMENTS {
text plan_id PK, FK
text feature_key PK, FK
bigint limit_value "null means unlimited"
}
SUBSCRIPTIONS {
uuid id PK
uuid tenant_id FK
text plan_id FK
tstzrange active_during "no overlaps per tenant"
text status
}
ENTITLEMENT_OVERRIDES {
uuid tenant_id PK, FK
text feature_key PK, FK
bigint limit_value
timestamptz expires_at
}
USAGE_COUNTERS {
uuid tenant_id PK, FK
text metric PK
date period_start PK
bigint quantity
}
PROJECTS {
uuid tenant_id PK, FK
uuid id PK
}
TASKS {
uuid tenant_id PK, FK
uuid id PK
uuid project_id FK "same tenant"
uuid assignee_id FK "same tenant"
}
The key modelling idea, which the tables below implement: tenant-owned entities are weak entities of the tenant. Their identity is effectively (tenant_id, id), and every relationship between two tenant-owned entities carries tenant_id through, so a foreign key cannot point across tenants.
Tenants
CREATE TABLE tenants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
slug TEXT NOT NULL UNIQUE, -- subdomain: acme.app.com
plan TEXT NOT NULL DEFAULT 'free', -- 'free', 'pro', 'enterprise'
settings JSONB DEFAULT '{}', -- feature flags, limits
is_active BOOLEAN DEFAULT TRUE,
trial_ends_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_tenants_slug ON tenants(slug);
idx_tenants_slug is redundant: slug ... UNIQUE already created a unique index on it. Slugs also become subdomains, so constrain them: CHECK (slug ~ '^[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?$'), and keep a reserved list (www, api, admin, status) out of the namespace.
Users & Authentication
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
email TEXT NOT NULL,
password_hash TEXT NOT NULL,
full_name TEXT NOT NULL,
avatar_url TEXT,
is_active BOOLEAN DEFAULT TRUE,
last_login TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (tenant_id, email) -- email unique within tenant, not globally
);
CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE INDEX idx_users_email ON users(tenant_id, email);
Email Uniqueness Scope
In single-tenant apps, email is globally unique. In multi-tenant apps, the same email can exist in different tenants (e.g., alice@company.com is a user in both Acme Corp and Globex Corp tenants). The UNIQUE (tenant_id, email) constraint enforces this correctly.
Note (2026-11-08): two redundant indexes
idx_users_email (tenant_id, email) is an exact duplicate of the index behind UNIQUE (tenant_id, email), and idx_users_tenant (tenant_id) is covered by the same index's leftmost prefix. Neither helps any query; both slow every insert. Drop them.
Tenant-local users versus global accounts. The table above gives each tenant its own user rows: Alice in Acme and Alice in Globex are two users with two passwords. That is right for products where a tenant owns its users (an HR system, an internal tool behind SSO). Products where one person belongs to many workspaces (Slack, GitHub, Notion) use a global identity plus memberships:
CREATE TABLE accounts ( -- global, not tenant-scoped
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL,
password_hash TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX uq_accounts_email ON accounts (lower(email));
CREATE TABLE memberships ( -- tenant-scoped
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('invited', 'active', 'suspended')),
joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (tenant_id, account_id)
);
CREATE INDEX idx_memberships_account ON memberships (account_id); -- "my workspaces"
Choose this early. Converting tenant-local users to global accounts later means deduplicating people by email across tenants, which surfaces every conflicting password, name and SSO identity.
Tenant-safe primary and foreign keys
A plain REFERENCES projects(id) lets a task in tenant A point at a project in tenant B, if a bug or a malicious API call supplies the wrong id. RLS hides the row on read, but the dangling cross-tenant reference is still stored. The fix is to make (tenant_id, id) the referenced key and to carry tenant_id in every foreign key:
-- Make (tenant_id, id) referenceable on parent tables.
ALTER TABLE users ADD CONSTRAINT uq_users_tenant_id UNIQUE (tenant_id, id);
ALTER TABLE projects ADD CONSTRAINT uq_projects_tenant_id UNIQUE (tenant_id, id);
ALTER TABLE roles ADD CONSTRAINT uq_roles_tenant_id UNIQUE (tenant_id, id);
-- A task's project must belong to the task's tenant.
ALTER TABLE tasks
ADD CONSTRAINT fk_tasks_project_same_tenant
FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id)
ON DELETE CASCADE,
-- PostgreSQL 15+: SET NULL only the assignee column, not tenant_id.
ADD CONSTRAINT fk_tasks_assignee_same_tenant
FOREIGN KEY (tenant_id, assignee_id) REFERENCES users (tenant_id, id)
ON DELETE SET NULL (assignee_id);
The ON DELETE SET NULL (assignee_id) column list matters: a plain SET NULL on a composite key would try to null tenant_id as well and fail its NOT NULL. On PostgreSQL versions before 15, handle assignee removal in application code or a trigger. The same reasoning applies to the junction table user_roles, which in the original schema has no tenant_id at all and therefore cannot be protected by RLS directly; it gets one below.
UUIDs or bigints? Random UUIDv4 keys (the gen_random_uuid() default) scatter inserts across the whole B-tree, so every insert touches a random leaf page and the index's working set is the whole index. Time-ordered UUIDv7 keeps the benefits listed in the design-decisions table (globally unique, no enumeration, safe to merge tenants across databases) with insert locality close to a sequence. PostgreSQL 18 ships a uuidv7() function; on older versions generate them in the application. See ID Generation.
Roles & Permissions (RBAC)
CREATE TABLE roles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
name TEXT NOT NULL, -- 'admin', 'editor', 'viewer'
permissions TEXT[] NOT NULL DEFAULT '{}', -- ['projects.read', 'projects.write']
is_default BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (tenant_id, name)
);
CREATE TABLE user_roles (
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, role_id)
);
-- Seed default roles per tenant
-- INSERT INTO roles (tenant_id, name, permissions, is_default) VALUES
-- ($tenant_id, 'admin', ARRAY['*'], false),
-- ($tenant_id, 'member', ARRAY['projects.read','projects.write','tasks.read','tasks.write'], true),
-- ($tenant_id, 'viewer', ARRAY['projects.read','tasks.read'], false);
CREATE INDEX idx_roles_tenant ON roles(tenant_id);
CREATE INDEX idx_user_roles_user ON user_roles(user_id);
Permission check query:
-- Check if user has a specific permission
SELECT EXISTS (
SELECT 1
FROM user_roles ur
JOIN roles r ON r.id = ur.role_id
WHERE ur.user_id = $user_id
AND (
r.permissions @> ARRAY['*'] -- wildcard admin
OR r.permissions @> ARRAY[$permission] -- specific permission
)
) AS has_permission;
Hardening the RBAC tables:
-- Give the junction table its tenant, so RLS and same-tenant FKs apply.
ALTER TABLE user_roles
ADD COLUMN tenant_id UUID NOT NULL,
ADD CONSTRAINT fk_user_roles_user FOREIGN KEY (tenant_id, user_id)
REFERENCES users (tenant_id, id) ON DELETE CASCADE,
ADD CONSTRAINT fk_user_roles_role FOREIGN KEY (tenant_id, role_id)
REFERENCES roles (tenant_id, id) ON DELETE CASCADE;
-- Exactly one default role per tenant.
CREATE UNIQUE INDEX uq_roles_one_default ON roles (tenant_id) WHERE is_default;
idx_user_roles_user is redundant with the primary key (user_id, role_id), and idx_roles_tenant with UNIQUE (tenant_id, name); both can go. The permission check is two primary-key-driven probes and runs 20,000 times a second, so cache its result per session (keyed by user and a role-version number that bumps on any role change) rather than making it faster in SQL. If permissions grow into resource-level sharing ("Bob can edit project X"), add a grants (tenant_id, subject_id, resource_type, resource_id, permission) table; that is the point where RBAC becomes ReBAC, and dedicated authorization services (Zanzibar-style) start to make sense.
Application Data (Projects & Tasks)
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
name TEXT NOT NULL,
description TEXT,
status TEXT NOT NULL DEFAULT 'active', -- 'active', 'archived'
owner_id UUID REFERENCES users(id) ON DELETE SET NULL,
settings JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
title TEXT NOT NULL,
description TEXT,
status TEXT NOT NULL DEFAULT 'todo', -- 'todo', 'in_progress', 'done'
priority SMALLINT DEFAULT 0,
assignee_id UUID REFERENCES users(id) ON DELETE SET NULL,
due_date DATE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_projects_tenant ON projects(tenant_id);
CREATE INDEX idx_tasks_tenant ON tasks(tenant_id);
CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id) WHERE assignee_id IS NOT NULL;
The single-column indexes work at small scale. The hot queries want composites that lead with tenant_id and then follow the query's equality-then-sort shape:
ALTER TABLE tasks
ADD CONSTRAINT chk_task_status CHECK (status IN ('todo', 'in_progress', 'done')),
ADD COLUMN version INT NOT NULL DEFAULT 0; -- optimistic locking for edits
-- Q4: tasks in a project, filtered by status, sorted by due date, keyset-paginated.
CREATE INDEX idx_tasks_project_board
ON tasks (tenant_id, project_id, status, due_date, id);
-- Q5: "my open tasks", soonest due first. Done tasks (the majority) are excluded.
CREATE INDEX idx_tasks_my_open
ON tasks (tenant_id, assignee_id, due_date)
WHERE status <> 'done' AND assignee_id IS NOT NULL;
Why tenant_id first even though project_id is already unique to one tenant? Three reasons: the RLS predicate always supplies it, so the index serves the exact predicate the database evaluates; it keeps each tenant's entries physically clustered in the index, so a tenant's working set is contiguous pages rather than scattered ones; and it is the shard key later, so composite indexes and keys already match the distribution. With these in place, idx_tasks_project, idx_tasks_assignee and idx_tasks_tenant are redundant. The version column supports optimistic locking for the "two people edit the same task" case; see Transactions, ACID & Concurrent Updates.
Billing & Usage
CREATE TABLE billing (
tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
stripe_customer TEXT, -- external billing provider ID
plan TEXT NOT NULL DEFAULT 'free',
billing_email TEXT NOT NULL,
payment_method JSONB, -- last4, brand (not full card!)
current_period_start TIMESTAMPTZ,
current_period_end TIMESTAMPTZ,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE invoices (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
amount_cents INT NOT NULL,
currency TEXT NOT NULL DEFAULT 'USD',
status TEXT NOT NULL DEFAULT 'pending', -- 'pending', 'paid', 'failed'
period_start TIMESTAMPTZ NOT NULL,
period_end TIMESTAMPTZ NOT NULL,
stripe_invoice TEXT, -- external reference
paid_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE usage_records (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
metric TEXT NOT NULL, -- 'api_calls', 'storage_bytes', 'seats'
quantity BIGINT NOT NULL,
recorded_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_invoices_tenant ON invoices(tenant_id, created_at DESC);
CREATE INDEX idx_usage_tenant ON usage_records(tenant_id, metric, recorded_at DESC);
Usage aggregation for billing:
-- Monthly API call count for a tenant
SELECT
tenant_id,
DATE_TRUNC('month', recorded_at) AS month,
SUM(quantity) AS total_api_calls
FROM usage_records
WHERE tenant_id = $1
AND metric = 'api_calls'
AND recorded_at >= DATE_TRUNC('month', NOW())
GROUP BY tenant_id, month;
That aggregation is correct but, at 50,000 usage events per second, usage_records would gain four billion rows a day. Raw events should not land in Postgres one row each. The full billing model, including a pre-aggregated usage table, is in Plans, Subscriptions and Entitlements.
API Keys
CREATE TABLE api_keys (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
name TEXT NOT NULL,
key_hash TEXT NOT NULL UNIQUE, -- SHA-256 of the key
key_prefix TEXT NOT NULL, -- first 8 chars for identification
permissions TEXT[] DEFAULT ARRAY['read'],
expires_at TIMESTAMPTZ,
last_used TIMESTAMPTZ,
created_by UUID REFERENCES users(id),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_api_keys_hash ON api_keys(key_hash);
CREATE INDEX idx_api_keys_tenant ON api_keys(tenant_id);
API key details that matter:
idx_api_keys_hashduplicates the unique index fromkey_hash ... UNIQUE; drop it.- API key lookup happens before the tenant is known, so it cannot go through tenant-scoped RLS. Do it through a narrow
SECURITY DEFINERfunction that takes the hash and returns only(tenant_id, key_id, permissions, expires_at), then set the tenant context from the result. - A fast hash (SHA-256) is fine for API keys because they are long random strings with no dictionary to attack, unlike passwords, which need a slow hash such as Argon2 or bcrypt.
- Do not update
last_usedon every request; that turns a read path into a write per call on a hot row. Update it at most once a minute per key (WHERE last_used < NOW() - INTERVAL '1 minute'), or buffer it. - Add
revoked_at TIMESTAMPTZrather than deleting keys, so audit logs that reference a key id stay meaningful.
Audit Logs
CREATE TABLE audit_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
action TEXT NOT NULL, -- 'user.created', 'project.deleted'
resource_type TEXT NOT NULL,
resource_id UUID,
changes JSONB, -- {"field": {"old": "x", "new": "y"}}
ip_address INET,
user_agent TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Partition by month for performance (audit logs grow fast)
-- In production, use declarative partitioning:
-- CREATE TABLE audit_logs (...) PARTITION BY RANGE (created_at);
CREATE INDEX idx_audit_tenant ON audit_logs(tenant_id, created_at DESC);
CREATE INDEX idx_audit_resource ON audit_logs(tenant_id, resource_type, resource_id);
The production version, partitioned and append-only, is in Audit Logs in Depth.
Row-Level Security (RLS)
RLS is the critical safety net that prevents cross-tenant data leaks at the database level.
Setup
-- 1. Enable RLS on every tenant-scoped table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;
-- 2. Create policies that filter by tenant_id
-- The application sets a session variable: SET app.current_tenant = 'uuid';
CREATE POLICY tenant_isolation ON users
USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY tenant_isolation ON projects
USING (tenant_id = current_setting('app.current_tenant')::uuid);
CREATE POLICY tenant_isolation ON tasks
USING (tenant_id = current_setting('app.current_tenant')::uuid);
-- 3. For the application user (not superuser), RLS is enforced
-- Even if the app forgets WHERE tenant_id = ?, RLS blocks cross-tenant access
-- 4. Set tenant context at the beginning of each request
-- In your connection pool / middleware:
SET app.current_tenant = '550e8400-e29b-41d4-a716-446655440000';
Corrections (2026-11-08): four gaps in the setup above
- Tables with RLS enabled but no policy return nothing.
rolesandaudit_logshave RLS enabled but no policy, and for a non-owner role, "no policy" means default deny: every query returns zero rows. Every RLS-enabled table needs a policy. current_setting('app.current_tenant')raises an error if the setting was never set, and returns an empty string (which fails the::uuidcast) if it was set and later reset. Use the two-argument form withNULLIF, so a missing context yieldsNULL, matches no rows, and fails closed.USINGfilters reads;WITH CHECKfilters writes. A policy with onlyUSINGstill applies its expression to new rows forINSERTandUPDATE(PostgreSQL falls back toUSINGwhenWITH CHECKis absent), but statingWITH CHECKexplicitly makes the intent obvious and survives later edits that split policies per command.- A session-level
SETis dangerous with connection pools. It survives on the physical connection after your request ends and is inherited by the next request that borrows the connection, which may belong to a different tenant. Set the tenant per transaction withset_config(..., true)instead (shown below).
The corrected, complete setup:
-- One helper, so every policy reads the context the same way.
CREATE FUNCTION app_current_tenant() RETURNS uuid
LANGUAGE sql STABLE AS $$
SELECT NULLIF(current_setting('app.current_tenant', true), '')::uuid
$$;
-- Apply to every tenant-scoped table, including the ones that were missing.
DO $$
DECLARE t text;
BEGIN
FOREACH t IN ARRAY ARRAY['users','roles','user_roles','projects','tasks',
'audit_logs','api_keys','invoices','usage_records']
LOOP
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);
EXECUTE format('DROP POLICY IF EXISTS tenant_isolation ON %I', t);
EXECUTE format(
'CREATE POLICY tenant_isolation ON %I
USING (tenant_id = app_current_tenant())
WITH CHECK (tenant_id = app_current_tenant())', t);
END LOOP;
END $$;
FORCE ROW LEVEL SECURITY makes the policies apply to the table owner too. Without it, if the application ever connects as the role that owns the tables (common when the same role runs migrations), RLS silently does nothing.
Two refinements worth knowing:
- Audit logs should be insert-and-read only. Split the policy per command:
FOR SELECT USING (...)andFOR INSERT WITH CHECK (...), with noUPDATEorDELETEpolicy, so even the application role cannot rewrite history. - RLS policies can do more than tenant filtering. A
restrictivepolicy (CREATE POLICY ... AS RESTRICTIVE) is ANDed with the permissive ones, which is how you layer "and only projects the user is a member of" on top of tenant isolation without weakening it.
Connection Pool Integration
-- In Node.js with pg pool:
-- pool.on('connect', async (client) => {
-- await client.query("SET app.current_tenant = $1", [tenantId]);
-- });
-- With Prisma:
-- Use $executeRaw to set session variable before queries
-- await prisma.$executeRaw`SET app.current_tenant = ${tenantId}`;
Correction (2026-11-08): do not set the tenant on connect
The two snippets above are unsafe and, as written, do not work:
pool.on('connect')fires once per physical connection, not once per request. The first tenant to open a connection "owns" it forever, and every later request that borrows that connection, from any tenant, runs as that tenant. That is precisely the cross-tenant leak RLS exists to prevent.SETis a utility command and does not accept bind parameters, soSET app.current_tenant = $1fails with a syntax error, and Prisma's tagged-template$executeRawparameterizes${tenantId}in the same way. Use the function formset_config(name, value, is_local), which is an ordinary function call and takes parameters normally.
The safe pattern is: open a transaction per request (or per unit of work), set the tenant locally inside it, run the queries, commit. The setting disappears at commit or rollback, so a pooled connection never carries a tenant to the next borrower:
BEGIN;
SELECT set_config('app.current_tenant', $1, true); -- true = transaction-local, like SET LOCAL
SELECT set_config('app.current_user', $2, true); -- for audit triggers
-- ... the request's queries ...
COMMIT;
// Node.js (pg): wrap every request's work in this helper.
async function withTenant(pool, tenantId, userId, work) {
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query("SELECT set_config('app.current_tenant', $1, true)", [tenantId]);
await client.query("SELECT set_config('app.current_user', $1, true)", [userId]);
const result = await work(client);
await client.query('COMMIT');
return result;
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}
This is also the pattern that works behind PgBouncer in transaction mode, where consecutive statements outside a transaction may run on different server connections, so a session-level SET would not even reach the next query reliably. See Connection Pooling. With Prisma, run the set_config call and the queries inside one $transaction([...]).
RLS and Superusers
RLS policies do NOT apply to superusers or the table owner. Your application should connect as a non-superuser role. Create a dedicated role:
CREATE ROLE app_user WITH LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- RLS now applies to all queries from app_user
Complete the picture with three roles: a migration role that owns the tables (and is never used by the application at runtime), the app_user role above with NOBYPASSRLS (the default), and a support or analytics role with BYPASSRLS that is used only from audited internal tooling. Combined with FORCE ROW LEVEL SECURITY, no runtime path bypasses isolation by accident.
Testing RLS
Isolation deserves its own automated tests, because a policy bug fails open silently:
-- As app_user: with no tenant set, every tenant table must look empty.
BEGIN;
SET LOCAL ROLE app_user;
SELECT count(*) FROM tasks; -- expect 0
SELECT set_config('app.current_tenant', '<tenant A>', true);
SELECT count(*) FROM tasks WHERE tenant_id = '<tenant B>'; -- expect 0
INSERT INTO tasks (tenant_id, project_id, title)
VALUES ('<tenant B>', '<some project>', 'x'); -- expect: violates row-level security policy
ROLLBACK;
Also add a CI check that queries the catalog for any table with a tenant_id column that does not have RLS enabled and forced:
SELECT c.relname
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id'
WHERE c.relkind IN ('r', 'p') AND c.relnamespace = 'public'::regnamespace
AND NOT (c.relrowsecurity AND c.relforcerowsecurity);
-- Any row returned fails the build.
Hot-Path Queries with EXPLAIN Reasoning
Plans are illustrative and trimmed. For reading plans in depth see Query Planning & Optimization.
Q4: The project board
SELECT id, title, status, priority, assignee_id, due_date
FROM tasks
WHERE tenant_id = $tenant -- explicit, even though RLS adds it too
AND project_id = $project
AND status = 'in_progress'
AND (due_date, id) > ($cursor_due, $cursor_id)
ORDER BY due_date, id
LIMIT 50;
Limit (rows=50)
-> Index Scan using idx_tasks_project_board on tasks
Index Cond: ((tenant_id = 'a1..') AND (project_id = 'p9..')
AND (status = 'in_progress') AND (ROW(due_date, id) > ROW(...)))
Filter: (tenant_id = app_current_tenant())
Three equality columns, then the sort columns, so the index delivers rows already in ORDER BY order and the scan stops at 50. The RLS predicate shows up as an extra Filter (or is merged into the index condition when the planner can). With only the original single-column idx_tasks_project, the plan would fetch every task in the project, filter by status, and sort.
Why to keep the explicit tenant predicate
If the query relies on RLS alone (WHERE project_id = $project), the planner sees tenant_id = app_current_tenant(), a stable function whose value it does not know at planning time. It estimates the selectivity as an average (one over the number of distinct tenants), which is wildly wrong for the 8% tenant. Wrong estimates produce wrong plans: a nested loop that is perfect for a small tenant and catastrophic for the biggest one. Passing tenant_id = $1 explicitly lets the planner use the actual value with the column's most-common-values statistics. RLS remains the safety net; the explicit predicate is for the planner.
Related: with prepared statements, PostgreSQL switches to a generic plan after five executions if it looks no worse on average than the custom ones. With skewed tenants, the average hides the outlier. For queries whose best plan differs by tenant size, set plan_cache_mode = force_custom_plan for those statements or the session; see PostgreSQL Performance Tuning.
Q5: My open tasks
SELECT id, project_id, title, due_date
FROM tasks
WHERE tenant_id = $tenant AND assignee_id = $me AND status <> 'done'
ORDER BY due_date
LIMIT 20;
The partial index idx_tasks_my_open matches the query's status <> 'done' predicate exactly, so the planner can use it. It contains only open, assigned tasks, a small fraction of the table, and is ordered by due_date within each (tenant, assignee). The result is an index scan that returns 20 rows and stops. A partial index is only used when the query's WHERE clause implies the index predicate, so keep the query's wording aligned with the index.
Q2: API key authentication
SELECT tenant_id, id, permissions, expires_at
FROM api_keys
WHERE key_hash = encode(sha256($presented_key::bytea), 'hex')
AND revoked_at IS NULL;
One unique-index probe. In practice it is served from a cache (key hash to tenant and permissions, short TTL, invalidated on revoke), because it runs on every API call.
Q7: Audit search
SELECT created_at, user_id, action, resource_type, resource_id
FROM audit_logs
WHERE tenant_id = $tenant
AND created_at >= $from AND created_at < $to
AND resource_type = 'project' AND resource_id = $project
ORDER BY created_at DESC
LIMIT 100;
On a monthly-partitioned table, the created_at range prunes all but one or two partitions before any index is touched (look for Subplans Removed in the plan). Within the surviving partitions, idx_audit_resource (tenant_id, resource_type, resource_id) narrows to one resource. Adding created_at DESC as a fourth column would remove the sort; for 100 rows of one resource, the sort is negligible.
Concurrency Hot Spots
Per-tenant isolation and noisy neighbours
All tenants in a pool share CPU, memory, I/O, connections, locks, and autovacuum. One tenant's bulk import or runaway report can raise everyone's latency. The schema helps, but most defences are operational:
| Resource a big tenant can exhaust | Defence |
|---|---|
| Connections | Per-tenant concurrency limits in the application pool (a semaphore keyed by tenant), separate pools for API, background jobs and reports |
| CPU and I/O from slow queries | SET LOCAL statement_timeout per request, lower for free plans; reports moved to replicas or the warehouse |
| Request volume | Per-tenant rate limiting at the API gateway, sized by plan; see Rate Limiting Deep Dive |
| Locks | Batch large writes in chunks of 1,000 rows with commits between; no long transactions |
| Vacuum and bloat | Big tenants' bulk deletes run in batches; monitor dead tuples per table |
| Buffer cache | Tenant-leading indexes keep each tenant's pages contiguous, so a big tenant's scan evicts less of everyone else |
| Planner misestimates | Explicit tenant_id predicates, extended statistics, custom plans for skewed queries |
Attribution comes first. pg_stat_statements aggregates by query shape, not tenant, so you cannot see "tenant X is burning 40% of CPU". Tag queries with a comment (/* tenant=acme, route=/tasks */, the sqlcommenter convention) and log slow queries with it, or record per-tenant request time in the application. Once you can see the outlier, the long-term fix for a tenant that is consistently a large fraction of the load is to move it to dedicated capacity using tenant_placements.
Plan limits under concurrency
The limit check shown later in Plan Limits & Feature Gating counts users, then the application inserts if under the limit. Two concurrent invitations on a plan with one seat left both see 4 of 5 and both insert: 6 users on a 5-seat plan. This is the classic read-decide-write race. Two fixes:
-- Fix A: serialize limit-checked inserts per tenant by locking the tenant row.
BEGIN;
SELECT 1 FROM tenants WHERE id = $tenant FOR UPDATE;
-- Count and compare here; only one transaction per tenant gets this far at a time.
INSERT INTO users (...) SELECT ... WHERE (SELECT count(*) FROM users WHERE tenant_id = $tenant) < $limit;
COMMIT;
-- Fix B: keep a counter with a CHECK, and let the conditional update decide.
CREATE TABLE tenant_quotas (
tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
seats_used INT NOT NULL DEFAULT 0,
seats_max INT NOT NULL,
CHECK (seats_used <= seats_max)
);
UPDATE tenant_quotas SET seats_used = seats_used + 1
WHERE tenant_id = $tenant AND seats_used < seats_max
RETURNING seats_used;
-- 0 rows: limit reached. Otherwise insert the user in the same transaction.
Fix B is the same conditional-update pattern that prevents overselling in E-Commerce Schema Design: the check and the change are one statement, and the second concurrent transaction re-evaluates the WHERE after the first commits.
Tenant-wide locks and long transactions
Avoid patterns that lock "the tenant" for long: a tenant-row FOR UPDATE held across a slow operation blocks every limit-checked action for that tenant. Keep such transactions to a few milliseconds. For large per-tenant operations (imports, bulk updates, tenant deletion), process in batches with a job table and FOR UPDATE SKIP LOCKED so several workers can share the work without stepping on each other.
Plan Limits & Feature Gating
-- Store limits in tenant settings JSONB
UPDATE tenants
SET settings = jsonb_build_object(
'max_users', 5,
'max_projects', 10,
'max_storage_mb', 500,
'features', jsonb_build_object(
'api_access', false,
'custom_domain', false,
'sso', false
)
)
WHERE plan = 'free';
-- Check limit before creating a user
SELECT
(SELECT COUNT(*) FROM users WHERE tenant_id = $1) <
(settings->>'max_users')::int AS can_add_user
FROM tenants
WHERE id = $1;
Copying limits into every tenant's JSONB works for a single tier, but it means a pricing change rewrites thousands of rows and nothing records which tenant has a custom deal. As pricing matures, model plans and entitlements as data.
Plans, Subscriptions and Entitlements
Three separate concepts that early schemas conflate:
- A plan is a product in the price list: "Pro, $20 per seat per month".
- A subscription is a tenant's contract for a plan over a period, with a status driven by the billing provider.
- An entitlement is what the tenant may actually do right now: the plan's grants plus any per-tenant overrides (a sales deal, a trial extension, a beta feature).
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE plans (
id TEXT PRIMARY KEY, -- 'free', 'pro_2026', 'enterprise'
name TEXT NOT NULL,
price_cents INT NOT NULL CHECK (price_cents >= 0),
interval TEXT NOT NULL CHECK (interval IN ('month', 'year')),
is_public BOOLEAN NOT NULL DEFAULT TRUE, -- legacy plans stay but are hidden
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE features (
key TEXT PRIMARY KEY, -- 'max_users', 'sso', 'api_access'
kind TEXT NOT NULL CHECK (kind IN ('boolean', 'limit'))
);
CREATE TABLE plan_entitlements (
plan_id TEXT NOT NULL REFERENCES plans(id),
feature_key TEXT NOT NULL REFERENCES features(key),
enabled BOOLEAN NOT NULL DEFAULT TRUE,
limit_value BIGINT, -- NULL = unlimited for 'limit' features
PRIMARY KEY (plan_id, feature_key)
);
CREATE TABLE subscriptions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
plan_id TEXT NOT NULL REFERENCES plans(id),
status TEXT NOT NULL CHECK (status IN
('trialing', 'active', 'past_due', 'canceled', 'expired')),
seats INT NOT NULL DEFAULT 1 CHECK (seats > 0),
active_during TSTZRANGE NOT NULL,
provider_sub_id TEXT UNIQUE, -- Stripe subscription id
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- A tenant cannot have two overlapping subscriptions.
EXCLUDE USING gist (tenant_id WITH =, active_during WITH &&)
);
CREATE TABLE entitlement_overrides (
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
feature_key TEXT NOT NULL REFERENCES features(key),
enabled BOOLEAN NOT NULL DEFAULT TRUE,
limit_value BIGINT,
reason TEXT NOT NULL, -- 'deal:ACME-2026', 'trial extension'
expires_at TIMESTAMPTZ,
PRIMARY KEY (tenant_id, feature_key)
);
The effective entitlements are a view: the active subscription's plan grants, with overrides winning.
CREATE VIEW effective_entitlements AS
SELECT s.tenant_id, pe.feature_key,
COALESCE(o.enabled, pe.enabled) AS enabled,
COALESCE(o.limit_value, pe.limit_value) AS limit_value
FROM subscriptions s
JOIN plan_entitlements pe ON pe.plan_id = s.plan_id
LEFT JOIN entitlement_overrides o
ON o.tenant_id = s.tenant_id AND o.feature_key = pe.feature_key
AND (o.expires_at IS NULL OR o.expires_at > NOW())
WHERE s.active_during @> NOW()
AND s.status IN ('trialing', 'active', 'past_due');
Design notes:
- Plans are versioned by id, never edited. Changing what Pro includes means creating
pro_2027and migrating tenants deliberately; existing customers keep their contract terms ("grandfathering"). - The billing provider is the source of truth for money; your database is the source of truth for access. Stripe webhooks update
subscriptions; deduplicate them with abilling_events (provider, event_id) UNIQUEtable, exactly like payment webhooks in e-commerce, because they are delivered at least once and out of order. Compare the event's timestamp or the provider object's version before applying it. past_duekeeps access for a grace period. Revoking access the moment a card fails creates support tickets; the status models the grace period explicitly.- Cache entitlements per tenant (they change rarely) and invalidate on subscription or override changes. The view runs on cache misses only.
Metered usage. Raw usage events go to a buffer (Redis counters, a Kafka topic) and are flushed as aggregates, one row per tenant, metric and period, with an idempotent upsert:
CREATE TABLE usage_counters (
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
metric TEXT NOT NULL,
period_start DATE NOT NULL, -- billing day or hour bucket
quantity BIGINT NOT NULL DEFAULT 0 CHECK (quantity >= 0),
PRIMARY KEY (tenant_id, metric, period_start)
);
-- Flush from the buffer, idempotent per (flush batch id) tracked by the flusher.
INSERT INTO usage_counters (tenant_id, metric, period_start, quantity)
VALUES ($tenant, 'api_calls', $day, $n)
ON CONFLICT (tenant_id, metric, period_start)
DO UPDATE SET quantity = usage_counters.quantity + EXCLUDED.quantity;
At 50,000 events per second across 25,000 tenants, flushing once a minute produces at most a few hundred upserts per second, and Q8 ("usage this period") becomes a primary-key range read. Keep the raw events in an analytics store if customers need per-call detail.
Audit Logs in Depth
Audit logs are append-only, high-volume, rarely read, and must be trustworthy. That combination suggests partitioning, write-only permissions, tamper evidence, and per-plan retention.
CREATE TABLE audit_events (
tenant_id UUID NOT NULL,
id UUID NOT NULL, -- UUIDv7, time-ordered
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
actor_type TEXT NOT NULL CHECK (actor_type IN ('user', 'api_key', 'system', 'support')),
actor_id UUID,
action TEXT NOT NULL, -- 'task.updated'
resource_type TEXT NOT NULL,
resource_id UUID,
changes JSONB,
ip_address INET,
user_agent TEXT,
request_id TEXT,
prev_hash BYTEA, -- hash chain for tamper evidence
row_hash BYTEA,
PRIMARY KEY (tenant_id, created_at, id)
) PARTITION BY RANGE (created_at);
CREATE TABLE audit_events_2026_11 PARTITION OF audit_events
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE INDEX ON audit_events (tenant_id, resource_type, resource_id, created_at DESC);
-- The application may insert and read, never modify.
REVOKE UPDATE, DELETE, TRUNCATE ON audit_events FROM app_user;
GRANT INSERT, SELECT ON audit_events TO app_user;
Why this shape:
- The primary key includes the partition key (
created_at), which PostgreSQL requires for unique constraints on partitioned tables, and leads withtenant_idso per-tenant time-range reads (Q7) are a single index range in each partition. - No foreign keys to
usersortenants. Audit records must outlive the things they describe; a deleted user's actions remain attributable by id. (The original table'sON DELETE CASCADEfrom tenants deletes a tenant's audit trail along with the tenant, which may violate retention obligations; choose deliberately.) - Monthly partitions make retention a metadata operation.
DETACH PARTITION ... CONCURRENTLY, export to object storage as Parquet, then drop. Retention can differ by plan (90 days on Pro, 7 years on Enterprise) by exporting per tenant before dropping, or by keeping enterprise tenants' events in a separate store. - Tamper evidence with a hash chain. Each row stores
row_hash = sha256(prev_hash || canonical_row), chained per tenant. Changing or deleting any historical row breaks every later hash, and a periodic job that verifies the chain (and publishes the latest hash somewhere external) makes silent edits detectable even by a database administrator. Chaining per tenant serializes writes per tenant; if that becomes a bottleneck, chain per tenant per hour and link the hourly roots. - Write it in the same transaction as the change when the audit record is a compliance requirement, using a trigger that reads
app.current_userandapp.current_tenantfrom the transaction-local settings. If audit volume threatens the primary, write to an outbox in the same transaction and let a consumer move events to a dedicated audit store; see Transactional Outbox.
At large scale, audit search across months moves to a columnar store such as ClickHouse or a search engine, with Postgres keeping only recent partitions.
Performance Considerations
| Concern | Solution |
|---|---|
Every query needs tenant_id | RLS handles it; composite indexes always start with tenant_id |
| Large tenants dominate shared tables | Monitor per-tenant row counts; consider sharding for outliers |
| Audit log growth | Partition by month; archive old partitions to cold storage |
| API key lookup speed | Index on key_hash; cache validated keys in Redis (TTL 5 min) |
| Usage metering overhead | Batch usage records (buffer in Redis, flush every minute) |
| Cross-tenant admin queries | Superuser bypasses RLS; or create a separate admin role with different policies |
| Planner misestimates for skewed tenants | Explicit tenant_id = $1 predicates alongside RLS; custom plans for skewed statements |
| Random UUID insert locality | UUIDv7 keys keep B-tree inserts on the right-hand edge |
| Noisy neighbours | Per-tenant rate limits, statement timeouts by plan, separate pools, move outliers to dedicated capacity |
| Tenant deletion | Mark inactive immediately; delete in batches from a job; DROP if on a dedicated database |
For cross-tenant admin work, prefer a dedicated role with BYPASSRLS over connecting as a superuser: it bypasses row security without also granting the power to alter the database.
Schema Design Decisions
| Decision | Rationale |
|---|---|
| UUID primary keys | Globally unique; safe for cross-tenant exports/imports; no sequential ID leakage |
tenant_id on every table | Core tenant isolation mechanism; enables RLS |
| RBAC over ABAC | Role-based is simpler and sufficient for most SaaS; attribute-based adds complexity |
permissions as TEXT[] | Simple, fast array containment check; good enough for 95% of apps |
| JSONB for tenant settings | Plan limits vary by tier; JSONB avoids schema changes for new limits |
Separate billing table | Billing data has different access patterns and sensitivity than tenant metadata |
| Audit log partitioning | Audit logs are append-only and grow indefinitely; partitioning enables efficient pruning |
key_hash not key_value | API keys are like passwords; store only the hash, show the key once at creation |
Composite (tenant_id, id) foreign keys | Cross-tenant references become impossible, not just invisible |
| Transaction-local tenant context | Pooled connections never carry one tenant's context into another's request |
FORCE ROW LEVEL SECURITY | Policies apply even if the app connects as the table owner |
| Plans, entitlements and overrides as tables | Pricing changes and custom deals are data, not code or bulk JSONB rewrites |
| Exclusion constraint on subscriptions | Overlapping subscriptions for one tenant are impossible |
| Aggregated usage counters | Billing reads a few rows per tenant, not billions of raw events |
Evolution as It Scales
| Stage | Symptom | Change |
|---|---|---|
| 1. One shared database | Early product | Shared schema, RLS, tenant-leading indexes |
| 2. Replicas and caches | Read load | Cache tenant, entitlements, permissions and API keys; route reports and exports to replicas (Replication) |
| 3. Streams out | Usage and audit volume | Usage buffered and aggregated; audit partitioned, then moved to a columnar store |
| 4. Outlier tenants out | One tenant dominates load or needs a contract-level guarantee | Move it to a dedicated database via tenant_placements |
| 5. Shard by tenant | Pooled tenants exceed one primary | Distribute all tenant tables by tenant_id (Citus) or a shard directory over many Postgres clusters |
| 6. Regional cells | Data residency, blast radius | Independent stacks per region or cell; a global control plane holds tenants and placements |
tenant_id is the natural shard key for B2B SaaS: every hot query includes it, tenants rarely interact, and co-locating all of a tenant's tables on one shard keeps joins and transactions local. In Citus terms, every tenant table is a distributed table with tenant_id as the distribution column, all co-located, while small global tables (plans, features) are reference tables replicated to every node. Because primary keys and foreign keys already include tenant_id, the schema needs no changes to distribute. See Sharding for the Citus model and the hot-shard problem.
The hot-shard problem is real here: hashing tenants spreads tenants evenly, not load. The largest tenant can still overwhelm its shard, which is why stage 4 (dedicated placement) precedes or accompanies stage 5. Very large tenants can even be sub-sharded by a second key (tenant_id, project_id), at the cost of cross-shard queries within that tenant.
Moving a tenant between placements is a routine operation, so make it boring: mark the tenant read_only in tenant_placements (or use logical replication filtered by tenant_id for a near-zero-downtime move), copy every table's rows for that tenant, verify counts and checksums, flip the placement, invalidate caches, and delete from the source in batches. Every row having tenant_id and every key including it is what makes this mechanical.
Migration Strategy for Multi-Tenancy
If you're adding multi-tenancy to an existing single-tenant app:
- Add
tenant_idcolumn (nullable) to all tables - Create a default tenant, assign all existing rows to it
- Make
tenant_idNOT NULL - Add composite indexes starting with
tenant_id - Enable RLS with policies
- Update all queries (or rely on RLS as safety net)
- Update application to set
app.current_tenantper request
On large tables, do each step without long locks (see Database Migrations):
- Step 2 in batches.
UPDATE ... SET tenant_id = $default WHERE id BETWEEN $a AND $bin chunks of a few thousand rows with commits between, not one statement that rewrites the table in a single transaction. Better still, add the column withDEFAULT '<default tenant uuid>', which in PostgreSQL 11+ is a metadata-only change for existing rows. - Step 3 without a full-table lock.
ALTER TABLE ... ADD CONSTRAINT tenant_id_not_null CHECK (tenant_id IS NOT NULL) NOT VALID, thenVALIDATE CONSTRAINT(which takes a weaker lock), thenSET NOT NULL, which PostgreSQL 12+ can do without a scan when a validated check constraint already proves it. - Step 4 with
CREATE INDEX CONCURRENTLY, and swap unique constraints to their tenant-scoped versions (UNIQUE (email)becomesUNIQUE (tenant_id, email)) by building the new unique index concurrently, then attaching it withADD CONSTRAINT ... UNIQUE USING INDEX. - Step 5 in report-only mode first. Before enabling enforcement, run the application against a staging copy as
app_userwith RLS forced, and log any query that returns fewer rows than expected. - Add the composite
(tenant_id, id)foreign keys withNOT VALIDand validate them afterwards; they are what keep the migrated data from ever acquiring cross-tenant references.
Common Mistakes
- No RLS, only
WHERE tenant_id = ?in the application. One forgotten predicate is a breach. Use both. - Setting the tenant on connect or with session-level
SET. Pooled connections leak the context to the next tenant. Use transaction-localset_config. - Connecting as the table owner or a superuser. RLS does not apply. Use a dedicated role and
FORCE ROW LEVEL SECURITY. - RLS enabled without a policy, or a policy only on some tables. Either empty results or a gap. Check the catalog in CI.
- Single-column foreign keys between tenant tables. Allow cross-tenant references; use
(tenant_id, id). - Junction tables without
tenant_id. They cannot be protected by RLS or tenant-scoped foreign keys. - Indexes that do not lead with
tenant_id. Scattered pages, poor plans, and a painful shard migration later. - Relying on RLS for planner statistics. Stable-function predicates get average selectivity; add the explicit predicate.
- Count-then-insert plan limits. Race to exceed the limit; use a conditional counter update.
- Limits copied into every tenant's JSONB. Price changes become bulk rewrites and custom deals are invisible; model plans and overrides.
- One row per usage event in Postgres. Billions of rows a day; aggregate before storing.
- Mutable audit logs. Revoke update and delete, partition, and chain hashes.
- Choosing database-per-tenant for "isolation" at the start. Migrations, connections and onboarding costs multiply with every tenant; start shared and move tenants out when they earn it.
- Unique constraints that ignore the tenant.
UNIQUE (email)blocks the same person from joining two tenants;UNIQUE (name)on projects blocks two tenants from both having "Roadmap".
Interview Walkthrough: "Design the Database for a Multi-Tenant SaaS"
1. Scope (3 minutes). "B2B project management. Tens of thousands of tenants, most small, a few very large. Enterprise customers ask about isolation and data residency. Seat-based pricing with some metered usage."
2. Access patterns (5 minutes). Everything is per tenant. Hot reads: board views, my tasks, permission checks, API key auth. Writes: task edits with audit. Streams: usage and audit. Skew: one tenant is 8% of rows.
3. Isolation strategy (8 minutes). Compare shared schema, schema per tenant, database per tenant on isolation, operations and migration cost. Choose shared schema with RLS as the default and a placement table to move large or regulated tenants to dedicated databases. Explain why starting shared keeps that move cheap.
4. Entities and keys (8 minutes). Tenants; users (tenant-local or global accounts plus memberships, and why it matters); roles and user roles; projects and tasks; API keys; plans, subscriptions, entitlements; usage counters; audit events. Every tenant-owned table has tenant_id, composite (tenant_id, id) uniqueness, and tenant-scoped foreign keys. UUIDv7 ids.
5. RLS done right (8 minutes). Policies on every table with USING and WITH CHECK, FORCE ROW LEVEL SECURITY, a non-owner app role, transaction-local tenant context that is safe with PgBouncer, a BYPASSRLS support role, and CI tests that fail if a table is unprotected.
6. Indexes and hot queries (5 minutes). Tenant-leading composites in equality-then-sort order, a partial index for open tasks, keyset pagination, explicit tenant predicates for planner statistics.
7. Concurrency and noisy neighbours (5 minutes). Seat limits with a conditional counter update; per-tenant rate limits, statement timeouts and pools; attribution with query tags; moving outliers out.
8. Evolution (3 minutes). Caches and replicas, usage and audit to streams and columnar storage, dedicated placements, then distributing by tenant_id with co-located tables, and regional cells for residency.
Likely follow-ups:
- "A customer wants their data deleted." Mark inactive, delete in batches across tables (every table has
tenant_id, so this is enumerable), purge from backups per your retention policy, and keep the audit record of the deletion itself. - "A customer wants their data in the EU." Place the tenant in an EU cluster through
tenant_placements; the control plane stays global but holds no customer content. - "How do you run a migration across a thousand dedicated databases?" Same schema everywhere, a migration orchestrator that records per-placement versions, backward-compatible expand-then-contract changes so mixed versions are safe during rollout.
- "Why not schema per tenant?" Fine for hundreds of tenants with customization needs; at tens of thousands, catalog bloat, per-schema migrations and connection handling dominate, and moving to shared later is a rewrite.
Where to Go Next
- OLTP Database Design: the general principles behind these choices
- Connection Pooling: why transaction-local tenant context matters behind PgBouncer
- Sharding: distributing by
tenant_id, Citus co-location, hot shards - Database Migrations: zero-downtime DDL for the migration steps above
- Indexing Deep Dive: composite, partial and covering indexes
- E-Commerce Schema Design, Social Media Schema Design, Chat Application Schema Design: the same method on other domains