Zero-downtime Schema Migration

Evolve a petabyte-scale database while old and new application versions coexist, with resumable backfill and a real rollback boundary. This worked design targets SDE-3; the scale figures below are exercise assumptions, not claims about a named company's implementation.

Clarify the prompt

Requirements and scope

The core deliverable is: Evolve a petabyte-scale database while old and new application versions coexist, with resumable backfill and a real rollback boundary. The first design implements the APIs below and the state constraints in the data model. Agree the latency, freshness, retention and failure policy before adding infrastructure. Every successful acknowledgment must have an explicit meaning: accepted intent, committed state or completed external work. These are separate milestones.

Capacity and bottleneck reasoning

At 1 PB and 1 GB/s effective scan throughput, a full read takes about 11.6 days before write amplification and contention. At 100 MB/s it takes about 116 days. Backfill is a long-running production workload requiring throttling and restart checkpoints.

Keep average and peak demand separate. Add replication, indexes, retries and safety headroom to raw arithmetic before selecting capacity. Load-test the busiest ownership key and recovery traffic as well as uniform happy-path throughput.

APIs and data model

• POST /migrations {planVersion} -> migrationId
• POST /migrations/{id}/pause -> stop claiming new chunks
• GET /migrations/{id} -> phase,rate,lag,validated ranges

migration(migration_id PK,phase,source_schema,target_schema,cutover_checkpoint)
chunk(migration_id,range_start,range_end,last_key,status,lease_generation)
row(id,source_version,old_value,new_value,migration_version)
validation(range,row_count,checksum,watermark)

Authorize resource ownership at the API boundary; do not trust IDs or state supplied by a client. Bind retry identity to the caller and payload for mutating operations. Reject an incompatible replay instead of returning an unrelated stored result.

Component relationships

Read the diagram

Follow the labeled relationship panels; they are separate directed paths, not one global timeline.

View full sizeOpen original

Zero-downtime Schema Migration: state boundaries

Reset viewClose

Zero-downtime Schema Migration: state boundaries

Step-by-step design

1. Establish the authority

Expand with a backward-compatible field/table and deploy code that can understand both representations. Choose one source of truth during transition; independently dual-writing two stores without reconciliation can diverge on partial failure.

2. Make retries and concurrent work safe

Backfill bounded key ranges with checkpointed leases. Apply only if the source version still matches the value read, so an old snapshot cannot overwrite a newer live update. Throttle against database latency, replication lag and write budget, not a fixed maximum copy rate.

3. Scale and recover the critical path

Validate counts and content at compatible watermarks, shadow reads and canary the new read path. Switch the read/write authority under a documented version boundary. Contract only after old binaries, replay streams and rollback windows no longer require the old schema.

Worked request and concurrency example

A backfill reads row R at version 10. A live request writes version 11 before the backfill applies. A conditional target write rejects the version-10 update, then retries from current source state or consumes the version-11 change event. Range checkpoints make worker restarts resume safely. At cutover, validate through watermark H; a row count from one moment and a checksum from another cannot establish equivalence under live writes.

Failure and recovery walkthrough

A new reader mishandles nulls. Before contract, revert readers to the still-maintained old representation. After dropping old fields or accepting data that cannot be represented there, instant rollback is no longer available; recovery needs a forward repair or preserved reverse transformation.

Read the diagram

This numbered trace follows one scenario; explain the durable evidence at each transition.

View full sizeOpen original

Zero-downtime Schema Migration: concrete trace

Reset viewClose

Zero-downtime Schema Migration: concrete trace

Deep dive: compatible versions before fast copying

This original exercise renames a payment amount from amount_minor to total_minor without changing its integer units or currency meaning. The table is too large to rewrite inside a deployment. Old and new binaries overlap for days. The invariant is that every accepted write has one authoritative value/version and every served value comes from that version or a documented compatible fallback. “Zero downtime” is an availability and latency objective under an admitted workload, not a promise that arbitrary DDL, partitions or incompatible writers can never interrupt service.

