Database migration rehearsal
CJ Ackley | SWE | Project brief

STATUS
Migration brief; query contracts to choose. Implementation and tests are planned.

THE PROBLEM
A migration that passes on an empty database can fail on existing rows or hold a lock longer than expected. This runner applies a proposed PostgreSQL migration to a disposable database and checks both old and new application queries.

DESIGN QUESTION
Will the proposed migration preserve data and support the intended rollout sequence?

SCOPE
- Load synthetic fixtures with nulls, duplicates, large tables, and old record shapes.
- Apply migrations under explicit lock and statement timeouts; record failures and schema differences.
- Run old and new query contracts, including a staged expand-and-contract rollout where needed.

CONSTRAINTS
Database: PostgreSQL; migration runner version pinned per test
Data: Synthetic fixtures with documented distributions
Rollback: Test rollback when supported; otherwise document forward recovery
Target: Disposable database only; no production execution

ENGINEERING DECISIONS (PROPOSED)
Query compatibility during rollout
Rehearse expansion, backfill, application cutover, and contraction separately. Run old and new query contracts during the overlap. Remove an old column only after the rollout fixture stops using its old contract.

ALTERNATIVE
A single migration that passes against the latest application is quicker to test, but says little about the mixed versions present during a rolling release.

TRADEOFF
Maintaining two contracts and restartable backfills adds work. The runner also needs separate handling for commands such as concurrent index creation that cannot run inside a transaction block.

PROPOSED TOOLS AND METHODS
PostgreSQL, SQL migrations, Python runner, CI

VERIFICATION SCENARIO (NOT YET RUN)
A deployment stops halfway through the backfill
Hold a competing transaction, interrupt the backfill after a committed batch, then resume the rollout. Include a failed concurrent-index build in a separate fixture.

PASS CONDITION
The lock wait ends at the configured timeout. Committed backfill batches remain consistent and resume without duplicating effects. The runner reports an invalid index instead of treating its name as proof of success.

EVIDENCE TO RETAIN
Stage-by-stage query results, lock-wait logs, checkpoint records, and schema checks before and after the recovery step.

MEASUREMENT AND LIMITS
Compare lock duration and backfill rate across documented fixture sizes. Preserve row counts, null rates, duplicate rates, and database settings with each run. Synthetic data cannot establish production timing.

COMPLETION CRITERIA (PLANNED)
- The example rollout succeeds from a fresh disposable database.
- Interrupted backfills, incompatible records, lock contention, and invalid indexes fail or recover as specified.
- The release notes state when old application versions stop being supported.

VERIFICATION PLAN
Existing records
Attempt a new constraint against incompatible rows; fail with useful evidence.

Lock contention
Hold a competing transaction and verify the configured timeout.

Compatibility
Require old and new query contracts during the overlap; retire old contracts only after application cutover.

PLANNED DELIVERABLES
- Migration runner, fixture generator, and CI integration
- Schema-diff report, failure logs, and query-contract tests
- Example rollout plan and forward-recovery exercise

SCOPE NOTE
Synthetic fixtures exercise known data shapes. Production sizing, workload, and lock behavior still need a separate rollout review.

NEXT STEP
Select the supported migration format and define old and new query contracts.
