Every product platform we build eventually serves multiple organisations from one deployment. The isolation model chosen in week two determines what is possible in year two, so it deserves more thought than it usually gets.
Shared schema with row-level security
For most enterprise platforms, a shared schema with Postgres row-level security is the right default. One migration path, one connection pool, and isolation enforced by the database rather than by every query an application developer writes.
alter table invoices enable row level security;
create policy tenant_isolation on invoices
using (tenant_id = current_setting('app.tenant_id')::uuid);The critical detail is that the setting must be applied per transaction by a trusted layer, and the application role must not be able to bypass RLS. A single connection that forgets to set the tenant, running as a superuser or table owner, defeats the whole model.
Partition the tables that grow without limit
Meter readings, audit logs and transaction histories grow monotonically. Range partitioning by month keeps indexes small, makes retention a partition drop rather than a mass delete, and lets the planner skip most of the table for time-bounded queries.
Pool connections deliberately
Postgres connections are expensive and serverless runtimes multiply them. Use a transaction-mode pooler in front of the database, keep transactions short, and be aware that transaction-mode pooling rules out session-level features such as prepared statements held across statements or advisory locks scoped to a session.
- Index every foreign key you filter or join on — Postgres does not add these for you
- Prefer composite indexes led by tenant_id on multi-tenant tables
- Use covering indexes to keep hot read paths index-only
- Measure with EXPLAIN (ANALYZE, BUFFERS) against production-scale data, not a seed set