38. Database migrations in a continuously deployed system

During a rolling update or canary, old and new code run against the same database at the same time. And a rollback means old code runs against the new schema. So every migration must be compatible with both versions: the expand/contract (parallel change) pattern.

Renaming orders.status to orders.state, done safely across releases:

Release	Schema change	Code
1 (expand)	add `state` (nullable)	write both columns; read `status`
2	backfill `state` in batches (a Job)	read `state`, fall back to `status`
3	add NOT NULL / constraints	read and write `state` only
4 (contract)	drop `status`, only after release 3 is proven and can't be rolled back past	—

Running migrations in GitOps:

apiVersion: batch/v1
kind: Job
metadata:
  generateName: orbit-migrate-
  annotations:
    argocd.argoproj.io/hook: PreSync                     # before the new app version rolls out
    argocd.argoproj.io/hook-delete-policy: BeforeHookCreation
spec:
  backoffLimit: 2
  activeDeadlineSeconds: 900
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: migrate
          image: ghcr.io/acme/orbit-migrations@sha256:...   # versioned alongside the app images
          args: ["-dir", "/migrations", "postgres", "$(DATABASE_URL)", "up"]
          env:
            - { name: DATABASE_URL, valueFrom: { secretKeyRef: { name: orbit-db-app, key: uri } } }

Migration rules for production Postgres:

CREATE INDEX CONCURRENTLY (no table lock; it can't run inside a transaction, so mark it -- +goose NO TRANSACTION).
Add columns as nullable or with a constant default (instant in PG 11+). Add NOT NULL via a CHECK ... NOT VALID constraint, then VALIDATE.
Set lock_timeout (e.g. SET lock_timeout = '5s') so a migration waiting on a lock fails fast instead of queueing every query behind it.
Large backfills run as batched Jobs, never inside the migration.
Never run migrations from app startup with N replicas. They race; goose and Flyway use locks, but a slow migration then blocks every pod's startup and readiness.

War story: ALTER TABLE orders ADD COLUMN ... DEFAULT now() on a 400M-row table (a volatile default forces a table rewrite even on modern Postgres) took an ACCESS EXCLUSIVE lock for 40 minutes. Every order request queued behind it, connection pools filled, and readiness probes failed fleet-wide. lock_timeout plus a review checklist (or a linter like squawk) in CI prevents this class of outage.