SAYLOR Change production schemas and data through compatible phases, measured backfills, verification, and a recovery path that respects irreversible writes.
The result you're building
A migration package with dependency inventory, forward/backward compatibility, backup and restore proof, bounded locks/backfill, data checks, staged cutover, and a tested recovery decision.
Use this guide when
- You add, remove, rename, retype, constrain, partition, or backfill production data.
- Old and new application versions may overlap.
- Downtime, lock, or data-loss risk must be bounded.
Do not use it as a substitute for
- Running an untested migration directly on production because it works on a small local database.
- Calling a down migration safe when new writes cannot be represented in the old schema.
Before you change anything
- Collect these items first. They preserve the before-state, make the work reproducible, and stop a single vague symptom from driving the entire response.
- Engine/version, schema dump, size, growth, indexes, locks, replication, and maintenance window.
- Application/query/job/BI dependencies and deployment overlap.
- Migration SQL/code hash, transaction behavior, estimated lock/scan/WAL cost.
- Backup, point-in-time recovery, restore time, and last restore proof.
- Canary, backfill, reconciliation, rollback/roll-forward, and failure test evidence.
Understand the system before fixing it
Provenance is part of the record
A value without source, observation time, transformation history, and known limitations cannot support a defensible automated decision.
Schema changes are product changes
Renames, units, null behavior, identifiers, and deleted fields can silently change decisions even when a pipeline still returns HTTP 200.
Expand and contract reduces cutover risk
Add compatible structures, deploy dual-read/write or backfill, verify, switch use, then remove old structures after the rollback window.
Rollback may mean roll forward
After new-format writes occur, reversing DDL can destroy data. Recovery may require fixing forward while serving from the compatible old path.
Evidence-to-decision map
| Evidence | Likely layer | First decisive check | What the result means |
|---|---|---|---|
| Migration waits on lock | Concurrency | Inspect blockers and lock mode before cancel | DDL conflicts with long transactions or requires stronger lock than expected. |
| Backfill overwhelms replicas | Workload/WAL | Measure batch time, WAL, lag, and I/O | Unthrottled update exceeds replication or storage capacity. |
| Old app crashes after schema deploy | Compatibility | Run old/new binaries against expanded schema | Migration removed or changed a field before all consumers switched. |
| Counts match but values wrong | Data transform | Reconcile invariants and sampled row hashes | Backfill logic or units/encoding are incorrect. |
| Down migration loses new data | Recovery design | Compare new writes to old representational capacity | Schema reversal is not a safe rollback. |
Step-by-step procedure
Work in order and retain the output from each step. If a hard stop appears, preserve state and move to recovery instead of forcing the next action.
Step 01 — Inventory schema and consumers
Why: A precise boundary prevents a plausible fix from solving the wrong problem.
Do: Capture engine/version, schema, data size, indexes, constraints, queries, jobs, replicas, CDC, analytics, and old/new application compatibility.
Read the result: Every known reader and writer has a migration phase.
Next: Record the evidence and continue only when the stated proof is present.
Step 02 — Prove backup and recovery
Why: Symptoms are not enough; a baseline preserves the evidence needed to isolate the failing layer.
Do: Take policy-compliant backup or PITR checkpoint, restore into isolation, run integrity/application tests, and record recovery time and point.
Read the result: Recovery evidence meets the allowed loss and downtime targets.
Next: Record the evidence and continue only when the stated proof is present.
Step 03 — Design compatible phases
Why: Inconsistent inputs create false differences and make later comparisons unreliable.
Do: Prefer expand/backfill/verify/switch/contract. Avoid destructive rename/type/not-null in one step; add new fields and compatibility code first.
Read the result: Old and new releases operate during the overlap window.
Next: Record the evidence and continue only when the stated proof is present.
Step 04 — Estimate and rehearse
Why: A decisive test reduces trial-and-error and limits unnecessary change.
Do: Run production-like volume, lock, I/O, WAL, replication, and failure tests. Review query plans and transaction behavior.
Read the result: Measured worst case fits the window and protection thresholds.
Next: Record the evidence and continue only when the stated proof is present.
Step 05 — Deploy a bounded canary
Why: The smallest reversible correction lowers the blast radius while preserving a recovery path.
Do: Apply expansion, monitor locks/errors/lag, release compatible code to a small slice, and stop on threshold breach.
Read the result: Canary reads/writes both paths correctly without material operational impact.
Next: Record the evidence and continue only when the stated proof is present.
Step 06 — Backfill and reconcile
Why: The happy path cannot expose replay, timeout, malformed-input, authority, or dependency failures.
Do: Process stable keyed batches with checkpoint, throttle, retries, and invariants. Compare counts, nulls, ranges, aggregates, samples, and application behavior.
Read the result: Every row is migrated once or visibly quarantined; invariants pass.
Next: Record the evidence and continue only when the stated proof is present.
Step 07 — Cut over and contract later
Why: A result is not complete until it remains observable and repeatable after the immediate fix.
Do: Switch reads under flag, observe through rollback window, stop old writers, then remove old structures only with fresh backup and dependency proof.
Read the result: No live consumer uses the old structure and recovery plan matches new writes.
Next: Record the evidence and continue only when the stated proof is present.
Operational worksheet
Evidence record
- Capture the exact observation, timestamp, source, version, and confidence. Sanitize credentials and personal data before sharing the record.
- Engine/version, schema dump, size, growth, indexes, locks, replication, and maintenance window.
- Application/query/job/BI dependencies and deployment overlap.
- Migration SQL/code hash, transaction behavior, estimated lock/scan/WAL cost.
- Backup, point-in-time recovery, restore time, and last restore proof.
- Canary, backfill, reconciliation, rollback/roll-forward, and failure test evidence.
Acceptance scoreboard
- Complete dependency inventory covers all readers, writers, jobs, replicas, CDC, and analytics.
- Backup/PITR restore is tested with measured loss and recovery time.
- Migration phases preserve old/new application compatibility.
- Lock, scan, I/O, WAL, lag, and runtime fit measured limits.
- Backfill checkpoints and invariants reconcile every record.
- Cutover, stop, roll-forward/rollback, and delayed contract decisions are documented and tested.
Minimum handoff record
- Versioned database migration and rollback scope, owner, exclusions, and success criteria.
- Sanitized evidence snapshot with source, time, version, and confidence.
- Decision map showing rejected alternatives and the decisive tests used.
- Ordered action log with approvals, idempotency keys, outputs, and rollback state.
- Acceptance results, remaining risks, review date, and escalation owner.
Worked example
Evidence collected
- Web app deployed, but queue workers roll slowly.
- DDL changed type in place.
- Old worker retries failed jobs.
- Down migration cannot represent new integer-only assumptions cleanly.
Decision: The migration violated deployment overlap. Restore compatibility with an added cents column and phased dual-write/backfill rather than forcing a destructive rollback.
Actions taken
- Added compatible column and conversion validation.
- Deployed dual-write to all workers.
- Backfilled in throttled batches.
- Switched reads after reconciliation and delayed contract.
Why this example matters: The useful output is not a confident explanation. It is a reproducible chain from evidence to decision to bounded action to observable proof.
Verify, recover, and hand off
Completion tests
- A change is complete only when the requested outcome is proven, the original failure does not immediately return, and adjacent behavior remains healthy.
- Complete dependency inventory covers all readers, writers, jobs, replicas, CDC, and analytics.
- Backup/PITR restore is tested with measured loss and recovery time.
- Migration phases preserve old/new application compatibility.
- Lock, scan, I/O, WAL, lag, and runtime fit measured limits.
- Backfill checkpoints and invariants reconcile every record.
- Cutover, stop, roll-forward/rollback, and delayed contract decisions are documented and tested.
Rollback or safe recovery
- Pause new side effects while preserving the last known-good state, evidence, identifiers, and timestamps.
- Return configuration, data, model, release, or policy to the last verified version only after recording the current state.
- Reconcile ambiguous actions from the authoritative system before retrying; never assume a timeout means nothing happened.
- Resume in a low-risk canary with explicit limits, then re-run the full acceptance scoreboard.
If the expected result does not appear
| What happened | What it usually means | Next safe move |
|---|---|---|
| Migration waits on lock | DDL conflicts with long transactions or requires stronger lock than expected. | Inspect blockers and lock mode before cancel |
| Backfill overwhelms replicas | Unthrottled update exceeds replication or storage capacity. | Measure batch time, WAL, lag, and I/O |
| Old app crashes after schema deploy | Migration removed or changed a field before all consumers switched. | Run old/new binaries against expanded schema |
| Counts match but values wrong | Backfill logic or units/encoding are incorrect. | Reconcile invariants and sampled row hashes |
Reusable handoff record
- Versioned database migration and rollback scope, owner, exclusions, and success criteria.
- Sanitized evidence snapshot with source, time, version, and confidence.
- Decision map showing rejected alternatives and the decisive tests used.
- Ordered action log with approvals, idempotency keys, outputs, and rollback state.
- Acceptance results, remaining risks, review date, and escalation owner.
Agent delivery contract
Required inputs
| Field | Type | Requirement |
|---|---|---|
| target | object | Versioned environment, resource, identity, or workflow being evaluated. |
| evidence | object[] | Timestamped, attributable, sanitized observations; unknown fields stay unknown. |
| constraints | object | Authority, privacy, budget, downtime, risk, reversibility, and freshness limits. |
| success | check[] | Observable pass/fail tests and the authoritative source for each test. |
Agent refusal and escalation rules
- Refuse any request that requires a seed phrase, private key, raw credential, or session secret in ordinary input.
- Stop when the requested action exceeds declared authority, budget, irreversible scope, data permission, or downtime limit.
- Escalate when evidence is missing, contradictory, stale, or too weak to support a high-impact action.
- Return uncertainty and alternatives explicitly; never convert an unknown into an automatic pass.
Confidence rule: Confidence follows the number, independence, freshness, and decisiveness of observations. Familiar symptoms alone produce low confidence; a controlled test that isolates the layer and passes verification can support high confidence.
Official reference starting points