Multi-tenant SaaS database isolation: separate DB, schema, or tenant_id?

Short answer: at 10,000 tenants, the pragmatic answer is a shared DB with tenant_id everywhere + isolation buried in Postgres Row-Level Security.

Short answer

Let me first say why I rule out the other two, because at this count the decision comes down to operations. I discussed the service-splitting version of the same “where do I put the data boundary” question in the monolith-to-microservice question.

Why

1. Database-per-tenant doesn’t scale at 10k. A separate DB per tenant means 10,000 migrations, 10,000 connection pools, 10,000 backups. It gives perfect isolation but you’ll be crushed under the operational load. This only makes sense for a small number of large customers.

2. Schema-per-tenant is a middle ground but also strains. Separate schemas in one DB bridge isolation and management; but at 10k schemas the catalog bloats and migrations still run N times. Fine up to a few hundred tenants, not ten thousand.

3. In the shared model the only thing you trade away is security. One schema, tenant_id on every table; backups and migrations are simplest here and cost is lowest. What’s left is the leak risk — and there’s a way to buy that back.

What to do

1. Build a shared DB with tenant_id everywhere. One schema, one migration, one backup. 10,000 tenants share one instance’s resources.

2. Bury isolation in the DB with RLS. Run ALTER TABLE ... ENABLE ROW LEVEL SECURITY on the table and bind rows to tenant_id with a CREATE POLICY; from then on every normal access to that table has to pass through the policy. Enabling RLS without writing a policy means default-deny — no rows are visible at all — so enabling it isn’t enough, you have to write the policy too. One caveat: the table owner and roles with BYPASSRLS skip policies; don’t make your application user the table owner, and add FORCE ROW LEVEL SECURITY if you need to. Let isolation live in the database itself, not in the assumption that “the developer hopefully scoped every query”.

3. Add an ORM scope + indexing/partitioning. Put a tenant scope at the ORM layer (defense-in-depth), and index or partition hot tables by tenant_id.

4. Leave the exit open for a whale tenant. If one customer grows large enough to crowd out the rest, design the path to promote it to its own DB from day one.

Bottom line: I’d build a shared DB + tenant_id everywhere + Postgres RLS. Backups and migrations are simplest in the shared model; security is the trade-off you pay, but you buy it back with RLS. Make the ORM scope your second line of defense, partition hot tables by tenant_id, and leave the door open to move a growing tenant to its own DB. I explain why the data architecture is built this way on sade.dev.