Rolling Out Migrations Across Schema-per-Tenant Databases

The product gives every customer their own PostgreSQL schema — tenant_0001 to tenant_4213 — inside a handful of databases. The first multi-tenant migration was a shell loop: for each schema, set search_path, run the SQL. It worked for 200 tenants. At 4,000 it took five hours, failed on tenant 2,917 because of a lock timeout, and restarted from tenant 1 because the loop kept no state. A later attempt wrapped all schemas in one transaction to make it atomic, and died with out of shared memory after acquiring locks on 30,000 tables. Schema-per-tenant migrations need an orchestrator: one short transaction per schema, a bounded number of schemas in flight, a lock timeout on each, a record of which schemas are done, and the ability to resume. This guide builds that. It belongs to Migrating Multi-Tenant Databases.

Symptom / Error Signatures

Naive tenant loops fail with:

ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
ERROR:  canceling statement due to lock timeout             -- on one busy tenant, stopping the loop
ERROR:  relation "invoices" does not exist                 -- search_path pointed at the wrong schema

And operationally: runs that restart from the beginning after any failure, no way to tell which tenants are done, and database load spikes when someone parallelises the loop without a limit.

Root Cause Analysis

Each tenant schema has its own copy of every table, so a migration that alters one table alters it once per tenant. In PostgreSQL, each ALTER TABLE takes a lock that is held until its transaction ends; the lock table is sized by max_locks_per_transaction × max_connections, so thousands of table locks in one transaction exhaust it. Beyond that limit, holding locks on every tenant’s tables until a fleet-wide commit means that one slow tenant blocks all of them.

The robust unit of work is therefore one schema, one transaction. Atomicity across tenants is neither possible at scale nor needed: the application must tolerate tenants at different versions anyway (see the parent topic), so a partially completed rollout is a normal state, not a failure. What is needed is durability of progress — which tenants are done — and a history table per schema so each tenant knows its own version.

Immediate Mitigation

1. Stop the all-schemas transaction. If a run is holding locks across many schemas, cancel it; it rolls back entirely, which releases the locks.

2. Create a status table that the orchestrator uses as its work queue.

-- PostgreSQL · registry schema · one row per tenant schema
CREATE TABLE IF NOT EXISTS tenant_migrations (
  schema_name text PRIMARY KEY,
  target      int  NOT NULL,
  status      text NOT NULL DEFAULT 'pending',    -- pending | running | done | failed | hold
  attempts    int  NOT NULL DEFAULT 0,
  last_error  text,
  updated_at  timestamptz NOT NULL DEFAULT now()
);
INSERT INTO tenant_migrations (schema_name, target)
SELECT nspname, 58 FROM pg_namespace WHERE nspname LIKE 'tenant\_%'
ON CONFLICT (schema_name) DO UPDATE SET target = EXCLUDED.target, status = 'pending'
WHERE tenant_migrations.status <> 'done' OR tenant_migrations.target < EXCLUDED.target;
-- ROLLBACK PATH: DROP TABLE tenant_migrations;  (tenant history tables remain authoritative)

3. Let workers claim tenants safely. FOR UPDATE SKIP LOCKED lets several workers pull from the same queue without collisions.

-- PostgreSQL · worker loop step · claims one tenant atomically
UPDATE tenant_migrations SET status = 'running', attempts = attempts + 1, updated_at = now()
WHERE schema_name = (
  SELECT schema_name FROM tenant_migrations
  WHERE status IN ('pending', 'failed') AND attempts < 3
  ORDER BY schema_name LIMIT 1 FOR UPDATE SKIP LOCKED)
RETURNING schema_name;

Permanent Fix / Long-Term Pattern

Run a small pool of workers (four to eight is typical), each looping: claim a tenant, run the migration tool for that schema with a lock timeout, record the result. Flyway’s -schemas option, Alembic with a per-schema version_table_schema, or plain SQL with SET search_path all work; the important part is that each tenant’s migration is its own transaction and its own history.

# Python · orchestrator worker · psycopg; run N copies for N-way concurrency
# WARNING: SET LOCAL keeps search_path and lock_timeout scoped to this tenant's transaction.
import psycopg, pathlib

MIGRATION = pathlib.Path("db/tenant/V58__add_invoice_region.sql").read_text()

def migrate_one(conn, schema):
    with conn.transaction():
        conn.execute("SELECT set_config('search_path', %s, true)", (schema,))
        conn.execute("SET LOCAL lock_timeout = '3s'")
        applied = conn.execute("SELECT 1 FROM schema_version WHERE version = 58").fetchone()
        if not applied:
            conn.execute(MIGRATION)
            conn.execute("INSERT INTO schema_version (version) VALUES (58)")

def worker(dsn):
    with psycopg.connect(dsn, autocommit=True) as conn:
        while (row := conn.execute(CLAIM_SQL).fetchone()):
            schema = row[0]
            try:
                migrate_one(conn, schema)
                conn.execute("UPDATE tenant_migrations SET status='done', updated_at=now() WHERE schema_name=%s", (schema,))
            except psycopg.Error as e:
                conn.execute("UPDATE tenant_migrations SET status='failed', last_error=%s, updated_at=now() "
                             "WHERE schema_name=%s", (str(e)[:500], schema))

Wrap the pool in waves and health gates, as in canarying schema changes on a subset of tenants, and keep failures from blocking the fleet, as in handling partial failures in fleet-wide migrations. Keep migrations per tenant small and online: they run thousands of times, so a statement that holds a lock for a second costs the fleet an hour of cumulative blocking. For very large schema counts, watch catalog growth and autovacuum on system catalogs, and keep max_locks_per_transaction sized for the largest single-tenant migration.

Stale running rows are the orchestrator’s own failure mode: a worker that dies mid-tenant leaves the row marked running forever. Add a sweeper that resets rows whose updated_at is older than a generous timeout back to pending; because each tenant’s migration is transactional or idempotent, rerunning it is safe.

Verification Checklist

• Each tenant’s migration runs in its own transaction, with search_path and lock_timeout scoped by SET LOCAL.
• Workers claim tenants with FOR UPDATE SKIP LOCKED, so no tenant is migrated twice concurrently.
• The status table shows every tenant’s state, and a restarted run resumes from where it stopped.
• Concurrency is fixed and within the database’s load and connection budget.
• Each tenant schema’s own history table agrees with the status table after the rollout.

Frequently Asked Questions

Why not run the migration for all schemas in one transaction? It needs a lock on every tenant table until commit, which exhausts the lock table at scale and makes one slow tenant block all others. Per-schema transactions keep locks small and failures isolated.

How do I make sure DDL runs in the right schema? Set search_path with SET LOCAL (or set_config(..., true)) inside the tenant’s transaction, or schema-qualify every object name. Never rely on a session-level search_path that may leak between tenants on a pooled connection.

What concurrency is safe? Whatever keeps database CPU, I/O, WAL and connections within budget while production traffic runs — often four to eight workers. Measure with the canary wave and adjust.

Can I use Flyway or Alembic instead of custom SQL? Yes. Flyway’s -schemas option and Alembic’s per-schema version tables both support schema-per-tenant layouts; the orchestrator then calls the tool once per tenant and records the outcome.