Migrating Schema-Per-Tenant Databases at Scale

Some tenants end up on their own schema or their own database entirely, usually because a contract demands it, not because pooling failed.

That choice buys real isolation. It also quietly removes two things pooling gave you for free: knowing which physical database a tenant's data is even in, and being able to run a migration once.

The tenant registry: one answer for where a tenant lives

Pooled multi-tenancy has one schema, so "where's this tenant's data" has one answer. Schema-per-tenant doesn't. Something has to map a tenant to its physical location. That something needs to be a single source of truth, not a convention scattered across config files.

@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)

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

The same rule from pooled multi-tenancy still applies here, just one layer up. The tenant id driving this lookup comes from the authenticated request, never from anything the client sends directly. A registry that trusts a client-supplied tenant id routes an attacker straight to whichever schema they ask for.

One migration, run five hundred times

A migration against one shared schema is a single statement. Against five hundred tenant schemas, it's five hundred. The failure modes that don't exist at n = 1 show up immediately at n = 500.

Track migration state per tenant, not globally

The fix is the same shape as idempotency in any other distributed system: a durable record of what's already been done, checked before doing it again.

@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)

Re-run the trace above against this version. The retry after the connection drop skips tenants 1 through 246, because their schema_migrations row already exists, and picks back up cleanly at 247.

Throttle how many run at once

Looping over tenants one at a time is safe but slow. Firing all five hundred at once is fast but can take down the database cluster itself. Both are wrong for the same reason a per-instance rate limit is wrong. The number that's safe doesn't come from either extreme; it comes from a deliberate concurrency limit.

@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, matters here. One tenant's schema being in an unexpected state shouldn't abort the batch for the other nine, it should surface as one failed result the operator can retry individually.

Roll out in waves, not everywhere at once

Throttled concurrency controls how fast a migration runs. It says nothing about the order tenants are migrated in, and that order is its own decision.

• Internal and test tenants first. They catch a broken migration before any customer does.
• Low-tier, low-risk tenants next.
• The largest, highest-contract-value tenants last, and often with advance notice and an agreed maintenance window, not a surprise.

Deploys and five hundred migrations can't be atomic

A single-schema deploy can migrate and ship new code together, close enough to atomic that nobody thinks about the gap. Five hundred schemas migrating at different times rules that out completely. Some tenants are on the old schema while new code is already live.

The fix is a parallel change (also called expand-contract), which makes every step backward compatible on its own:

1. 1Expand: add the new column, nullable, no default required. Old code ignores it entirely.
2. 2Dual write: new code writes both the old and new column. Old code, wherever it's still running against an unmigrated tenant, keeps working unmodified.
3. 3Backfill: populate the new column for rows written before this change.
4. 4Cut over reads: new code reads only from the new column.
5. 5Contract: once every tenant has migrated and no code path reads the old column, drop it. This step can wait weeks.

Every stage is independently safe to have running at once, across tenants on different schema versions. That's the actual requirement once "migrate everyone atomically" is off the table.

Detect drift before a support ticket does

Even with all of the above, something will eventually apply a manual hotfix to one tenant's schema. Or a migration will silently fail in a way retries don't catch. A periodic job that diffs each tenant's recorded schema_migrations against the expected version catches this before it becomes a confusing bug report:

@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 schema version stops being something a debugging session discovers by accident, and becomes something this job pages someone about directly.

1. 1

   Trusting a client-supplied tenant id to resolve a connection

   The registry lookup is only as safe as the tenant id feeding it. Resolve it server-side from the authenticated request, the same rule pooled multi-tenancy depends on, one layer further down the stack.

2. 2

   Tracking migration state globally instead of per tenant schema

   A single global "latest migration" flag can't tell you that tenants 1 through 246 succeeded and 247 through 500 didn't. Track applied migrations inside each tenant's own schema.

3. 3

   Running a migration serially or with unlimited concurrency

   One at a time is safe but slow enough to matter at real scale. Unlimited concurrency can overwhelm the database cluster. Pick a deliberate concurrency limit instead of either extreme.

4. 4

   Migrating tenants in whatever order the registry returns them

   Wave order is a safety decision, not an implementation detail. Internal and low-risk tenants first, the tenants paying for isolation last, with notice.

5. 5

   Assuming a migration and a deploy happen atomically

   Once tenants migrate at different times, some requests will hit old-schema tenants and new code simultaneously. Use a parallel change so every intermediate state is independently correct.

6. 6

   Never checking whether tenants actually converged

   A migration that silently failed on one tenant, or a manual hotfix that drifted one schema, looks like a mystery bug without a drift check. Diff recorded versions against the expected one on a schedule.

Physical tenant isolation trades one set of problems for another. Pooling's risk is a forgotten WHERE clause; schema-per-tenant's is a migration that has to succeed hundreds of times instead of once, and an application that has to know where each one physically lives.

Neither is solved by writing more careful application code. Both need their own piece of infrastructure: a registry that's the one source of truth for location, and a migration runner that tracks state per tenant, throttles its own concurrency, and rolls out in waves instead of everywhere at once.

---