Where does the wall between tenants sit?
A database each, a schema each, or a tenant_id on every row of shared tables. A sharded pool adds a catalog saying where each tenant lives.
Is the repository filter enough?
Bad: Filter in code only
Raw SQL, an ORM escape hatch or an admin endpoint skips it.
Good: Plus row-level security
The database filters every row too. One bug isn’t one breach.
The query forgot its tenant. PostgreSQL didn’t.
A row-level security policy checks every row before the query’s own conditions. Four overdue invoices matched; only acme’s two came back.
Request A sets tenant acme on a pooled connection. Request B, for zenmart, borrows it and forgets to set one.
What B sees
Acme’s rows. Use SET LOCAL: the tenant dies with the transaction.
200,000
connections for 1,000 tenant databases
Pools of 10 per tenant database on 20 app instances. Over 10 servers, still 20,000 each; the default limit is typically 100. A pooler sized to busy tenants: ≈450.
Bad import at 10:43. Put back one tenant as of 10:42.
Its own database: restore 2 GB, repoint the catalog, minutes. A shared database: restore all 2 TB to a side copy, extract, merge. Hours.
Grow into a hybrid
- Shared tables,
tenant_idand RLS from day one - A catalog, even with one database
- Place whales alone and EU tenants in the EU
- One schema everywhere, so moves are data copies
Remember
Shared tables with tenant_id leading every key, row-level security set per transaction, and a catalog that gives whales their own database.