Skip to content
Writing··9 min read

Designing multi-tenant SaaS on PostgreSQL

How I structured tenant isolation for Aurora Hub: shared schema with row-level security, why I rejected schema-per-tenant, and the query patterns that keep it safe.

PostgreSQLSaaSArchitecture

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 tablesql
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-...';

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_idsql
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';

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.
Written by Doğukan Kardaş — Full Stack Engineer

Next article

Location-verified task flows with Firebase
→
Let's talk—Let's talk—Let's talk—Let's talk—

Next step

Got somethingworth building?

Tell us what slows your business down — or what your team needs shipped. You get a reply within one business day.