Multi-Tenant Best Practices

Q: What is the top multi-tenant incident cause?

Missing WHERE tenant_id in one repository method or stale GUC on pooled connection.

Test cross-tenant leaks in CI with negative cases. Multi-tenant correctness is a security property - treat it like auth, not a naming convention.

How to Use This List

Apply to every new tenant-owned table and API endpoint.
Run negative tenant tests in CI on every PR touching data access code.
Re-audit when adding BI tools, support consoles, or background jobs.
Pick isolation tier (shared, schema, database) per contract - document in tenant registry.

A - Data Model

Put tenant_id on every tenant-owned row (shared schema). Composite PKs and FKs include tenant_id.
Use UNIQUE (tenant_id, ...) not global UNIQUE. Slugs and emails collide across tenants by design.
Index with tenant_id as leading column. Match WHERE tenant_id = $1 on all hot paths.
Separate global reference data from tenant data. Document tables without tenant_id as intentionally global.
Register tenant slug, schema, database, and tier in control plane. Single source for connection routing.

B - Enforcement

Enable FORCE ROW LEVEL SECURITY on tenant tables. Policies on SELECT, INSERT, UPDATE, DELETE as needed.
Set app.tenant_id (or equivalent) every request. Never trust client-supplied tenant without auth binding.
Reset session state on pool checkout. PgBouncer server_reset_query = DISCARD ALL or equivalent.
Forbid app connections with BYPASSRLS. Migrations use separate role in controlled pipeline.
Composite FKs on parent-child links. Prevent document_id joins that cross tenants.

C - Testing and Operations

CI negative test: tenant A cannot read tenant B rows. Required per table class, not optional integration test.
Alert on per-tenant row growth anomalies. Noisy neighbor detection in metrics platform.
Runbook for per-tenant export and delete. GDPR/CCPA requests must not require full cluster restore.
Automate schema/database provisioning idempotently. Signup and tier upgrade paths tested in staging.
Audit support and admin bypass queries. Log tenant context for platform roles with elevated access.

D - Tier Strategy

Default SMB tier: shared schema + RLS. Lowest ops cost with disciplined filters.
Business tier: evaluate schema-per-tenant when contracts require. Cap tenant count with automation investment.
Enterprise tier: database- or instance-per-tenant when mandated. Price reflects ops multiplier.
Do not mix tiers without explicit routing. Wrong connection string equals data breach.
Review tier boundaries annually. Compliance and scale change acceptable patterns.

FAQs

What is the minimum CI test for multi-tenant?

Seed tenant A and B, authenticate as A, query each endpoint/table, assert zero B rows returned. Fail build on any leak.

Is RLS enough without app filters?

RLS is defense in depth. Apps should still filter - policies catch mistakes, not replace intentional query design.

How do I test PgBouncer pooling leaks?

Two sequential requests as different tenants through same pool without reset should not see cross data - automate in staging.

What about background workers?

Workers must set tenant GUC per job payload. Global workers loop tenants explicitly - never scan full table without filter.

Should tenant_id appear in logs?

Yes for audit trails; avoid PII in same log line. Correlate tenant_id with request ID for incident response.

How do I choose isolation tier?

Start shared + RLS. Move to schema/database when contracts, noisy neighbor metrics, or restore SLAs demand it.

What is the top multi-tenant incident cause?

Missing WHERE tenant_id in one repository method or stale GUC on pooled connection.

Do read replicas need RLS?

Yes if apps connect with non-superuser roles. Replica policies match primary - test leaks on replica in CI too.

How do migrations avoid cross-tenant impact?

Shared schema: one DDL affects all - test on largest tenant fixture. Per-tenant schemas/DBs: parallelize with failure alerts.

Can I skip tenant_id on audit tables?

No - audit logs are tenant-owned data and need same isolation and indexes.

Multi-Tenant Basics - pattern overview
Row-Level Security Tenancy - policy templates
Shared Schema + tenant_id - indexing details
Database-per-Tenant - enterprise tier

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Multi-Tenant Best Practices

