Planning a Safe Database Migration

Map compatibility before writing SQL

Describe the current schema, target representation, affected queries, writers, readers, jobs, reports, replicas, and external consumers. Identify application versions that may run concurrently during a rolling deployment. A direct rename or type change can break an older process immediately. Prefer additive preparation such as a nullable column, new table, compatible index, or parallel representation when overlap exists.

Write invariants and success criteria in data terms. Examples include every active order having one valid currency, no duplicate membership within a tenant, or old and new totals matching. Decide which layer enforces each rule during transition. Temporary dual reads or writes need one named authority and explicit conflict handling. Otherwise two representations can diverge while both appear operational.

Estimate operational impact

Schema statements differ across database engines and versions. Some acquire strong locks, scan a complete table, rewrite rows, build indexes, or generate substantial transaction logs. Test the exact operation against a production-like schema and representative volume. Measure duration, lock waiting, query latency, storage growth, replication lag, and cancellation behavior rather than relying on a small empty development database.

Choose an execution window and limits based on service objectives, not habit. Set appropriate statement and lock timeouts where supported. Coordinate connection pools, long-running transactions, background jobs, backups, and replicas. Confirm the migration runner's permissions and transaction behavior. A transaction can make selected statements atomic, but one oversized transaction may hold locks and delay replication or recovery for too long.

Move data in controlled phases

Deploy compatible application code before relying on the new structure. Backfill data in bounded, restartable batches ordered by a stable key. Record progress and make repeated processing safe. Throttle from observed database health, and avoid large offsets that become slower as the job advances. Concurrent writes require dual-write, change capture, or a final reconciliation strategy suited to the system.

Validate with counts, null rates, uniqueness checks, sampled record comparisons, aggregates, and application-level reads. Checksums can help only when canonicalization and scope are defined. Investigate mismatches instead of repeatedly overwriting them. Add constraints after existing data satisfies the invariant, using lower-impact validation features when the chosen database supports them. Monitor error rates and query plans as traffic moves to the new path.

Recover honestly and clean up deliberately

Before execution, define stop conditions and recovery for each phase. Disabling new reads may restore application behavior, but it cannot erase notifications, transformed values, or writes accepted only by the new schema. Restoring a backup loses later valid writes unless a replay or reconciliation plan exists. Practice recovery for high-consequence migrations and identify the person authorized to stop the operation.

After all readers and writers use the target and validation remains clean through an observation period, remove compatibility code and obsolete data in a separate reviewed step. Confirm old deployments cannot return, reports have moved, and retention obligations permit deletion. Update schema documentation, fixtures, disaster recovery procedures, and monitoring. Leaving duplicate representations indefinitely preserves migration risk rather than completing the work.

Replace a customer name with structured fields

A large customer table stores one display name, while a new workflow needs given and family names without interrupting existing clients.

1. Add nullable structured columns while keeping the original field, then deploy code that can read either representation and writes both deliberately.
2. Backfill stable batches, preserving ambiguous names for review rather than inventing a split that appears authoritative.
3. Compare counts and sampled records, monitor write agreement, and repair mismatches before making new reads primary.
4. Move readers gradually, retain a tested fallback during observation, and define how concurrent edits reconcile.
5. Only after every consumer migrates, decide whether the original display field remains a useful product value or can be removed safely.

Database migration runbook

Use this runbook for approval, execution, monitoring, and recovery.

• Scope: current and target schema, invariants, tables, volume, readers, writers, jobs, reports, replicas, and owners.
• Compatibility: deployment order, concurrent versions, additive phase, read policy, write policy, and contract phase.
• Execution: exact statements, engine version, locks, scans, batches, throttle, timeout, permissions, window, and operator.
• Proof: baseline, progress, counts, mismatches, latency, errors, query plans, replication, storage, and stop conditions.
• Recovery and closure: phase-specific action, backup limits, reconciliation, cleanup date, documentation, and final verification.

Common mistakes

• Renaming or dropping a column while older application instances still read it during a rolling deployment.
• Running one unbounded backfill transaction without measuring locks, logs, replicas, or restart behavior.
• Calling backup restoration a rollback without accounting for valid writes made after the backup point.

Try one

Sources

• GitHub database migration guideGitHub's operational description of background database migrations and upgrade safety.
• GitHub Actions documentationGitHub's primary guide to automated workflows, jobs, runners, and events.
• GitHub required status checksGitHub's documentation for protected branches, required checks, and review controls.