Migrating Schema-Per-Tenant Databases at Scale

Usually a contract forces it: a customer whose data may not share tables with anyone else's. (If nothing forces it, a shared schema with row-level security is the better default; see Designing Multi-Tenant APIs That Scale.) Once tenants live in separate schemas, two things a shared schema gave you for free are gone.

Where does this tenant live?

With one schema, the answer is "there". With five hundred, something has to map each tenant to a host and schema, and it has to be one source of truth, not config scattered across services.

Request

Tenant registry

Schema A

Schema B

Schema C

1. Request to Tenant registry: resolve tenant_id
2. Tenant registry to Schema A
3. Tenant registry to Schema B
4. Tenant registry to Schema C

@itsnas — SQL

Hide line numbersCopy code

CREATE TABLE tenant_registry (
  tenant_id   uuid PRIMARY KEY,
  db_host     text NOT NULL,
  schema_name text NOT NULL,
  tier        text NOT NULL, -- pooled | dedicated
  region      text NOT NULL
);

main

Nas (@itsnas)

@itsnas — TypeScript

Hide line numbersCopy code

async function resolveTenantConnection(tenantId: string) {
  const [entry] = await registryDb`
    SELECT db_host, schema_name
    FROM tenant_registry
    WHERE tenant_id = ${tenantId}
  `
  if (!entry) throw new NotFoundError('tenant')
 
  return getPool(entry.db_host).withSchema(entry.schema_name)
}

main

Nas (@itsnas)

The tenantId feeding this lookup comes from the authenticated request, never from the client, or the registry will happily route an attacker to whichever schema they name. Resolve once per request and carry the connection on a request context (AsyncLocalStorage in Node) instead of hitting the registry per query.

One migration, five hundred runs

A loop runs ALTER TABLE invoices ADD COLUMN due_date date across every schema. Tenant 7's connection drops, and the script exits.

1. Index 0: v20
2. Index 1: v21
3. Index 2: v22
4. Index 3: v23
5. Index 4: v24
6. Index 5: v25
7. Index 6: ? (crash)6
8. Index 7: v17
9. Index 8: v18
10. Index 9: v19
11. Index 10: v110
12. Index 11: v111

Re-run from the top and it fails on the schemas that already have the column. Paper over that with ADD COLUMN IF NOT EXISTS and it "succeeds" while hiding whatever went wrong. At n = 1 none of this exists; at n = 500 it happens on the first real migration.

Each tenant keeps its own history

The fix is the idempotency pattern again: a durable record of what's done, checked before doing it. The record lives inside each tenant's own schema, because a single global "latest migration" can't express "six yes, six no".

@itsnas — SQL

Hide line numbersCopy code

CREATE TABLE schema_migrations (
  version     text PRIMARY KEY,
  applied_at  timestamptz NOT NULL DEFAULT now()
);

main

Nas (@itsnas)

@itsnas — TypeScript

Hide line numbersCopy code

async function migrateTenant(tenantId: string, migration: Migration) {
  const db = await resolveTenantConnection(tenantId)
 
  const [applied] = await db`
    SELECT version FROM schema_migrations WHERE version = ${migration.version}
  `
  if (applied) return { tenantId, status: 'already-applied' }
 
  await db.begin(async tx => {
    await migration.up(tx)
    await tx`
      INSERT INTO schema_migrations (version) VALUES (${migration.version})
    `
  })
 
  return { tenantId, status: 'migrated' }
}

Show all 17 lines

main

Nas (@itsnas)

The migration and its record commit in one transaction, so they can't disagree. Re-run after the crash: tenants 1 to 6 report already-applied, and the run picks up at tenant 7.

A fixed number in flight

One at a time is safe and takes all day. All five hundred at once can take the cluster down. Pick a concurrency limit on purpose:

@itsnas — TypeScript

Hide line numbersCopy code

async function migrateAllTenants(migration: Migration, concurrency = 10) {
  const tenants = await getAllTenantIds()
  const results = []
 
  for (let i = 0; i < tenants.length; i += concurrency) {
    const batch = tenants.slice(i, i + concurrency)
    results.push(
      ...(await Promise.allSettled(
        batch.map(id => migrateTenant(id, migration)),
      )),
    )
  }
 
  return results
}

main

Nas (@itsnas)

Promise.allSettled, not Promise.all: one tenant in a weird state should be one failed result to retry, not an aborted batch.

Order is a safety decision

The tenants most likely to be on their own schema are exactly the ones least tolerant of a surprise schema change. Go last with them.

Old code and new schemas, at the same time

With one schema, migrating and deploying are close enough to atomic that nobody notices the gap. With five hundred migrating over hours, new code will hit unmigrated tenants and old code migrated ones. Every intermediate state has to work. That's a parallel change (expand, then contract):

A column of ticks is the whole point: no step depends on every tenant being on the same version.

Notice drift before a customer does

Someone will hotfix one tenant's schema by hand, or a migration will fail in a way retries miss. Diff every tenant's recorded version against the expected one on a schedule:

@itsnas — TypeScript

Hide line numbersCopy code

async function detectDrift(expectedVersion: string) {
  const tenants = await getAllTenantIds()
  const drifted = []
 
  for (const tenantId of tenants) {
    const db = await resolveTenantConnection(tenantId)
    const [latest] = await db`
      SELECT version FROM schema_migrations
      ORDER BY applied_at DESC LIMIT 1
    `
    if (latest?.version !== expectedVersion) drifted.push(tenantId)
  }
 
  return drifted
}

main

Nas (@itsnas)

A tenant on the wrong version becomes a page, not a three-day debugging mystery.

Physical isolation swaps a forgotten WHERE for an operations problem, and neither is solved by more careful application code. It needs two pieces of infrastructure: a registry that is the only answer to "where", and a migration runner that records progress per tenant, limits its own concurrency, and rolls out in waves.

---