Multi-tenancy is a decision you make once, badly, in week two of a product, and live with for years. The three viable models in Postgres are shared tables with row-level security, schema-per-tenant, and database-per-tenant. All three work. They fail in completely different ways, and the cost of moving between them rises sharply with customer count.
Shared tables with row-level security
Every row carries a tenant_id, and Postgres row-level security policies enforce that queries only ever see one tenant. This is the default we recommend for products that expect thousands of tenants with modest per-tenant data volumes.
CREATE POLICY tenant_isolation ON invoices USING (tenant_id = current_setting('app.tenant_id')::uuid);The failure mode: one connection, one forgotten SET
RLS is only as good as the code that sets the session variable. A connection returned to the pool with a stale setting is a cross-tenant data leak, and it will not show up in tests because tests run one tenant at a time. Set the variable inside the transaction, never on the connection, and add an integration test that deliberately interleaves two tenants on one pooled connection.
Force RLS on table owners too - by default the owner bypasses policies.
Index every tenant_id column as the leading column of the composite index, not as an afterthought.
Watch for query plans that filter after a large scan; RLS predicates do not always push down as you expect.
Treat any query that runs without a tenant context as a production incident, and log it as one.
Schema-per-tenant
Each tenant gets its own schema with an identical table layout, and the search_path selects it. Isolation is structural rather than policy-based, which is easier to defend in a security review, and per-tenant restore becomes trivial.
The failure mode: migrations and catalogue bloat
At fifty tenants, a migration is fifty DDL statements in one transaction and you are fine. At five thousand, the system catalogue is large enough that connection setup slows down, autovacuum has thousands of tables to consider, and a migration becomes an orchestrated multi-hour job with its own failure handling. Schema-per-tenant is excellent up to a few hundred tenants and painful beyond that.
Database-per-tenant
Complete isolation, per-tenant tuning, per-tenant backup and residency guarantees. This is the right answer when you have dozens of large enterprise customers, contractual data residency requirements, or tenants big enough to need their own hardware profile. It is the wrong answer for self-serve products because the per-tenant fixed cost dominates.
Choose the model that matches the shape of your customer base, not the one that matches the size of your ambition.
Planning the escape hatch
Whichever model you start with, make the tenant identifier explicit in the application layer from day one and route every query through a single data-access seam. Teams that did this can move from RLS to database-per-tenant for their three largest accounts in a few weeks. Teams that scattered implicit tenancy through the codebase spend a year on it.
A practical hybrid we ship often: shared tables with RLS for the long tail, and dedicated databases for the handful of enterprise accounts whose contracts demand it, behind the same routing seam.
