Multi-Tenant Architecture for AI SaaS: Lessons from the Field

A tenant isolation bug is not a bug in the sense that a null pointer is a bug. It is a disclosure event with a legal team attached. And the standard defense against it, a WHERE user_id = $1 clause on every query, has a defect that no amount of code review fully closes: it requires every developer, on every query, forever, to remember something. One forgotten predicate in one rarely-hit analytics endpoint is the whole incident.

Postgres row-level security moves that enforcement from N query sites to one policy definition. The database refuses to return rows the current tenant is not entitled to, regardless of what SQL you wrote. That is a categorically different guarantee, and it is cheap to set up.

The setup, in full

ALTER TABLE memories ENABLE ROW LEVEL SECURITY;
ALTER TABLE memories FORCE ROW LEVEL SECURITY;   -- applies to the table owner too

CREATE POLICY memories_tenant_isolation ON memories
  FOR ALL
  TO app_user
  USING      (user_id = current_setting('app.user_id', true)::uuid)
  WITH CHECK (user_id = current_setting('app.user_id', true)::uuid);

Two clauses, two jobs. USING filters what you can read, update, or delete. WITH CHECKvalidates what you can insert or leave behind after an update, which is what stops a tenant from writing a row stamped with someone else's user_id. Omit WITH CHECK and you have built a read-side firewall with an open write path. The semantics of both are documented in the Postgres row security documentation.

FORCE ROW LEVEL SECURITY is the line people skip. By default, policies do not apply to the table owner, and in a lot of deployments the app connects as the owner because that is what the migration tool used. Without FORCE, you enable RLS, run your tests, see everything working, and never notice that no policy was ever evaluated.

The other half is per-transaction context. Connection pools reuse backends, so anything you SET without LOCALleaks into the next tenant's request on the same connection. This is not a hypothetical; it is the most common way an RLS deployment fails in production.

BEGIN;
SET LOCAL app.user_id = '3f2a...';   -- reverts at COMMIT or ROLLBACK, always
SELECT id, content FROM memories WHERE palace_id = $1;
COMMIT;

Wrap it once in a helper that takes a callback and refuses to hand out a client outside a transaction. If a developer can get a raw connection without the context set, they eventually will. Note also that current_setting('app.user_id', true) with the true flag returns NULL when unset rather than erroring, and user_id = NULL evaluates to NULL, which the policy treats as false. Unset context means zero rows, not all rows. That is the correct failure direction, and it is worth writing a test that asserts it.

Then you add pgvector and the plan gets worse

Here is the concrete cost, and it is the reason RLS on a vector table is not a free win.

An HNSW or IVFFlat index scan is approximate and, critically, it is a top-k operator. You ask for k nearest neighbors, it walks the graph and returns k candidate rows. Then the RLS policy is applied to those candidates as a filter. Rows belonging to other tenants get dropped after the index has already decided which rows to return.

SELECT id, content
FROM memories
ORDER BY embedding <=> $1
LIMIT 10;

-- Index returns 10 nearest across ALL tenants.
-- RLS drops the 8 that are not yours.
-- You get 2 rows. No error. No warning.

This is the post-filter problem, and its severity scales with how small your tenant is relative to the table. If you hold 0.1% of the rows, an unbounded ANN search returns approximately 0.1% relevant candidates, and a LIMIT 10query returns approximately zero rows. Recall does not degrade gracefully. It collapses, and it collapses silently, because "fewer results than requested" is a legal answer from an approximate index.

Three mitigations, ranked by how much I like them

Put the tenant key in the index. Postgres 17 and pgvector 0.8 improved iterative index scans, but the durable fix is making the tenant a first-class part of the search. A partial index per tenant is unmanageable past a few dozen tenants. A partitioned table, with PARTITION BY HASH (user_id)and an HNSW index on each partition, gets you index scans that only ever traverse one partition's graph. Partition pruning happens before the ANN walk, so there is nothing to post-filter. This is the right answer for a store with many mid-sized tenants and it is what I would build again.

Over-fetch and re-filter. Set hnsw.ef_search higher and pull 200 candidates to get 10 usable rows. It works, it is one line, and the multiplier you need is a function of tenant selectivity, so it is a constant you will be re-tuning as the tenant distribution changes. Fine as a stopgap. Bad as an architecture, because the failure mode when the multiplier is too low is silent under-retrieval rather than an error.

Separate schemas or databases per tenant. Total isolation, no policy evaluation cost, no post-filtering. Also: connection pool exhaustion, migrations that must run N times and can partially fail, and cross-tenant analytics that require either a union of everything or a separate warehouse. Justified for enterprise customers with contractual isolation requirements. Not justified as a default, and the operational tax compounds.

Two more things that bit us

Policies are not free on the planner. An RLS predicate becomes an implicit qualifier on the relation, and if user_idis not selective in the planner's statistics you can get a sequential scan where you expected an index scan. Always EXPLAIN ANALYZE your hot queries with the policy active and the session context set, because a plan captured as superuser with RLS bypassed tells you nothing about production.

And RLS covers tables, not everything. Sequences leak row counts. Materialized views built by a privileged role bake in unfiltered data. Functions declared SECURITY DEFINER execute as their owner, which means they can read across tenants and hand the result back to a caller who could not have queried it directly. Every one of those is a real leak path with a policy sitting uselessly next to it.

Unimatrix runs RLS with per-transaction context on every memory read, plus audit logging on the paths that cross tenant boundaries by design, like admin access. The enforcement details and what the self-hosted deployment inherits are on the security page.