DEV Community

Dogukan Kardas
Dogukan Kardas

Posted on Originally published at dogukankardas.com

Designing multi-tenant SaaS on PostgreSQL

#architecture #webdev #saas #postgres

When I started Aurora Hub, the first irreversible decision was tenant isolation. Dealers, service teams, and admins all share one platform, but a dealer must never see another dealer’s customers. This article walks through the actual strategy running in production.

Three isolation models, one winner

  • Database-per-tenant: strongest isolation, but operationally heavy — migrations multiply by tenant count.
  • Schema-per-tenant: middle ground, but connection pooling and cross-tenant reporting get awkward fast.
  • Shared schema + tenant_id column with row-level security: one migration path, native Postgres enforcement.

I chose the shared schema with row-level security (RLS). The deciding factor was not performance — it was that RLS moves the isolation guarantee from application code into the database itself. A forgotten WHERE clause in a late-night query cannot leak data.

Row-level security in practice

Tenant policy on the customers table

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON customers
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Every pooled connection sets the tenant before queries run
SET app.tenant_id = '9f3c2e1a-...';
Enter fullscreen mode Exit fullscreen mode

The API layer sets app.tenant_id once per request from the verified JWT, inside a transaction. Every table with tenant data carries the same policy. Even raw ad-hoc queries through the same role are automatically scoped.

Index design for tenant-scoped queries

With a shared schema, almost every query filters by tenant_id first. Composite indexes leading with tenant_id keep queries on a single tenant’s slice of the B-tree.

Composite indexes lead with tenant_id

CREATE INDEX idx_customers_tenant_created
  ON customers (tenant_id, created_at DESC);

CREATE INDEX idx_tasks_tenant_status
  ON tasks (tenant_id, status)
  WHERE status != 'archived';
Enter fullscreen mode Exit fullscreen mode

What I would repeat and what I would change

  • Repeat: RLS from day one. Retrofitting policies onto live tables is far riskier.
  • Repeat: a tenant_id on every row, even for "obviously global" tables that later became tenant-specific.
  • Change: I would add pgTAP tests for the policies earlier — I wrote them only after the first near-miss in staging.

Originally published on dogukankardas.com. The full case study is here: Aurora Hub. I build software for small businesses and SaaS teams — see more at dogukankardas.com.

Top comments (0)