Database Migration Procedure

The rule: the schema moves before the image that expects it, every time, by the same mechanism, and a roll that cannot move the schema says so loudly instead of rolling anyway.

How the schema moves

There is one migration program (Memex.Database.Migration, built as memex-migration:<tag> beside memex-portal-ai:<tag> from the same commit — the two share DbVersion.Latest). It reads admin.mesh_nodes.db_version, applies every V##_* above it, writes the new version, then runs the always-on reconcile (partition access, doc backfill, embeddings, Orleans clustering, searchable schemas) and exits. It is idempotent: at latest it applies nothing and reconciles.

Two things mint the Job that runs it:

The portal's DbVersionGate is the backstop, not the mechanism: it refuses to start a pod whose build expects a db_version the database has not reached. That refusal is correct — and it is invisible from the front door: the crash-looping pod is out of the Service endpoints, the old ReplicaSet keeps serving, and https://…/ answers 200 the whole time.

🚨 The wedge this procedure exists for (2026-09-03)

Both AKS portals had been rolling images by kubectl set image (the self-updater) for days without a helm upgrade — memex since revision 28 (Aug 30), memex-cloud since revision 21 (Aug 30). Plugins PR #1216 added V55 (the pg_notify payload carries node_type; DbVersion.Latest = 55). The next self-update rolled memex-portal-ai to a build expecting 55; nothing minted a migration Job; the DB stayed at 54:

crit: Memex.Portal.Distributed.DbVersionGate[0]
      DB migration incomplete: admin.mesh_nodes.db_version=54 < expected 55. … Refusing to start.

memex: 45 restarts over ~7 h behind a 200. memex-cloud: the same, while its eight old pods kept running pre-#1216 code — the very fan-out storm #1216 fixes (1,917 slow 201-schema UNIONs per 30 min per pod) — so the site was up and unusable at the same time.

kubernetes.io/change-cause still read "roll to 3.0.0-rc8.ci.5083 … 2026-08-23" at deployment revision 641: set image never updates it. Read meshweaver.io/self-update-rolled-at and the container image instead.

The routine

1. Adding a migration (developer). Drop V##_*.cs under Memex.Database.Migration/Migrations and bump DbVersion.Latest; MigrationRegistry.VerifyComplete() refuses a mismatch at startup. Say so in the PR title — a reviewer should see "schema" without opening files. Nothing else: the self-updater runs it.

2. One-time grant per install (operator). The self-updater creates Jobs under memex-portal-sa; the chart's memex-portal/rbac.yaml grants batch/jobs create,get,list,delete. An install that has not been helm upgraded since that rule landed answers the Job POST with 403 — the self-updater logs, at Warning, that it is rolling without the migration and names this paragraph. Do the helm upgrade once and it stops.

3. Every automatic roll. RunMigrationAsync(tag) creates memex-migration-su-<tag> (same ConfigMap and Secret as the helm Job, same image tag as the portal it precedes), waits for status.succeeded within SelfUpdate:MigrationJobTimeout (30 min), and only then patches the portal image. Failed or timed out ⇒ the roll is refused and recorded as SelfUpdateOutcome.MigrationFailed on Admin/UpdatePolicy; the schema demonstrably did not move, so the image must not. NotSupported (a host whose IDeploymentUpdater predates the seam) or Forbidden (step 2 not done) ⇒ the roll proceeds as it always did, at Warning, with DbVersionGate as the only net.

4. Verifying. helm list -n <ns> against kubectl get deploy memex-portal-deployment -n <ns> -o jsonpath='{.spec.template.spec.containers[0].image}': divergence means the schema and the code are on different clocks. A migration Job's success line is literal: Database migration completed. Version: N. Anything else is not a pass.

Recovery when a pod is refusing on db_version

Do not helm upgrade with the values on file — they pin the image the chart last knew (rc8 on both portals that day), and an upgrade would roll the portal back. Mint the Job by hand at the tag the deployment is already on:

