deskthebuildDATABASES
← All notes · 5 min read

How would you migrate a large production table without downtime?

Suppose a hypothetical service needs to replace one overloaded column with a normalized representation across a table containing hundreds of millions of rows. The application must continue reading and writing while several versions are deployed. A single blocking migration or one enormous update could lock the table, fill transaction logs, or overwhelm replicas.

Zero downtime does not mean the migration has no cost. It means the cost is controlled, compatibility is maintained, and every phase has a safe rollback.

Why the common answer is incomplete

Most candidates answer: “Add the new column, backfill it, and rename it.”

Those are useful phases, but the difficult details are how old and new application versions coexist, how concurrent writes reach the new representation, how backfill avoids overwriting fresh values, and how the team proves completeness before deleting the old path. A rename can also take a lock or break cached queries depending on the database.

A production migration uses expand-and-contract with versioned writes, checkpointed backfill, verification, and gradual read cutover.

A production approach

1. Establish invariants and rollback boundaries

Define the source of truth during each phase, the acceptable data lag, and how correctness will be compared. Inventory readers, writers, batch jobs, replicas, exports, and downstream consumers. Decide whether the transformation is reversible. A migration plan should state which step becomes irreversible and what backup or reconstruction path exists before crossing it.

2. Expand the schema compatibly

Add the new nullable column or table without removing or changing the old one. Use an online or low-lock operation supported by the database and test it against a production-like schema and transaction load. Add constraints initially as non-validating when the engine supports that pattern, then validate later. Old application versions must continue operating during this phase.

3. Deploy compatible writes

Update the application to write both representations from one validated input, or write the old form and emit a durable change event that populates the new form. Prefer one service to own the transformation. Track divergence. Dual writes to separate systems are not atomic by default; if both must succeed, use an outbox or make one clearly authoritative with repair logic.

4. Backfill in bounded, resumable batches

Process rows by a stable primary-key range or another checkpointable order. Keep transactions small, pause between batches, and cap database resource use. Record progress durably. Update only rows whose new value is absent or whose source version matches the backfill snapshot so an old batch cannot overwrite a concurrent application write.

5. Protect replicas and maintenance work

Monitor replication lag, transaction-log growth, vacuum or cleanup pressure, buffer-cache churn, and lock waits. Slow or pause the backfill when service objectives are threatened. Run during lower demand only as an additional safeguard, not as the sole control. A job that is safe at midnight can still run into a failover or traffic anomaly.

6. Verify continuously and reconcile

Compare counts, null rates, checksums or normalized value hashes, and business invariants across old and new representations. Sample records across tenants, time ranges, and edge cases. Maintain a reconciliation job that repairs mismatches from failed dual writes or transformation bugs. Queue completion alone does not prove every eligible row is correct.

7. Canary and switch reads

Read the new representation for internal users or a small cohort while shadow-comparing with the old value. Define how to handle mismatches. Increase exposure only after correctness and latency remain within thresholds. Keep writes compatible with both readers until all old application versions and jobs are gone. Feature flags make rollback a routing change rather than another database migration.

8. Contract only after evidence

Stop old writes, wait through the maximum deployment and job lifetime, and verify no consumers query the old representation. Validate new constraints and indexes. Archive or export what policy requires, then remove old columns or tables through a reviewed low-lock operation. Continue monitoring after cleanup because query plans and storage behavior may change.

Engineering trade-offs

Dual representations consume storage and write capacity. A slower backfill reduces production impact but prolongs the compatibility window. Application dual writes are simple inside one transaction, while event-driven propagation handles separate stores at the cost of temporary lag. Shadow reads improve confidence but double selected query work.

Database-native online migration tools can reduce locking while adding triggers, replicas, or copy tables that need their own capacity and cleanup. A maintenance window may be simpler for a small low-availability system. “Zero downtime” is justified only when product availability requirements outweigh the operational complexity.

Common mistakes

  • Combining schema change, full backfill, and constraint validation in one transaction.
  • Assuming a nullable column addition is non-blocking on every engine and version.
  • Letting backfill overwrite values written by the new application.
  • Starting new reads before all write paths populate the new representation.
  • Declaring completion from processed-row count without comparing business invariants.
  • Removing the old column while stale application instances still reference it.
  • Running a backfill without pause controls, checkpoints, or replica monitoring.

Failure handling and observability

Track phase, schema version, application writer version, backfill checkpoint, rows scanned and changed, batch latency, lock wait, database load, log volume, replication lag, mismatch count, and read-cohort errors. Alert on divergence and resource pressure. Keep an operator control to pause, resume, reduce batch size, or switch reads back without editing job state manually.

If a batch fails, resume from the last durable checkpoint and reprocess idempotently. If dual-write divergence rises, stop read rollout and repair from the authoritative value. If database health degrades, pause the migration before scaling the job. If a transformation bug is discovered, version the transformation and rebuild affected rows; do not hide mismatches with a one-off manual update.

Interview insight

The interviewer is not testing whether you remember the phrase “expand and contract.” They are testing whether you can maintain compatibility while data and code change independently. A staff-level answer makes every phase observable, resumable, reversible where possible, and safe under concurrent production writes.