Make the compatibility matrix executable

For the first phase, the old field remains authoritative. A controlled database compatibility layer maintains the new field and its projection version atomically with every old-field mutation. New code still writes through that old-authority operation. A real implementation must enforce this for bulk jobs, imports, triggers and administrative writers too. Two independent application writes to different stores do not meet this atomic contract.

Do not allow old and new authority triggers to form a bidirectional feedback loop. Select one write direction per phase under controlled configuration, version it, and prove all accepted writers follow it. A stale binary attempting an old-authority write after cutover must fail safely rather than silently overwrite the new truth.

row(id, currency, amount_minor, total_minor,
    source_version, total_projection_version, deleted_at)
migration(id, phase, writer_generation, source_checkpoint, target_checkpoint)
chunk(migration_id, lower_key, upper_key, cursor, lease_generation, status)
validation(range_id, checkpoint, mismatch_count, sampled_values)

The renamed fields may use different physical types, but the exercise permits only the same representable integer domain until rollback is retired. A later new-only value is a separate compatibility decision. A schema version string alone does not prove compatibility.

Backfill without resurrecting an older value

A worker reads row R at version 7 with amount 100. A live transaction then changes R to version 8 with amount 125 and updates the compatible projection. The delayed worker must not write version 7 over version 8. For the same database, use an atomic conditional update:

UPDATE payment_row
SET total_minor = :snapshot_amount,
    total_projection_version = :snapshot_version
WHERE id = :id
  AND source_version = :snapshot_version
  AND deleted_at IS NULL
  AND (total_projection_version IS NULL
       OR total_projection_version < :snapshot_version);

Zero affected rows means re-evaluate current state or recognize an already-current projection; it does not mean force the old value. A concurrent deletion leaves either no row to update or a tombstone that fails the predicate. If the target is a different store, retain source versions and delete evidence there and reject an older snapshot upsert after a newer tombstone. A database update predicate cannot enforce a separate store's version ordering by itself.

Read the diagram

Worker reads row R at source version 7 → Live write commits version 8 and compatible projection → Delayed backfill attempts version 7 → Atomic source-version condition rejects stale write → Validation checks version 8 at common checkpoint

View full sizeOpen original

Zero-downtime Schema Migration: recovery decision

Reset viewClose

A completed scan is not proof that concurrent writes and deletes were applied correctly.

Partition by stable key ranges, not OFFSET pages over a mutating table. Persist a checkpoint after the chunk's effects are durable. Retrying a chunk must be harmless, and a stale lease generation cannot mark a newer worker's chunk complete. Record failed or skipped ranges so a high cursor cannot hide holes.

Budget the petabyte scan and change stream

At decimal units, scanning 1 PB at a sustained 2 GB/s takes 500,000 seconds, about 5.79 days before retries, indexes or live-work interference. This is an optimistic lower bound, not a completion forecast. At that scale, a small unbounded per-row overhead becomes material.

For a separate change stream, assume a 900 GB backlog, continuing changes at 0.5 GB/s and effective apply capacity 1.5 GB/s. Ideal catch-up is 900 seconds at 1 GB/s net drain. If apply capacity falls to 0.5 GB/s, lag never shrinks. Backfill and change replay must share a resource budget with production, including WAL/log retention, disk headroom, replica lag and connection capacity. Pause copy work when user-facing latency or safe storage thresholds regress; a fixed “maximum speed” is not a safety policy.

Cut over through evidence gates

1. Deploy expansion with a measured lock/DDL budget and verify the compatibility path on every writer class. Bound lock acquisition; do not let a deployment wait indefinitely behind a long transaction.
2. Backfill bounded ranges while preserving version/deletion rules and enough source history for replay.
3. Validate schema semantics, values, currency, null handling and deletes at compatible checkpoints. Compare per-range evidence and shadow-read results, not just global row counts.
4. Canary new reads for a stable cohort. Keep a safe fallback that rechecks current permissions and the correct value version. Record discrepancies rather than hiding all failures behind fallback success.
5. Before changing write authority, establish the writer-generation boundary and drain/reject incompatible old writers. Confirm the new path preserves the reverse-compatible representation needed for rollback.
6. Switch reads/writes in controlled stages, observe correctness and latency, and keep rollback generations/data for the declared window.
7. Contract only when old binaries, jobs, replay streams, restore paths and operational scripts no longer require the old field.

