Running Migrations Across Thousands of Tenant Databases

A schema change that takes two seconds against one database takes hours against four thousand, and any one of them can fail, so this page treats fleet migration as a distributed job rather than a deploy step. It is a focused part of Database-Per-Tenant Isolation: ordering, concurrency, tracking and recovery.

Problem Framing

With one shared database, "migrated" is a boolean. With a database per tenant it is a distribution: at any moment some databases are on version 41, most on 42, a handful failed at 42 because a tenant had data violating a new constraint, and two are unreachable because their instance is being resized. Application code must work correctly against every one of those states simultaneously, for as long as the rollout takes.

That single fact drives the whole design. Every migration must be backwards compatible with the previous version — expand first, deploy code that tolerates both shapes, contract later — because there is no instant at which the fleet is uniform. A migration that drops a column in the same release that stops writing it will break every tenant not yet migrated.

The second driver is failure being normal rather than exceptional. At four thousand databases, a 0.5% failure rate is twenty failures per migration, every time. The runner therefore needs per-tenant version tracking, bounded concurrency so it cannot saturate the shared control plane, and a resume path that retries only what failed.

Uniformity is not a state the fleet passes through. Design for the distribution, not for the end state.

Step-by-Step Guide

1. Split every change into expand and contract releases

-- Release N (expand): additive only, safe against old code.
ALTER TABLE invoice ADD COLUMN currency text;
UPDATE invoice SET currency = 'EUR' WHERE currency IS NULL;      -- batched in practice
-- No NOT NULL yet: old code still inserts rows without it.

-- Release N+1 (constrain): only after the whole fleet is on N and code writes it.
ALTER TABLE invoice ALTER COLUMN currency SET NOT NULL;

-- Release N+2 (contract): only after no code reads the old shape.
ALTER TABLE invoice DROP COLUMN legacy_currency_code;

Three releases for one logical change looks heavy until the first time a single-release version breaks four hundred tenants that had not migrated yet.

2. Track the applied version per tenant, in the control plane

