Migrating Multi-Tenant Databases

A single-tenant migration runs once. A multi-tenant migration runs once per tenant — against 40 regional databases, 3,000 PostgreSQL schemas, or 12,000 customer databases — or once against a shared table that holds every tenant’s rows and therefore every tenant’s traffic. Either way, the familiar problems of zero-downtime migrations are multiplied: a lock that is harmless on one small tenant stalls the one enormous tenant; a migration that takes a second takes three hours across the fleet; one tenant with unusual data fails halfway through the rollout and leaves the fleet at two different versions; and nobody can answer “which tenants are on version 58?” without querying every database. This part of CI/CD & Migration Automation covers the operational patterns that make fleet-wide migrations predictable: rollout orchestration, canaries, failure handling, version tracking, and the special case of shared tables. It serves platform teams running SaaS databases and engineers who write migrations for multi-tenant products.

The key shift is to treat the fleet’s schema version as a distribution, not a number. At any moment during a rollout, tenants are spread across versions, so the application must work against every version in that spread — which is the expand-and-contract discipline from Expand and Contract Methodology, stretched over hours or days instead of minutes.

Concept & Mechanism

Database-per-tenant and schema-per-tenant designs run the same migration many times. The migration tool is usually the same one used for a single database — Flyway, Liquibase, Alembic, a framework migrator — pointed at each tenant in turn by an orchestrator. What the orchestrator adds is the fleet-level logic: which tenants to migrate first, how many at once, what to do when one fails, and how to record where each tenant is. Each tenant keeps its own history table (Flyway’s flyway_schema_history inside each schema or database), so version tracking is naturally per tenant, and a fleet view must aggregate it.

Schema-per-tenant has extra shared-resource effects. Every schema’s tables live in one catalog, so thousands of schemas mean hundreds of thousands of catalog entries; DDL across many schemas in one transaction holds many locks at once (and can exhaust max_locks_per_transaction); and a migration run schema by schema still contends for the same connection pool, CPU and WAL as production traffic.

Shared tables — every tenant’s rows in one table with a tenant_id column — run each migration once, but against the largest tables in the system, used by every tenant. The techniques are the single-database ones at their most demanding: online DDL, NOT VALID constraints, throttled backfills. The multi-tenant twist is that tenant data is uneven: a backfill or index build proceeds at the pace of the largest tenant’s data, and row-level security or tenant-scoped indexes change what “online” requires.

Database-per-tenant fleets add a connection-management dimension. Each tenant database has its own connection string, credentials and often its own server, so the orchestrator is also a fleet-wide client: it needs a secrets source for every tenant, network access to every server, and timeouts and retries that tolerate some servers being slow or briefly unavailable. Grouping tenants by server and limiting concurrency per server, rather than globally, prevents one busy host from receiving a disproportionate share of migration load while others sit idle. For managed databases with per-instance maintenance windows, the orchestrator can also schedule each tenant’s migration inside its host’s quiet hours.

Whatever the model, the application’s view of the schema must be version-aware during a rollout. Code paths that depend on new structure need a guard — a per-tenant version check, a feature flag keyed by tenant, or a query that tolerates the missing structure — until the fleet minimum reaches the new version. Designing that guard into the release is cheaper than discovering, mid-rollout, that the new code errors on every tenant not yet migrated. This is the fleet form of the N-1 compatibility rule described in running blue-green deploys with a shared database.

Finally, remember that per-tenant cost multiplies. A migration that adds one second of lock time per tenant adds nearly an hour across a 3,000-tenant fleet; a backfill that writes 100 MB of WAL per tenant writes 300 GB fleet-wide, which replicas and backup systems must absorb. Estimate fleet totals before starting, not only per-tenant numbers, and schedule large rollouts accordingly.

Prerequisites & Decision Criteria

Fleet migrations need infrastructure that single-database migrations do not. Check these before the fleet grows beyond what a loop in a shell script can handle:

Before each fleet rollout:

• The migration is backward compatible with the application version running on not-yet-migrated tenants.
• The orchestrator’s concurrency limit keeps total database load and connections within budget.
• A canary wave of representative tenants (including at least one large one) is defined.
• Each tenant’s migration is idempotent or transactional, so a retry after failure is safe.
• The rollout plan states what happens to the fleet if the canary fails.

Step-by-Step Procedure

1. Write the migration to be safe on every tenant. Add the same lock timeout, online DDL and idempotency you would use for a single large database — the largest tenant decides whether a statement is safe. Verify by running it on a copy of the largest tenant.

2. Build waves. Order tenants into waves: internal or test tenants first, then a canary of representative customers, then the fleet in growing batches, with the largest tenants either early (to surface problems) or in their own wave with extra monitoring.

3. Run each wave with a concurrency limit. The orchestrator migrates up to N tenants at once, records each result, and continues past failures.

