2026-09-06 · 8 min read
- sql
- database
- deployments
A migration runner can lose its connection or time out after the database commits work but before success is recorded. The next attempt must distinguish an untouched database from a fully or partially applied change.
Idempotency does not mean ignoring every already-exists error. It means checking a precise postcondition so repeating a step converges on the intended schema and data without hiding an incompatible object.
Know the engine's transaction boundaries
Database engines differ on transactional schema changes, implicit commits, concurrent index construction, lock duration, and statement cancellation. Write the migration plan for the exact engine and deployed version.
- Separate short metadata changes from long data movement.
- State which operations can roll back atomically.
- Set and observe lock and statement timeouts deliberately.
- Prevent multiple migration runners from owning the same step.
Check definitions, not only names
Before creating or skipping an object, compare its effective columns, types, defaults, nullability, indexes, constraints, and ownership with the desired definition. A matching name can conceal a failed earlier experiment or manual hotfix.
Record migration state in a durable table, but corroborate it with database metadata during incident recovery. Never edit migration history merely to silence a runner without proving the resulting postcondition.
Make data backfills restartable
Process bounded batches selected by a stable key and checkpoint completed ranges. Updates should target rows that still need transformation, while unique constraints and deterministic values prevent duplicate side effects.
- Keep old and new application versions compatible during the rollout window.
- Measure replication lag, lock waits, throughput, and remaining rows.
- Throttle or pause without losing the completed checkpoint.
- Validate counts and representative records before enforcing constraints.
Rehearse interruption and forward repair
Use Flashman's SQL formatter to review statements, diff to compare schema definitions, timestamp converter to align deploy history, JSON formatter for sanitized runner events, and UUID generator for public fixture rows.
Test interruption before and after each commit, retries, concurrent runners, lock contention, mixed application versions, backup restoration, and forward repair in a production-like copy with the same engine settings.