PostgreSQL Schema Design for Multi-Tenant SaaS: Three Patterns Compared

-- All tenants share tables; tenant_id column separates rows
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  amount NUMERIC(10,2),
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Composite index for tenant-scoped queries
CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at DESC);

-- Combine with RLS for enforcement
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Each tenant gets their own schema
CREATE SCHEMA tenant_abc123;
CREATE TABLE tenant_abc123.orders (
  id BIGSERIAL PRIMARY KEY,
  amount NUMERIC(10,2),
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Set search_path per connection
SET search_path = tenant_abc123;

-- Partition orders by tenant for large multi-tenant tables
CREATE TABLE orders (
  id BIGSERIAL,
  tenant_id UUID NOT NULL,
  amount NUMERIC(10,2),
  created_at TIMESTAMPTZ
) PARTITION BY HASH (tenant_id);

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);