When many small databases beat one big one

The first multi-tenant application most people build puts a tenant_id column on every table. It is the obvious design, it takes an afternoon, and it works. The trouble is that it makes isolation something you have to keep remembering rather than something that is true. Every query, forever, written by everyone who ever joins, including the one somebody types in a hurry on a Friday afternoon to answer a support ticket. Get it wrong once and a customer sees another customer's records. There is no apology that undoes that.

The column is a promise you have to keep

There are good tools for keeping it. Query scopes in an ORM, a base repository nobody is allowed to bypass, row level security in the database itself. All of them help and none of them changes the shape of the problem, which is that correctness depends on a rule being reapplied indefinitely.

The leak is the dramatic failure, so it gets the attention, but the boring ones cost more in practice:

• One tenant writing ten times more than everyone else slows down everyone else. There is nowhere to put them.
• Deleting a tenant is a large cascading delete across every table, run against a live database, usually on the day somebody is already annoyed.
• Restoring one tenant to how it looked on Tuesday means restoring everything to Tuesday, or writing a bespoke extraction script under pressure.
• Handing a departing customer their data is a query you have to write and then check twice.

Every one of those is easy in a world where the tenant has a database of its own.

What separation actually buys

Isolation stops being a rule and becomes routing. You open a database and the other tenants' rows are not there to be selected by mistake. A noisy tenant becomes a placement decision rather than an incident. Deleting a customer is dropping a database. Giving them their data back is a copy.

The costs are real and worth listing before anyone gets excited:

• Schema migrations become a distributed operation with partial failure. You will apply a migration to seven databases out of ten and have to think about the other three.
• You cannot answer a question about all customers at once. Counting anything means fanning out or keeping counters.
• Connection handling, backups and monitoring all multiply.

Ten, not one, and not one each

Objectify sits in the middle. Tenant data is spread across ten separate D1 databases with a Durable Object acting as the directory, mapping each tenant to a shard and putting new tenants on the least loaded one. A fixed pool rather than a database per tenant, and the reason is migrations.

Design	Migrations per release	Blast radius of a bad query	Per-tenant restore
One database, tenant column	1	Every tenant	No
Fixed pool of N databases	N	The tenants on that shard	Shard level only
One database per tenant	One per tenant	One tenant	Yes

Ten is a number you can run a migration across, watch, and retry the failures by hand at three in the morning. Ten thousand is not, unless you first build the machinery to make it so, and that machinery is a product in its own right. The middle option gives up per-tenant restore and keeps the property that matters most, which is that a query cannot reach data it was never pointed at.

The directory is the one thing that must not be wrong

Everything rests on the mapping from tenant to shard. If two requests for the same tenant ever resolve to different databases, you have split the tenant in half and you will find out days later.

That is why the directory is a Durable Object rather than a cache entry. It gives you a single place, handling one thing at a time, that owns both the mapping and the lock state that goes with it. An eventually consistent store is the wrong tool here: the window where two readers disagree is small, and the consequences of landing in it are not.

Moving a tenant while the service is running

Rebalancing is the whole reason a directory exists. A tenant that was small in March is the largest one by December, and the assignment made at sign-up was based on what "least loaded" meant at the time. The sequence is six steps:

lock(tenant)              # directory holds new writes; in-flight requests drain
copy(source -> target)    # table by table, in dependency order
switch(tenant -> target)  # directory updates the mapping
verify(target)            # row counts and per-table checksums against source
cleanup(source)           # delete the old copy, only once verify passes
release(tenant)           # lock comes off; writes resume, against the target

Two details in that order matter more than the rest.

The lock is first because it is what makes verification meaningful. With writes held, the source cannot drift while you are copying it, so a row count taken afterwards is comparing two things that have stopped moving. Without the lock you are chasing a target and you will never get a clean comparison.

Cleanup is last because it is the only irreversible step. Switch before verify looks wrong at first glance, but the switch is a single field in the directory and the source database is still sitting there intact. If verification fails you point the mapping back and delete the partial copy. Once cleanup runs you have no source to go back to, which is exactly why it waits.

The honest part is the lock itself. For the duration, that tenant's writes are held or failing. For a small tenant that is short. For a large one it is not, and there is no version of this that is invisible to the customer being moved. Anybody who tells you their live migration has no window is describing a copy, not a migration.

What it does not fix

A tenant bigger than a single database is still a problem. Objectify's schema has partition rules in it for splitting one tenant across shards, and that mode is not switched on, so in practice a tenant lives on one shard and inherits that database's ceiling. Saying so is more useful than the alternative, which is discovering it during a sales call.

Cross-tenant reporting stays awkward. Least loaded assignment is a guess about the future that is wrong reasonably often. And ten shards is a number chosen once; changing it later means moving tenants, which is fine because you built the tool for that, but it is not free.

When to not do any of this

Sharding earns its cost when a leak between tenants would be a serious event and when tenants genuinely have nothing to say to each other. B2B software holding other companies' client lists, health records, payroll: yes. A consumer product where every user is a tenant of one and the worst case is a mildly embarrassing bug: almost certainly not, and the fan-out you have signed up for will slow you down for years.

The other test is organisational. If your deployment process cannot cope with a migration that half succeeded, do not distribute your database until it can. That is a smaller piece of work than the sharding itself and it is the piece that decides whether you regret this in eighteen months.

Further reading on the store this came out of: Objectify.