# Shell · orchestrator host · Flyway per tenant schema, 8 at a time, results recorded
# WARNING: keep concurrency low enough that N concurrent migrations fit the database's load budget.
migrate_tenant() {
  schema="$1"
  if flyway -url="$JDBC_URL" -schemas="$schema" -locations=filesystem:db/tenant migrate > "/tmp/$schema.log" 2>&1
  then status=done; else status=failed; fi
  echo "UPDATE tenants SET status = :'st',
          version = CASE WHEN :'st' = 'done' THEN :'v' ELSE version END
        WHERE schema_name = :'s';" |
    psql -q "$REGISTRY_URL" -v s="$schema" -v st="$status" -v v="58"
}
export -f migrate_tenant
psql -At "$REGISTRY_URL" -c "SELECT schema_name FROM tenants WHERE wave = 2 AND status <> 'done'" |
  xargs -P 8 -I{} bash -c 'migrate_tenant "$1"' _ {}

4. Gate between waves. Compare error rates, latency and database load for migrated tenants against the rest; proceed only if they are within bounds. Details in canarying schema changes on a subset of tenants.

5. Handle failures per tenant. Failed tenants go to a retry queue with their logs; the fleet continues. Investigate patterns (all failures on tenants with a legacy feature, for example) before retrying, per handling partial failures in fleet-wide migrations.

6. Close the rollout. When every tenant is done, record the fleet’s minimum version; that is the version the next contract migration may assume.

Verification & Observability

The fleet version table is the primary instrument. It should answer, instantly, how many tenants are at each version and which failed:

-- PostgreSQL · registry database · read-only · fleet version distribution
SELECT version, status, count(*) AS tenants
FROM tenants
GROUP BY version, status
ORDER BY version DESC, status;

Cross-check it periodically against the tenants’ own history tables, because the registry records what the orchestrator believes, not what the databases contain — the drift check in tracking schema versions across thousands of tenants. During a rollout, graph migration throughput (tenants per minute), failures per wave and database load; after it, alert on any tenant stuck behind the fleet’s minimum version for more than a day.

Rollback Path

Fleet rollbacks are rarely “run the down migration everywhere”. Because migrations are backward compatible, the usual response to a bad migration is to stop the rollout — leaving migrated tenants on the new version, which old code tolerates — fix the migration, and continue. If migrated tenants are actually broken, roll back only those tenants, using the same orchestrator with a down migration or a corrective forward migration, and keep the version table accurate throughout.

# Shell · orchestrator · pause the rollout by marking remaining tenants on hold
psql "$REGISTRY_URL" -c "UPDATE tenants SET status = 'hold' WHERE status = 'pending'"

Rollback is safe per tenant when the migration was additive and nothing has written to the new structure yet. Otherwise, fix forward. Shared-table migrations follow single-database rollback rules, described in Rollback Automation.

Common Errors & Fixes

ERROR: out of shared memory / HINT: You might need to increase max_locks_per_transaction. Root cause: one transaction touching tables in many tenant schemas. Fix: migrate schema by schema in separate transactions, or raise the setting for the migration window.

Rollout stalls on one tenant for hours. Root cause: the largest tenant’s data makes a statement slow or blocking. Fix: run large tenants in their own wave with online techniques; test on a copy of the largest tenant first.

Application errors on tenants that were not migrated yet. Root cause: code deployed for the new version assumes the new schema everywhere. Fix: keep code compatible with the fleet’s minimum version, or gate new code paths by tenant version.

Registry says “done” but the tenant is not migrated. Root cause: the orchestrator recorded success incorrectly or a tenant was restored from backup. Fix: reconcile the registry against each tenant’s history table.

Child Page Index

Five guides cover the fleet mechanics. Rolling out migrations across schema-per-tenant databases builds the orchestrator. Canarying schema changes on a subset of tenants defines waves and health gates. Handling partial failures in fleet-wide migrations covers retries, quarantine and version skew. Tracking schema versions across thousands of tenants builds the fleet version view and its drift checks. And adding tenant_id to a shared table without downtime handles the shared-table model’s most common migration.

The techniques applied on each tenant are the ones throughout Zero-Downtime Schema Evolution Patterns, and pipeline gating for fleets extends Migration Pipeline Gating.

Frequently Asked Questions

Should all tenants be migrated in the same deploy? Not necessarily. Migrate in waves, with the application compatible with every version in the fleet during the rollout. Large fleets often take hours or days to converge, and that is fine if compatibility is designed in.

How many tenants should be migrated concurrently? As many as the shared infrastructure can absorb without affecting production — database CPU, I/O, WAL volume and connections. Start low, measure, and increase; per-tenant migrations are usually short, so modest concurrency still finishes quickly.

What if one tenant always fails? Quarantine it, inspect its data or schema for the difference, and fix it individually. One tenant must never block the fleet, but it must also not be forgotten; track it in the version table until it converges.

How do new tenants fit into a rollout in progress? Provision them from the latest baseline — the schema at the newest version — and register them at that version immediately. Creating new tenants from an older template during a rollout adds stragglers the orchestrator then has to migrate.

Can the same migration files serve every tenancy model? Usually yes for database-per-tenant and schema-per-tenant, where each tenant has the same tables; the orchestrator only changes the target. Shared-table designs need their own migrations, written as large-table operations with the online techniques used for any big table.

Is schema-per-tenant a good design for migrations? It isolates tenants well but makes every migration a fleet operation and puts pressure on the shared catalog. Many teams accept that for strong isolation; others prefer shared tables with row-level security and handle migrations as large-table operations.