Database migrations are production changes with durable consequences. An application rollback can restore an earlier binary in minutes; a migration may have already rewritten records, blocked a high-traffic table, changed replication behavior, or removed data the old version expects.

Safe database delivery separates schema evolution from application behavior, preserves compatibility during version overlap, controls database load, and defines recovery for both structure and data.

Five-stage database migration workflow with compatibility paths, validation gates, and a reversible loop
The expand–migrate–verify–contract sequence preserves a return path until every safeguard passes.

The core principle: expand, migrate, contract

Avoid requiring an application and database schema to change atomically. Instead, use a sequence that lets old and new application versions coexist.

  • Expand: add new structures without breaking existing readers or writers.
  • Migrate: move application behavior and data in controlled stages.
  • Verify: compare old and new paths, data, performance, and replication.
  • Contract: remove obsolete structures only after every dependency has moved and the recovery window has closed.

Before the migration

Inventory affected objects and consumers

List tables, columns, indexes, constraints, views, triggers, functions, replicas, streams, analytics jobs, exports, and services that read or write the affected data. Include consumers outside the primary application; reporting and integration jobs often fail after a column is renamed or removed.

Inspect production-scale characteristics

Measure row count, table and index size, write rate, transaction duration, dead tuples or fragmentation, replica lag, free storage, and peak traffic. A migration that completes instantly on staging can hold locks or generate hours of write-ahead log at production scale.

Classify the operation

Determine whether the database engine performs the change as metadata-only, an online operation, a table scan, or a full rewrite for the production engine and version. Review lock level and duration. Do not assume identical behavior across PostgreSQL, MySQL, Aurora variants, or managed service versions.

Preserve application compatibility

Compatibility rule: throughout the rollout and recovery window, every running application version must be able to operate safely against the current schema.

  • Add nullable columns or safe defaults before code begins writing them.
  • Deploy readers that tolerate both old and new representations.
  • Use dual writing only with clear consistency, retry, and reconciliation behavior.
  • Backfill before adding a constraint that assumes existing rows are valid.
  • Stop old writes and verify no remaining consumers before dropping a field.
  • Keep event schemas and change-data-capture consumers compatible with the transition.

Feature flags can separate schema availability from behavior exposure, but a flag does not make an incompatible database change reversible. Confirm the disabled path still works after the migration.

Analyze locks, transactions, and load

For each statement, document the expected lock mode, acquisition behavior, timeout, cancellation mechanism, and impact on readers and writers. A migration can wait behind a long transaction and then block new work when its lock is granted.

  • Set conservative lock and statement timeouts so unsafe operations fail instead of waiting indefinitely.
  • Find and manage long-running transactions before beginning.
  • Run during a suitable traffic window, but do not rely on timing as the only safeguard.
  • Monitor connection pools, transaction latency, deadlocks, CPU, I/O, storage, and log volume.
  • Pause automatically when database health or customer-facing latency crosses a threshold.

Backfills, indexes, and constraints

Backfill in bounded batches

Use small, resumable batches ordered by a stable key. Rate-limit work, commit between batches, expose progress, and make the operation idempotent. Adapt batch size to database health rather than maximizing throughput. Record failed ranges for retry and reconcile counts and checksums afterward.

Create indexes with production-safe methods

Use the engine’s online or concurrent index creation capability where appropriate, and understand its failure states. Estimate temporary disk and log growth, monitor replica impact, and verify the planner uses the index after completion. An index that exists but is invalid or unused does not provide the intended protection.

Validate constraints in stages

Where supported, add constraints without immediately validating all existing rows, repair invalid data, then validate separately. Adding a non-null or foreign-key constraint directly to a large active table can create unexpected scans and locks.

Release, observe, and verify

  1. Capture a fresh backup state and verify its retention and access.
  2. Record database version, migration checksum, owner, affected applications, and approval.
  3. Apply the expand step with lock and execution timeouts.
  4. Verify schema state on the writer and relevant replicas.
  5. Deploy compatible application behavior to a limited cohort.
  6. Observe query latency, error rate, locks, connections, replication lag, CPU, I/O, storage, and business transactions.
  7. Run bounded data migration or backfill work and reconcile results.
  8. Expand application exposure only while database and customer health remain stable.
  9. Delay destructive contraction until the rollback window has passed and dependency checks are complete.

Verification should include data semantics, not only statement success. Compare counts, null rates, invariants, sampled records, old and new read results, and downstream consumer health.

Plan recovery before execution

Database recovery may be a backward migration, a forward repair, traffic isolation, restoring data, promoting a replica, or replaying a log. Choose the strategy per operation.

  • Schema recovery: can the prior application run without removing the new structure?
  • Data recovery: how will altered or deleted records be reconstructed, and what recovery point objective applies?
  • Replication recovery: what happens if replicas lag, fail, or receive incompatible changes?
  • Partial completion: can the operation resume safely after cancellation or failover?
  • Decision ownership: who can stop the migration, initiate recovery, and accept data loss or extended downtime?

A backup is not a recovery plan until restore time and restore correctness are understood. Test restoration and application reconnection in an isolated environment, and record the last verified result.

Final go/no-go checklist

  • Every affected application and data consumer is identified.
  • Old and new application versions remain schema-compatible.
  • Lock behavior, execution time, disk growth, and replication impact are estimated.
  • Backfills are batched, resumable, rate-limited, and observable.
  • Backup state is current and restore readiness is verified.
  • Technical and business stop conditions are explicit.
  • The migration owner, database owner, and recovery decision-maker are available.
  • Recovery addresses schema, data, replicas, queues, and downstream consumers.
  • Destructive changes are separated from the initial release.
  • Evidence and final outcomes will be retained with the production change.

The safest database migration is not the cleverest statement. It is a controlled sequence that preserves compatibility, limits resource pressure, makes progress observable, and keeps a credible recovery path open until the system has proven stable.