Schema Migrations Across Many Tenants

Expand, migrate, contract, in waves

In a pool, one migration updates every tenant at once, so it must be backward compatible with the code still running. In silo or schema-per-tenant setups, the same migration runs hundreds of times, and some runs will fail on odd legacy data. Two habits handle both. First, expand and contract: add new columns or tables in a way old code ignores (expand), deploy code that writes both shapes, backfill, switch reads, and only later drop the old column (contract). Never rename or drop in the same release that starts using the new shape. Second, roll out in waves: run the migration on internal and canary tenants, then a small batch, then everyone, checking errors and latency between waves and stopping on failure. Your catalogue should record each tenant's schema version, so you always know which tenants are on which version and can resume a halted rollout.

Waves that stop on failure

A simulated rollout over ten tenants. The canary passes, the early wave hits a broken tenant, and the rollout halts before touching the rest.

tenants = [f"t{i:02d}" for i in range(1, 11)]
WAVES = [("canary", 1), ("early", 3), ("rest", None)]

def migrate(tenant):
    # Pretend t03 has odd legacy data that breaks the migration
    if tenant == "t03":
        raise RuntimeError("check constraint failed")

pending = list(tenants)
for wave, size in WAVES:
    batch = pending if size is None else pending[:size]
    failed = []
    for t in batch:
        try:
            migrate(t)
        except RuntimeError as e:
            failed.append((t, str(e)))
    pending = [t for t in pending if t not in batch]
    print(f"{wave:6} migrated={len(batch) - len(failed)} failed={failed}")
    if failed:
        print("halting rollout; investigate before continuing")
        break

Output:

canary migrated=1 failed=[]
early  migrated=2 failed=[('t03', 'check constraint failed')]
halting rollout; investigate before continuing

Long locks hurt everyone in a pool

Some DDL operations take strong locks or rewrite a whole table. On a shared table, that blocks every tenant at once. Prefer operations your database can do online, create indexes concurrently where supported, and backfill in small batches.

त्वरित जाँच: In expand-and-contract, when is the old column finally dropped?

• In the same migration that adds the new column
• Before deploying code that uses the new column
• After all running code has stopped reading and writing the old column
• Never; old columns must be kept forever