The precise lock/rewrite behavior depends on the database and operation. Consult PostgreSQL ALTER TABLE for a PostgreSQL implementation. A validation strategy can reduce long blocking work without making every schema command lock-free. Test the actual DDL against representative data and traffic.

Know where rollback ends

Before contract, an incorrect new reader can return to the maintained old representation. After authority changes, that remains safe only while reverse compatibility is actually maintained. Once a new-only value cannot be represented in the old schema, switching binaries back can lose meaning even if the old column still exists. After dropping it, recovery requires a tested reconstruction or forward repair; a backup is not an instantaneous undo of subsequent accepted writes.

For the rename, old and new integer domains remain identical, so reverse-compatible writes are possible during the rollback window. A future widening/semantic change must either preserve an explicit lossless reverse transform or close the old-reader rollback path before accepting incompatible data. Record that irreversible boundary in the runbook and require evidence before crossing it.

Migration assessment: prove the boundary

Question: Snapshot R/v7 arrives after a deletion at v8 in a separate projection. May an upsert recreate R because it is missing from the target's active index?

Question: Old binaries still write directly to amount_minor. Can new code declare total_minor authoritative just because the backfill finished?

Question: New code accepted a value the old schema cannot represent. Is rolling back the reader binary sufficient?

Score one point for a complete reader/writer matrix, versioned backfill/delete handling, checkpointed validation, enforced cutover authority and a stated irreversible boundary. Then explain what changes when the target is another database rather than another field.

Prerequisites: Change Data Capture, Transactions and Isolation and Consensus, Leader Election and Fencing. The schema, workload, SQL and runbook above are scoped teaching examples; repository model checks are not a live petabyte migration test.

Decisions and alternatives

Operational contract

Monitor remaining bytes/ranges, conditional-write conflicts, replica lag, application p99 and validation mismatch rate. Practice pause/resume and rollback before the destructive contract phase; require an explicit operator checkpoint for irreversible cleanup.

Choose alert thresholds from the agreed user-facing objective. Assign an owner for stuck work and a replay/repair procedure that preserves the original business identity. Bound queue depth, concurrency and recovery throughput so an outage does not produce an uncontrolled catch-up burst.

Foundation, senior and staff depth

• Foundation: Explain expand/backfill/validate/cutover/contract with one write authority.
• Senior: Work live-update and delete races; enforce a mixed-version writer boundary.
• Staff: Budget petabyte copy/replay and identify the first irreversible rollback boundary.

Practice before revealing the answer

Spend 45 minutes: 5 clarifying scope, 7 on capacity/APIs/state, 12 on the first architecture, 15 on the concurrency and failure examples, and 6 on trade-offs and operations. Draw your own design before comparing it with this chapter.

Question: Why is successful backfill completion insufficient evidence for cutover?

Verification and rollout

Race snapshot writes with live updates and deletes, retry completed chunks and reject stale chunk owners. Validate values and tombstones at a compatible checkpoint before cutover. Test old-writer rejection and lossless rollback before contract; prove forward-repair readiness before accepting new-only values or dropping old fields.

Primary technical reference

Official documentation for the mechanism used in this exercise. This lesson is an original interview design, not a description of undisclosed company internals.

Related study

Before this design, review these mechanisms and identify which owns the critical state transition:

• Change Data Capture
• Sharding

Use the Interview Playbook to adapt the depth and timing, then return to the question breakdown overview.

Shared foundation: Schema, API and Event Evolution

Schema, API and Event Evolution: Test mixed-version contracts, semantic meaning, retained replay and identity-preserving rollout or rollback. Work its failure history before extending this design.