Multi-Tenant Basics

8 examples to get you started with Multi-Tenant patterns - 5 basic and 3 intermediate.

Prerequisites

• PostgreSQL 18.4 with role creation privileges.
• A SaaS domain where every business object belongs to one tenant (organization, account, workspace).

Basic Examples

1. Tenant Column on Every Business Table

The simplest pattern: tenant_id on all tenant-owned rows.

CREATE TABLE tenants (
  tenant_id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name        text NOT NULL,
  plan        text NOT NULL DEFAULT 'starter'
);
 
CREATE TABLE projects (
  project_id  bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id   uuid NOT NULL REFERENCES tenants (tenant_id),
  name        text NOT NULL,
  PRIMARY KEY (tenant_id, project_id)
);

• Composite PK (tenant_id, project_id) scopes identity per tenant.
• Every query must filter tenant_id - missing filter risks cross-tenant leaks.
• Index leading with tenant_id for OLTP lookups.

Related: Shared Schema + tenant_id - indexing and query patterns

2. Scoped UNIQUE Constraints

Uniqueness applies per tenant, not globally.

CREATE TABLE projects (
  tenant_id   uuid NOT NULL,
  project_id  bigint GENERATED ALWAYS AS IDENTITY,
  slug        text NOT NULL,
  PRIMARY KEY (tenant_id, project_id),
  UNIQUE (tenant_id, slug)
);

• UNIQUE (tenant_id, slug) lets two tenants use slug api.
• Global UNIQUE (slug) breaks multi-tenant SaaS.
• Application and SQL reviews should flag global unique mistakes.

3. Session Tenant Context

Set tenant once per connection or transaction for consistent filtering.

-- Application sets after auth
SET app.tenant_id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479';
 
SELECT project_id, name FROM projects
WHERE tenant_id = current_setting('app.tenant_id')::uuid;

• Custom GUC (app.tenant_id) centralizes tenant context.
• Pair with RLS so even forgotten WHERE clauses are safe.
• Reset GUC on connection return to PgBouncer pool (DISCARD ALL or RESET ALL).

Related: Row-Level Security Tenancy - policy templates

4. Noisy Neighbor Detection Query

Find tenants consuming disproportionate resources.

SELECT
  tenant_id,
  COUNT(*) AS row_count
FROM audit_events
WHERE logged_at > now() - interval '1 hour'
GROUP BY tenant_id
ORDER BY row_count DESC
LIMIT 10;

• Spiky tenant_id counts signal noisy neighbors on shared schema.
• Combine with pg_stat_statements grouped by tenant when tagged in comments.
• Enterprise tier may need schema-per-tenant or database-per-tenant isolation.

5. Tenant in Index Leading Column

Align indexes with WHERE tenant_id = ? access paths.

CREATE INDEX projects_tenant_name_idx ON projects (tenant_id, name);
 
CREATE INDEX audit_events_tenant_time_idx
  ON audit_events (tenant_id, logged_at DESC);

• Leading tenant_id enables index scans per tenant.
• Time-series tenant data benefits from (tenant_id, logged_at DESC).
• Missing tenant_id in indexes causes seq scans within large shared tables.

Intermediate Examples

6. Row-Level Security Policy Skeleton

Database-enforced tenant isolation as a safety net.

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
 
CREATE POLICY projects_tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

• FORCE ROW LEVEL SECURITY applies policies even to table owners.
• WITH CHECK blocks inserts into wrong tenant.
• Policies complement, not replace, application filters.

Related: Row-Level Security Tenancy - tier templates

7. Schema-per-Tenant Sketch

Stronger namespace isolation for mid-market enterprise tier.

CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
CREATE TABLE tenant_acme.invoices (
  invoice_id  bigint PRIMARY KEY,
  amount      numeric(12, 2) NOT NULL
);

• Each tenant schema duplicates table DDL - migration fan-out risk.
• Connection search_path or qualified names route queries.
• Better isolation than shared tables; worse ops than shared schema + RLS.

Related: Schema-per-Tenant - trade-offs

8. Database-per-Tenant for Regulated Tier

Hard isolation for compliance-heavy customers.

CREATE DATABASE tenant_acme_db;
CREATE DATABASE tenant_globex_db;
 
\c tenant_acme_db
CREATE TABLE invoices (
  invoice_id  bigint PRIMARY KEY,
  amount      numeric(12, 2) NOT NULL
);

• Separate backup, restore, and upgrade per tenant database.
• PgBouncer pool per database or dynamic routing in connection service.
• Highest ops cost; strongest blast-radius containment.

Related: Database-per-Tenant - enterprise isolation

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+.