Migration diary: choosing a tenant isolation model

Greenfield advice is easy to write and not much use. This is the same idea applied to a system already carrying production load that isn't allowed to stop, with the compromises left in view.

Tenant isolation is one of the few decisions that's genuinely hard to change later, because it reaches into every query and every backup. Pick it deliberately, on the basis of what happens when you get it wrong, not on what's fastest to start.

THE THREE OPTIONS

A shared table with a tenant column is cheapest to build and cheapest to operate, and it puts one WHERE clause between your clients and each other's data. A schema per tenant costs more operationally and makes the isolation structural. A database per tenant is expensive and unambiguous.

There's no universally right answer. There's a right answer given how many tenants you expect, how different their data volumes are, and what the consequence of a leak actually is. Ten enterprise clients with audit requirements and ten thousand self-serve accounts want different answers.

THE FORGOTTEN WHERE CLAUSE

With shared tables, the whole model rests on every query being filtered, forever, by everyone. That's not a thing humans do reliably. So it can't be a convention. It has to be enforced somewhere that a new engineer can't route around, whether that's row-level security in the database or a query layer that won't build an unscoped statement.

Test it adversarially. Write a test that tries to read another tenant's row through every access path you have, and treat a pass as a load-bearing part of the suite. The one path nobody tested is the one that leaks, and it's usually a report or an export.

The code wasn't the hard part. Convincing dispatchers that a second reader wouldn't cause two carriers were committed against the same trailer was, because the last project that promised that did exactly that.

THE PARTS PEOPLE FORGET

Isolation doesn't stop at the table. It applies to backups. Can you restore one tenant without the others? To exports, to logs, to error reports, to caches keyed carelessly. Each of those has leaked somewhere, and the cache key is the one that catches good teams.

Also plan for the tenant that's a hundred times larger than the rest. In a shared model, their data volume becomes everyone's query planner problem, and that conversation arrives with no warning.

We ran both paths against live traffic for three weeks and compared every load tender. The mismatch rate started at four percent, all of it the old system's undocumented rounding.

WHERE IT GOES WRONG

• A missing WHERE clause in a report path that nobody tested adversarially.
• Cache keys without the tenant in them, serving one client's data to another.
• No way to restore a single tenant, so every recovery is all-or-nothing.
• One tenant a hundred times bigger than the rest, quietly ruining everyone's query plans.

Isolation has to be structural. A convention that everyone must remember isn't isolation.

WHAT THE MIGRATION COST

Eleven weeks, one reverted step, nothing customer-visible. The reverted step was the one where we changed two things at once. We keep relearning that and we keep writing it down.