Migration failure taxonomy and production discipline

Six months after introducing a migration pipeline, a team has had no lock-queue incidents. Then a backfill update generates 40 GB of WAL in ten minutes — replication lag climbs to 120 seconds, standby reads go stale, and two read endpoints return rows that contradict each other. The migration ran fine; the database failed around it.

The nine migration failure modes

The backfill WAL flood in depth

A naive backfill runs one large UPDATE:

-- NEVER do this on a large table:
UPDATE users SET handle = username WHERE handle IS NULL;

On 100M rows, this generates a WAL (Write-Ahead Log) entry for every updated row — potentially 20–50 GB of WAL in minutes. Replicas must consume this WAL before they can serve reads; replication lag spikes to minutes or longer. During that window, standby read endpoints return stale data. If the lag exceeds max_standby_streaming_delay, Postgres cancels conflicting queries on the standby.

Durable fix: batch in 1k–10k row chunks with breathing room:

DO $$
DECLARE
  batch INT;
BEGIN
  LOOP
    UPDATE users SET handle = username
    WHERE handle IS NULL
      AND ctid IN (
        SELECT ctid FROM users WHERE handle IS NULL LIMIT 5000
      );
    GET DIAGNOSTICS batch = ROW_COUNT;
    EXIT WHEN batch = 0;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

Monitor SELECT * FROM pg_stat_replication — watch sent_lsn - replay_lsn stay near zero during the backfill.

Schema drift on replicas

A migration applied on the primary propagates to replicas via streaming replication. Replication lag (normal range: <1 s; under load: 5–30 s) means replicas may see the old schema for seconds after the migration commits. If code is deployed before replication catches up:

• Read replicas serve queries against old schema.
• New code expecting the new column gets NULLs or errors from standby reads.

Durable fix: gate the code rollout on replication lag approaching zero. Monitor via pg_stat_replication.replay_lag on the primary. Most migration tools’ pre-deploy checks include a replica-lag query.

Squawk CI and strategic migration posture

Squawk (Linux Foundation) parses migration SQL and warns or errors on unsafe patterns:

• ADD COLUMN with volatile DEFAULT → error
• ALTER COLUMN TYPE without cast coercibility check → error
• CREATE INDEX without CONCURRENTLY → error
• RENAME COLUMN / TABLE → warn
• DROP COLUMN without prior code-deploy confirmation → warn

Run Squawk on every PR touching migrations/**. Cost: under 30 s per migration PR. Benefit: catches the most common failure modes before merge.

The strategic posture: treat migration code with the same discipline as application code — PR review, CI lint, staging deploy on production-size data, runbook entry, observability on runtime and lock acquisition. Senior teams ship breaking changes routinely; the difference is that every change is planned, linted, observable, and forward-rollback-capable.