PostgreSQL — Multi-Tenant Architecture

OWNER: boris, yoanna ALSO_USED_BY: urszula, maxim, piotr, ashley, aimee, anna LAST_VERIFIED: 2026-03-26 GE_STACK_VERSION: PostgreSQL 16.x

---

Overview

Every GE client project is multi-tenant by default. GE serves SME business owners — their customers are the tenants. This page covers the three isolation strategies, when to use each, and how GE implements them.

---

The Three Strategies

1. Shared Schema with tenant_id (GE default)

All tenants share one database, one schema, same tables. Each row has a tenant_id column. Row-level security (RLS) enforces isolation.

-- Every tenant-scoped table has tenant_id
CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  customer_name text NOT NULL,
  total_cents integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Composite index with tenant_id first
CREATE INDEX orders_tenant_created_idx ON orders (tenant_id, created_at);

Advantages: - Simple schema management — one migration for all tenants. - Connection pooling works normally. - Drizzle ORM works without modification. - Easiest to implement and maintain.

Disadvantages: - Noisy neighbor risk (large tenant impacts others). - Must remember tenant_id in EVERY query. - Backup/restore is all-or-nothing.

2. Row-Level Security (RLS)

RLS builds on shared-schema by enforcing tenant isolation at the database level. Even if application code forgets a WHERE tenant_id =, PostgreSQL prevents cross-tenant access.

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: rows visible only to their tenant
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Force RLS for the application role (not superuser)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

Application sets the tenant context per request:

// Middleware sets tenant context
await db.execute(sql`SET LOCAL app.current_tenant = ${tenantId}`);
// All subsequent queries in this transaction are tenant-scoped

Advantages: - Defense in depth — database enforces isolation even if app has bugs. - ISO 27001 / SOC 2 auditors love it. - No code changes needed in query layer.

Disadvantages: - Adds ~5-10% query overhead. - Requires transaction-scoped SET LOCAL (no autocommit). - Debugging is harder (queries silently return empty sets for wrong tenant).

3. Schema-Per-Tenant

Each tenant gets their own PostgreSQL schema within the same database.

-- Create tenant schema
CREATE SCHEMA tenant_acme;

-- Tables in tenant schema
CREATE TABLE tenant_acme.orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  -- No tenant_id needed — schema IS the isolation
  customer_name text NOT NULL
);

Advantages: - Strongest isolation without separate databases. - Per-tenant backup/restore possible. - No noisy neighbor — can set per-schema resource limits.

Disadvantages: - Migrations must run per-schema (N migrations for N tenants). - Connection pooling more complex (schema switching). - Drizzle ORM requires dynamic schema configuration. - Does not scale past ~100 tenants.

---

GE Decision Matrix

CHECK: Agent is designing a multi-tenant system. IF: Tenant count is < 50 AND tenants need strong isolation (regulated industry). THEN: Use schema-per-tenant. IF: Tenant count is 50-10,000 AND ISO 27001 compliance needed. THEN: Use shared schema + RLS. This is the GE default. IF: Tenant count is > 10,000 OR extreme simplicity needed. THEN: Use shared schema with tenant_id (no RLS). IF: Unsure. THEN: Use shared schema + RLS. It is always the safe choice.

                    < 50 tenants           50 - 10K tenants         > 10K tenants
Regulated?     Schema-per-tenant       Shared + RLS (DEFAULT)    Shared + RLS
Non-regulated? Shared + RLS            Shared + RLS (DEFAULT)    Shared + tenant_id

---

Implementation: Shared Schema + RLS (GE Default)

Step 1: Tenants Table

// drizzle/schema/tenants.ts
export const tenants = pgTable('tenants', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  slug: text('slug').notNull().unique(),
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

Step 2: Tenant-Scoped Tables

export const orders = pgTable('orders', {
  id: uuid('id').primaryKey().defaultRandom(),
  tenantId: uuid('tenant_id').references(() => tenants.id).notNull(),
  customerName: text('customer_name').notNull(),
  totalCents: integer('total_cents').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  tenantIdx: index('orders_tenant_id_idx').on(table.tenantId),
  tenantCreatedIdx: index('orders_tenant_created_idx')
    .on(table.tenantId, table.createdAt),
}));

CHECK: Agent is creating a tenant-scoped table. IF: tenant_id is not the first column in at least one composite index. THEN: Add it. Without tenant_id leading an index, queries filter ALL tenants before filtering by tenant — disastrous for performance.