apiVersion: batch/v1
kind: Job
metadata: { name: memex-migration-v55-manual, namespace: <ns> }
spec:
  backoffLimit: 6
  ttlSecondsAfterFinished: 3600
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: memex-migration
          image: meshweaver.azurecr.io/memex-migration:<the deployment's tag>
          envFrom:
            - configMapRef: { name: memex-migration-config }
            - secretRef: { name: memex-migration-secrets }

az aks command invoke … --file job.yaml --command "kubectl apply -f job.yaml", then wait for Database migration completed. Version: N. The gate is db_version < expected, so migrating forward never breaks the pods still serving on the older build. (Break-glass form: a HelmRelease deploy Hosting/InstanceAction runs the same Job through helm upgrade with no hand-applied manifest — OperatingFromThePortal.)

🚨 Why a migration deadlocks under load — and what to do until the fix ships

The migration runner's schema initialisation drives mw_auth_mirror_heal_batch — per partition schema it CREATE OR REPLACEs the access trigger functions, re-installs triggers on mesh_nodes, and re-runs rebuild_user_effective_permissions() (an ACCESS EXCLUSIVE rename-swap of the permission table). A live pod meanwhile holds ACCESS SHARE on those same tables across a multi-schema UNION, or a row lock on access inside the very trigger being replaced. Opposite orders ⇒ 40P01 deadlock detected, and Postgres kills the migration. On memex-cloud it died five times out of five at eight replicas, before it had even read db_version; at three replicas it completed in 11 minutes. Two consequences:

• A migration image can fail under load even with no new V## — the heal batch runs regardless.
• Until the heal batch yields instead of waiting (lock_timeout + retry per batch, MeshWeaver.Plugins), run a contended migration in a quieter window: pause the rollout, scale the portal down (the site stays up on fewer pods, and fewer pods also means fewer fan-out queries), run the Job, scale back, resume.

🚨 A long-running Job is not a stuck Job — measure it before you kill it

The schema steps finish in seconds to minutes; the always-on reconcile that follows (partition access, doc backfill, embeddings) is a loop over every partition schema, and on a large install it runs for hours. Elapsed time therefore says nothing about health, and "it has been running all day, it can never finish" is a conclusion that has been reached — and been wrong.

Measured on memex-cloud 2026-09-07: Job memex-migration-28 was described as a backfill that "can never finish within its own design" and slated for deletion after 9 h 50 m. Its own log said otherwise — Current DB version: 55 (the schema half had completed 90 s in), 165 of the 220 partition schemas the run had announced, 74,246 rows embedded, and a steady 200 rows per 81–86 s. It was left to run, and it finished on its own ~2 h later: Database migration completed. Version: 55, Job Complete 1/1, duration 10 h, then ttlSecondsAfterFinished removed it with no manual step. Nothing needed intervention at any point — both portals served HTTP 200 throughout.

Read these five before deciding, in this order — they are all in the Job itself:

Deleting a reconcile that is progressing costs the un-embedded tail: those rows stay NULL and their partitions answer vector search poorly until some later migration reaches them again. The per-row writes are committed as they go, so a delete loses only the row in flight — but it also loses the remaining work, which nothing re-queues on its own.

When it genuinely is stuck, the tell is a counter that does not move (or 40P01 in the log, which is the deadlock above), never the elapsed time. And the durable fix for the long tail is not a kill: it is the budgeted, batched reconcile — a bounded slice per Job that reports its remainder and resumes on the next one — so no single Job has to finish the whole backfill.

🚨 The budget is not retroactive, so "it should have stopped after ten minutes" is not a reason to kill one. The migration image carries its own budget: memex-migration-28 ran 3.0.0-ci.8009, which predates both the ten-minute Job budget (MeshWeaver#3630) and the batched backfill (MeshWeaver.Plugins#1488). A Job only becomes time-bounded once its environment rolls onto a set built after those landed — until then a migration running for hours is behaving exactly as its image was built to, and the readings above are the only way to judge it.

Related

• Deployment — AKS — the runbook this procedure is the schema half of.
• Release & Self-Update Strategy — what rolls, when.
• Postgres Schema Architecture — one schema per partition, which is why every migration step is a loop.