Reference: Multi-Tenant SaaS

Composite reference for a B2B SaaS on PostgreSQL 18.4 (AWS RDS), ~8k tenants, shared schema with tenant_id, 1.2B rows in events. Stack: PgBouncer 1.x transaction pooling, Patroni 3.x on self-managed DR copy, pgvector 0.8+ not used in this profile.

Architecture Overview

                    ┌─────────────┐
  App pods (120) ──►│ PgBouncer   │──► Primary (OLTP)
                    │ pool 200    │      │
                    └─────────────┘      ├── async replica (read API)
                                         └── delayed replica (BI, 15 min)

Layer	Choice	Rationale
Tenancy	Shared schema + `tenant_id` uuid	Ops simplicity to 10k tenants
Isolation	RLS policies per table	Defense in depth vs app bugs
Pooling	PgBouncer transaction mode	120 pods → 200 server connections
Reads	Replica for reporting API	Primary protected from dashboards
Migrations	Flyway, weekly low-risk train	High-risk monthly window

Schema and RLS

CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY projects_tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);
 
-- App sets per request
SET app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

-- Index every tenant-scoped hot path
CREATE INDEX CONCURRENTLY projects_tenant_created_idx
  ON projects (tenant_id, created_at DESC);

Connection and Pool Policy

# pgbouncer.ini excerpt
pool_mode = transaction
default_pool_size = 50
max_client_conn = 2000
server_idle_timeout = 60

SET app.tenant_id runs at transaction start via middleware.
Prepared statements disabled in transaction mode (documented in app README).
max_connections on RDS: 500; never expose primary without pooler.

Replication and Migration Cadence

-- Weekly migration example (low risk)
CREATE INDEX CONCURRENTLY IF NOT EXISTS events_tenant_time_idx
  ON events (tenant_id, occurred_at DESC);

High-risk DDL: first Sunday of month, 03:00 UTC window.
Replica lag alert: 256 MB byte lag on OLTP replica; 2 GB on BI replica.
Tenant export: logical dump filtered by tenant_id for enterprise departures.

Lessons

RLS performance needs indexes leading with tenant_id.
Noisy neighbor tenants isolated by rate limits at app layer; largest tenant on watchlist.
Council approves cross-tenant tables (billing, admin) separately from tenant data.

Row-Level Security Tenancy - RLS patterns
Shared Schema Tenant ID - modeling
Connection Math - pool sizing
Change Management Basics - migration tiers

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

Architecture Overview

                    ┌─────────────┐
  App pods (120) ──►│ PgBouncer   │──► Primary (OLTP)
                    │ pool 200    │      │
                    └─────────────┘      ├── async replica (read API)
                                         └── delayed replica (BI, 15 min)

Layer	Choice	Rationale
Tenancy	Shared schema + `tenant_id` uuid	Ops simplicity to 10k tenants
Isolation	RLS policies per table	Defense in depth vs app bugs
Pooling	PgBouncer transaction mode	120 pods → 200 server connections
Reads	Replica for reporting API	Primary protected from dashboards
Migrations	Flyway, weekly low-risk train	High-risk monthly window

Schema and RLS

CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY projects_tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);
 
-- App sets per request
SET app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

-- Index every tenant-scoped hot path
CREATE INDEX CONCURRENTLY projects_tenant_created_idx
  ON projects (tenant_id, created_at DESC);

Connection and Pool Policy

# pgbouncer.ini excerpt
pool_mode = transaction
default_pool_size = 50
max_client_conn = 2000
server_idle_timeout = 60

SET app.tenant_id runs at transaction start via middleware.

Prepared statements disabled in transaction mode (documented in app README).

max_connections on RDS: 500; never expose primary without pooler.

Replication and Migration Cadence

-- Weekly migration example (low risk)
CREATE INDEX CONCURRENTLY IF NOT EXISTS events_tenant_time_idx
  ON events (tenant_id, occurred_at DESC);

High-risk DDL: first Sunday of month, 03:00 UTC window.

Replica lag alert: 256 MB byte lag on OLTP replica; 2 GB on BI replica.

Tenant export: logical dump filtered by tenant_id for enterprise departures.

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

Reference: Multi-Tenant SaaS

Search Documentation

Reference: Multi-Tenant SaaS