SAYLOR The result you are building
Finished Result:
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.
Stop Before Proceeding:
Stop when no verified restore exists, a migration requires an unbounded production lock/scan, compatibility with the live application is unknown, or rollback would discard accepted new writes.
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
Start with the row that most closely matches the evidence. The first test isolates a layer; it is not permission to
make every available change.
Evidence Likely layer First decisive check What the result means
Migration waits on Concurrency Inspect blockers and lock mode DDL conflicts with long transactions or requires stronger lock
lock before cancel than expected.
Backfill overwhelms Workload/WAL Measure batch time, WAL, lag, Unthrottled update exceeds replication or storage capacity.
replicas and I/O
Old app crashes Compatibility Run old/new binaries against Migration removed or changed a field before all consumers
after schema deploy expanded schema switched.
Counts match but Data Reconcile invariants and Backfill logic or units/encoding are incorrect.
values wrong transform sampled row hashes
Down migration Recovery Compare new writes to old Schema reversal is not a safe rollback.
loses new data design representational capacityStep-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.
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.
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.
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.
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.
Procedure continued 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.
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.
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.
Decision rule SHIP / AUTOMATE GATE Proceed only when every required acceptance check is supported by direct evidence, rollback is available, and the remaining risk is explicitly owned. Unknown is not a pass.
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
Starting Problem:
A table column is changed from text dollars to integer cents in one migration, breaking an old worker still writing decimals.
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.
Proof Of Completion:
Old and new workers coexist, monetary invariants pass, replicas remain healthy, and the old field is removed only after all dependencies are proven off it.
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 Inspect blockers and lock mode before cancel requires stronger lock than expected.
Backfill overwhelms replicas Unthrottled update exceeds replication Measure batch time, WAL, lag, and I/O or storage capacity.
Old app crashes after schema Migration removed or changed a field Run old/new binaries against expanded schema deploy before all consumers switched.
Counts match but values Backfill logic or units/encoding are Reconcile invariants and sampled row hashes wrong incorrect.
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.
Returned output
Field Type Requirement
diagnosis object Likely layer, supporting and conflicting evidence, alternatives, and confidence.
plan step[] Ordered bounded actions with owner, risk, expected proof, and stop condition.
verification check[] Observed pass/fail/unknown results, not inferred success from command exit alone.
handoff object Sanitized evidence record, recovery state, remaining risk, and next review trigger.
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
- https://www.postgresql.org/docs/current/sql-altertable.html
- https://www.postgresql.org/docs/current/backup.html
- https://docs.aws.amazon.com/prescriptive-guidance/latest/database-migration-best-practices/welcome.html