Test cross-tenant leaks in CI with negative cases. Multi-tenant correctness is a security property - treat it like auth, not a naming convention.

How to Use This List

Apply to every new tenant-owned table and API endpoint.
Run negative tenant tests in CI on every PR touching data access code.
Re-audit when adding BI tools, support consoles, or background jobs.
Pick isolation tier (shared, schema, database) per contract - document in tenant registry.

A - Data Model

Put tenant_id on every tenant-owned row (shared schema). Composite PKs and FKs include tenant_id.
Use UNIQUE (tenant_id, ...) not global UNIQUE. Slugs and emails collide across tenants by design.
Index with tenant_id as leading column. Match WHERE tenant_id = $1 on all hot paths.
Separate global reference data from tenant data. Document tables without tenant_id as intentionally global.
Register tenant slug, schema, database, and tier in control plane. Single source for connection routing.

B - Enforcement

Enable FORCE ROW LEVEL SECURITY on tenant tables. Policies on SELECT, INSERT, UPDATE, DELETE as needed.
Set app.tenant_id (or equivalent) every request. Never trust client-supplied tenant without auth binding.
Reset session state on pool checkout. PgBouncer server_reset_query = DISCARD ALL or equivalent.
Forbid app connections with BYPASSRLS. Migrations use separate role in controlled pipeline.
Composite FKs on parent-child links. Prevent document_id joins that cross tenants.

C - Testing and Operations

CI negative test: tenant A cannot read tenant B rows. Required per table class, not optional integration test.
Alert on per-tenant row growth anomalies. Noisy neighbor detection in metrics platform.
Runbook for per-tenant export and delete. GDPR/CCPA requests must not require full cluster restore.
Automate schema/database provisioning idempotently. Signup and tier upgrade paths tested in staging.
Audit support and admin bypass queries. Log tenant context for platform roles with elevated access.

D - Tier Strategy

Default SMB tier: shared schema + RLS. Lowest ops cost with disciplined filters.
Business tier: evaluate schema-per-tenant when contracts require. Cap tenant count with automation investment.
Enterprise tier: database- or instance-per-tenant when mandated. Price reflects ops multiplier.
Do not mix tiers without explicit routing. Wrong connection string equals data breach.
Review tier boundaries annually. Compliance and scale change acceptable patterns.

FAQs

What is the minimum CI test for multi-tenant?

Seed tenant A and B, authenticate as A, query each endpoint/table, assert zero B rows returned. Fail build on any leak.

Is RLS enough without app filters?

RLS is defense in depth. Apps should still filter - policies catch mistakes, not replace intentional query design.

How do I test PgBouncer pooling leaks?

Two sequential requests as different tenants through same pool without reset should not see cross data - automate in staging.

What about background workers?

Workers must set tenant GUC per job payload. Global workers loop tenants explicitly - never scan full table without filter.

Should tenant_id appear in logs?

Yes for audit trails; avoid PII in same log line. Correlate tenant_id with request ID for incident response.

How do I choose isolation tier?

Start shared + RLS. Move to schema/database when contracts, noisy neighbor metrics, or restore SLAs demand it.

What is the top multi-tenant incident cause?

Missing WHERE tenant_id in one repository method or stale GUC on pooled connection.

Do read replicas need RLS?

Yes if apps connect with non-superuser roles. Replica policies match primary - test leaks on replica in CI too.

How do migrations avoid cross-tenant impact?

Shared schema: one DDL affects all - test on largest tenant fixture. Per-tenant schemas/DBs: parallelize with failure alerts.

Can I skip tenant_id on audit tables?

No - audit logs are tenant-owned data and need same isolation and indexes.

Multi-Tenant Basics - pattern overview
Row-Level Security Tenancy - policy templates
Shared Schema + tenant_id - indexing details
Database-per-Tenant - enterprise tier

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Multi-Tenant Best Practices

Search Documentation

Multi-Tenant Best Practices