Step 3: RLS Policies (Migration)

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Application role can only see own tenant's rows
CREATE POLICY tenant_isolation_select ON orders
  FOR SELECT
  TO app_role
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY tenant_isolation_insert ON orders
  FOR INSERT
  TO app_role
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY tenant_isolation_update ON orders
  FOR UPDATE
  TO app_role
  USING (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY tenant_isolation_delete ON orders
  FOR DELETE
  TO app_role
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

Step 4: Tenant Context Middleware

// middleware/tenant.ts
export async function withTenant<T>(
  tenantId: string,
  callback: () => Promise<T>
): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`SET LOCAL app.current_tenant = ${tenantId}`);
    return callback();
  });
}

// Usage in API handler
export async function GET(req: Request) {
  const tenantId = getTenantFromAuth(req);
  return withTenant(tenantId, async () => {
    const orders = await db.select().from(orders);
    // RLS automatically filters to this tenant
    return Response.json(orders);
  });
}

CHECK: Agent is querying a tenant-scoped table. IF: The query is NOT inside a withTenant() wrapper or equivalent. THEN: STOP. Without SET LOCAL, RLS returns empty results or throws.

---

Performance Implications

tenant_id Indexing (Critical)

-- WRONG: Single-column indexes without tenant_id
CREATE INDEX orders_customer_idx ON orders (customer_name);
-- This scans ALL tenants to find matching customers

-- RIGHT: tenant_id first in composite index
CREATE INDEX orders_tenant_customer_idx ON orders (tenant_id, customer_name);
-- This goes directly to the tenant's rows

ANTI_PATTERN: Creating indexes without tenant_id on multi-tenant tables. FIX: Tenant_id MUST be the first (leftmost) column in composite indexes on tenant-scoped tables. The only exception is the primary key.

Connection Overhead

RLS with SET LOCAL requires transactions for every request. This means the connection pool must handle transaction-bound connections:

// Connection pool config for RLS
const client = postgres(connectionString, {
  max: 20,              // Match expected concurrent tenants
  idle_timeout: 30,     // Release idle connections
  max_lifetime: 300,    // Recycle connections every 5 min
});

Query Plan Caching

PostgreSQL caches query plans. With RLS, the plan cache is per-session, not per-tenant. This is fine — the plan shape is the same regardless of tenant_id value.

---

Cross-Tenant Operations (Admin Only)

GE internal admin operations need to query across tenants:

-- Superuser/admin role bypasses RLS
CREATE ROLE admin_role;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_role;
-- Do NOT force RLS for admin_role
ALTER TABLE orders NO FORCE ROW LEVEL SECURITY; -- only for admin_role

CHECK: Agent is building an admin/reporting feature. IF: Feature needs cross-tenant data. THEN: Use a separate admin database role that bypasses RLS. NEVER disable RLS globally to solve cross-tenant queries.

---

EU Data Sovereignty

All tenant data MUST reside in EU data centers.

CHECK: Agent is provisioning database infrastructure. IF: The database is hosted outside the EU. THEN: STOP. GE requires UpCloud Frankfurt or equivalent EU hosting. IF: Tenant data can be exported or replicated to non-EU locations. THEN: STOP. GDPR and EU data sovereignty requirements prohibit this.

---

Anti-Patterns

ANTI_PATTERN: Forgetting FORCE ROW LEVEL SECURITY on a table. FIX: Without FORCE, the table owner bypasses RLS silently. Always use both ENABLE and FORCE in the migration.

ANTI_PATTERN: Using current_setting('app.current_tenant', true) (silent null). FIX: Use current_setting('app.current_tenant') WITHOUT the true flag. If tenant context is missing, the query MUST fail — not silently return all rows.

ANTI_PATTERN: Global tables mixed with tenant tables in the same queries. FIX: Clearly document which tables are global (tenants, billing_config) and which are tenant-scoped. Never JOIN a tenant table without the tenant context set.

---

Cross-References

READ_ALSO: wiki/docs/stack/postgresql/index.md READ_ALSO: wiki/docs/stack/postgresql/security.md READ_ALSO: wiki/docs/stack/postgresql/performance.md READ_ALSO: wiki/docs/stack/postgresql/indexing.md READ_ALSO: wiki/docs/stack/drizzle/schema-design.md READ_ALSO: wiki/docs/stack/drizzle/queries.md READ_ALSO: wiki/docs/development/standards/naming.md