CREATE TABLE tenant_schema_state (
  tenant_id     uuid PRIMARY KEY,
  cluster_key   text NOT NULL,
  database_name text NOT NULL,
  version       integer NOT NULL DEFAULT 0,
  status        text NOT NULL DEFAULT 'idle',     -- idle | running | failed | unreachable
  last_error    text,
  attempts      integer NOT NULL DEFAULT 0,
  updated_at    timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON tenant_schema_state (version, status);

Keeping this centrally — rather than only in each tenant database's own migration table — is what lets you answer "how far along are we" without opening four thousand connections. Each database still records its own version as the authoritative local truth; the control plane is a cache that the runner reconciles.

3. Run with bounded concurrency and a claim, not a loop

# fleet_migrate.py
import asyncio, asyncpg

CONCURRENCY = 24            # bounded: the control plane and the network are shared resources

async def claim_batch(pool, target_version: int, size: int) -> list[dict]:
    return await pool.fetch(
        """
        UPDATE tenant_schema_state
        SET    status = 'running', attempts = attempts + 1, updated_at = now()
        WHERE  tenant_id IN (
                 SELECT tenant_id FROM tenant_schema_state
                 WHERE  version < $1 AND status IN ('idle', 'unreachable')
                 ORDER  BY version, updated_at
                 LIMIT  $2
                 FOR UPDATE SKIP LOCKED)
        RETURNING tenant_id, cluster_key, database_name, version
        """, target_version, size)

async def migrate_one(row, statements: list[str], target_version: int, pool):
    try:
        conn = await asyncpg.connect(dsn_for(row['cluster_key'], row['database_name']), timeout=10)
    except Exception as exc:                       # unreachable is retryable, not a failure
        await mark(pool, row['tenant_id'], 'unreachable', str(exc)); return
    try:
        async with conn.transaction():             # DDL is transactional in PostgreSQL
            await conn.execute("SET lock_timeout = '5s'")
            await conn.execute("SET statement_timeout = '10min'")
            for stmt in statements:
                await conn.execute(stmt)
            await conn.execute(
                "INSERT INTO schema_version (version, applied_at) VALUES ($1, now())", target_version)
        await mark(pool, row['tenant_id'], 'idle', None, version=target_version)
    except Exception as exc:
        await mark(pool, row['tenant_id'], 'failed', str(exc))
    finally:
        await conn.close()

lock_timeout is the setting that prevents one busy tenant from turning a fleet migration into a fleet outage: the migration waits five seconds for the lock, gives up, and is retried later rather than queuing behind a long transaction while every subsequent query queues behind it.

4. Distinguish failure classes, because they need different responses

# classify.py
def failure_class(err: str) -> str:
    if "violates check constraint" in err or "violates not-null" in err:
        return "data_repair"          # this tenant's data is genuinely incompatible
    if "lock timeout" in err or "deadlock detected" in err:
        return "retry"                # transient contention
    if "could not connect" in err or "timeout" in err:
        return "unreachable"          # infrastructure, retry later
    return "investigate"              # unknown: stop and look

A data_repair failure must not be retried in a loop — it will fail identically forever while consuming a worker slot and filling the log with the same message.

5. Report progress and stop on a systemic failure

# guard.py
async def should_continue(pool, target_version: int) -> bool:
    row = await pool.fetchrow(
        """
        SELECT count(*) FILTER (WHERE status = 'failed') AS failed,
               count(*) FILTER (WHERE version >= $1)     AS done,
               count(*)                                  AS total
        FROM   tenant_schema_state
        """, target_version)
    if row['failed'] > max(10, 0.01 * row['total']):
        await alert("fleet migration halted: systemic failure rate")
        return False                  # a broken migration should stop at 1%, not at 100%
    return row['done'] < row['total']

The two red boxes are the ones that need a human. Everything else the runner handles by itself, which is what makes a four-thousand-database rollout unattended.

Verification

-- 1. Version distribution across the fleet.
SELECT version, status, count(*) FROM tenant_schema_state GROUP BY 1, 2 ORDER BY 1, 2;

-- 2. Tenants stuck for more than a day.
SELECT tenant_id, version, status, attempts, left(last_error, 120) AS err
FROM   tenant_schema_state
WHERE  version < (SELECT max(version) FROM migration_catalogue)
  AND  updated_at < now() - interval '24 hours'
ORDER  BY attempts DESC;

-- 3. Control-plane drift: does each database agree with what we recorded?
--    Run as a sampled reconciliation, comparing local schema_version to tenant_schema_state.

The third check catches the failure that silently invalidates every dashboard: a migration applied successfully but the control-plane update failed afterwards, so the tenant is reported as pending forever and gets retried on every pass.

Plan the schedule around the plateau. The runner finishes in four hours; the migration finishes when someone has repaired twenty tenants' data.

Failure Modes & Gotchas

Symptom: a release breaks tenants that have not migrated yet. Cause: the code assumes the new schema in the same release that adds it. Fix: expand, deploy tolerant code, then contract — three releases, in that order.
Symptom: a migration blocks production queries for minutes. Cause: DDL waits indefinitely for an ACCESS EXCLUSIVE lock behind a long transaction, and every later query queues behind the waiter. Fix: set a short lock_timeout and retry; never let a migration wait unbounded.
Symptom: the same twenty tenants fail on every run and the log is unreadable. Cause: data-incompatibility failures are retried like transient ones. Fix: classify failures and exclude the data-repair class from automatic retry.
Symptom: the control plane says pending, the database says migrated. Cause: the DDL committed but the control-plane update failed afterwards. Fix: reconcile from the databases periodically and treat local schema_version as authoritative.

FAQ

Should each tenant database be migrated on first access instead? Lazy migration is tempting and dangerous: the first request after a deploy pays an unpredictable schema-change latency, concurrent requests race to apply the same DDL, and rarely-used tenants can sit unmigrated for months, which quietly extends the compatibility window forever. Migrate eagerly with a runner, and use a version check on connection only as a safety assertion.

How long should the compatibility window between expand and contract be? Long enough that the slowest tenant is definitely done, plus a margin for the ones that needed data repair — in practice a full release cycle, often two weeks. Do not shorten it because the dashboard says one hundred percent; the tenants that were unreachable during the rollout are exactly the ones that come back online just as you drop the column.

Does this get easier with schema-per-tenant instead of database-per-tenant? Somewhat: schemas in one cluster share a connection and a transaction can cover several, so the migration is faster and cheaper per tenant. The failure model is identical, though, and one long-held lock now affects every tenant on that cluster rather than one. The comparison across models is in Hybrid & Tiered Isolation Models.

How should a migration that needs a data backfill be handled? Separate it from the schema change entirely. The DDL runs in the fleet migration, quickly and transactionally; the backfill runs as its own batched job with its own progress tracking, because it is proportional to data volume rather than to schema complexity. Combining them means a tenant with a large table blocks the migration runner for minutes and holds a lock nobody expected, and it makes the whole operation impossible to resume cleanly.