Designing Multi-Tenant Backends That Don't Hate You at 3 AM
Schema-per-tenant, row-level security, or shared-everything? A practical walk-through of the tradeoffs I've made across three real multi-tenant systems — and the migration scars to prove it.
Every multi-tenant system I've worked on started with the same optimistic sentence: 'we'll just add a tenant_id column to every table.' Every one of them eventually had a 3 AM incident that proved the sentence was a lie.
Shared-schema with a tenant_id column is the cheapest to start and the easiest to mess up. One missing WHERE clause and tenant A is reading tenant B's invoices. Postgres row-level security helps, but only if you actually enforce it on every connection — and only if your ORM doesn't quietly strip the session variable.
Schema-per-tenant trades runtime risk for migration pain. Isolation is wonderful until you have 800 schemas and a migration that takes four hours. I've shipped this model successfully, but only with heavy investment in migration tooling and a strict 'no online schema changes' rule.
Database-per-tenant is the safest and the most expensive. For regulated customers (health, finance) it's often the only acceptable answer. For a self-serve SaaS with 50,000 free accounts, it's financial suicide.
My current default is shared-schema with row-level security, plus a per-tenant connection pool that sets the tenant context once and refuses to operate without it. It's not glamorous, but it's the model that has woken me up the fewest times.
