Case Study: Zero-Downtime Storage and Schema Migration

The cutover review has been running for eleven minutes when the storage lead asks the question that stops it.

“If the new row and the old document disagree after we switch reads, which value is true?”

LedgerBay’s migration dashboard is green. A backfill has copied 720 million account-policy records from a JSON field in the account store into normalized policy and entitlement tables. Change capture lag is eight seconds. Twelve million shadow reads have found only 720 differences, all marked repaired. Target read p99 is 71 ms against an 85 ms migration gate. The release plan says, “Enable new reads at 10%, then 100%. Roll back the flag if errors rise.”

None of those statements answers the question.

The plan does not say whether the old store remains authoritative after new reads begin. It does not say whether a repair may overwrite a newer target value, whether an old application instance can still write the retired JSON shape, or whether rolling the read flag back is safe after a target-only write. “Zero downtime” has hidden three separate promises: requests remain available, accepted writes preserve their semantics, and every logical record has one explainable authority throughout the transition.

LedgerBay pauses the cutover. It treats the migration as a distributed system operating beside production rather than as a background copy job. That change of frame determines the workload model, compatibility window, telemetry, failure tests, and point of no return.

All service names and measurements in this case are fictional. The evidence packet is deterministic modeled teaching data, not a report from a production database.

Put authority in a ledger before moving bytes

The source is a sharded relational account store. Each account row contains a policy_document JSON value. Reads decode the document, compute effective entitlements, and cache them. Growth has made that path expensive: updates rewrite the document and its indexes, unrelated fields contend on the same row, and policy queries repeatedly parse data that should be addressable.

The target separates policy identity, rules, grants, and version metadata. That improves query shape and ownership, but it also changes representation. A byte comparison cannot decide semantic equality because field order, defaults, normalized identifiers, and derived values differ.

The initial requirement—“move policies without downtime and keep rollback for a week”—becomes a set of questions:

• What event makes an accepted policy mutation durable?
• Which representation authorizes a read, a write, and a repair in each phase?
• Which application and worker versions can coexist?
• How are deletes, missing fields, default changes, and out-of-order events represented?
• Does one logical write commit atomically to both stores? If not, how is partial success recovered?
• What makes two differently shaped records semantically equal?
• How stale may the target be before a shadow result is invalid evidence?
• Which action makes old-state rollback impossible, and who approves it?

LedgerBay answers with an authority ledger. “Dual write” is not an authority state; it is only a delivery mechanism.

The compatibility window starts when the first expanded schema or dual-capable application reaches production. It ends only when every old writer, reader, delayed job, replay source, client contract, and disaster-recovery procedure that can emit the old semantics is retired or translated. A seven-day observation timer is irrelevant if a monthly billing job can still write the old shape.

The record envelope carries a stable logical key, schema generation, monotonically comparable source version, mutation identifier, event time, capture position, tombstone state, and canonical semantic digest. The source version is assigned at the authoritative commit, not when a backfill worker observes the row. Mutation identifiers are stable across retries. A transform is a versioned pure function of source state plus named reference data.

That envelope makes authority testable. Without it, “last write wins” usually means “whichever clock, consumer, or repair happened last,” which is not a correctness rule.

Build the transition as seven controlled phases

LedgerBay uses expand–migrate–contract, but the useful detail is in the gates between verbs.

Expand without changing authority

The first deployment creates nullable target structures, version columns, capture offsets, a reconciliation table, and code capable of understanding old and new schema generations. Old behavior remains authoritative. Database changes are additive and their lock and rewrite behavior are tested against a production-like copy before deployment.

New application code can write the existing shape and emit a stable mutation identity. It does not yet require the target. Old application instances continue to operate. This ordering matters: changing a producer before every consumer tolerates the new shape turns a rolling deploy into a compatibility outage.

The team rejects an in-place conversion of the JSON column. It would combine physical rewrite, logical transformation, index build, application release, and rollback into one large failure domain. It also rejects dropping the old field after a successful staging migration; staging does not contain LedgerBay’s live mutation races, delayed workers, or production key skew.

Capture the ordered change stream

Change capture begins from a named source position. The capture record includes enough before/after or reconstructable state to express update and delete semantics. The consumer writes the target with this rule:

apply(event):
  existing = target[event.logical_key]
  if existing.source_version >= event.source_version:
      acknowledge duplicate or stale event
  else:
      write transform(event.source_state)
      set source_version = event.source_version
      set mutation_id = event.mutation_id
      commit target row and applied position together

Exactly-once transport is unnecessary for this boundary. At-least-once delivery plus version-guarded idempotent application is sufficient if a target commit and applied position cannot disagree. If the sink commits data and crashes before recording progress, replay is harmless. If it records progress before data, an acknowledged hole is possible; that ordering is forbidden.

Capture is not schema replication. PostgreSQL’s current logical-replication documentation, for example, states that schema changes are not replicated automatically and that incoming data can error when subscriber schema is incompatible. That is a transfer limit, not an implementation recommendation: LedgerBay must coordinate schema compatibility independently of the change stream.

Backfill a snapshot without erasing newer changes

The backfill enumerates a consistent source snapshot at capture position (P_0). It partitions by stable logical-key ranges, records checkpoints, and applies the same transform as the live consumer. A backfill row carries the source version seen in the snapshot. Its conditional write cannot replace a target row whose captured version is newer.

This resolves the classic race:

1. snapshot sees account 42 at version 10;
2. production updates it to version 11;
3. capture applies version 11 to the target;
4. a slow backfill worker reaches account 42;
5. the target rejects version 10 as stale.

An unconditional upsert would silently restore old state. Scheduling backfill before capture would create a gap between snapshot and stream. Starting capture first and pinning (P_0) creates an ordered join between bulk history and live mutations.

Workers use bounded pages, deterministic range ownership, resumable checkpoints, and per-partition rate limits. A poisoned record goes to a quarantine lane with its key, version, transform error, and retry state; it does not block unrelated ranges or disappear into a skipped counter. Completion means every source range is complete or explicitly quarantined and every capture event after (P_0) is applied or accounted for.

Compare semantics, not serialization

Shadow reads sample by tenant, key range, schema generation, mutation type, record age, and high-risk feature. The source and target are projected into one canonical semantic model. Defaults are materialized, unordered collections are sorted, identifiers are normalized, and derived fields are compared under a versioned rule.

A mismatch record contains:

logical key and tenant
source version, target source_version, and capture position
source schema generation and transform generation
canonical digests plus field-level difference class
last mutation identity and writer version
authority at comparison time
repair decision, owner, and terminal result

The comparator first checks version alignment. Comparing source version 104 with a target that has only reached 103 measures lag, not divergence. Missing target rows, stale target rows, transform differences, target-only rows, delete disagreements, and comparator faults are separate populations.

Twelve million shadow reads find 720 differences, or 0.006%. That is not “99.994% correct” permission to cut over. The 720 are evidence about mechanisms. In the fixture, they separate into a deprecated writer emitting an old default, a delete transform that omitted tombstones, and retries whose mutation identity changed. Each class requires a repair and prevention rule. The gate is zero unexplained divergence across the declared coverage window, not an attractive aggregate percentage.

Repair reads current authority again, compares versions, and issues a conditional mutation. A human-approved repair generated from yesterday’s diff may not overwrite today’s valid update. The repair pipeline is therefore another idempotent writer with its own rate, errors, and audit trail.

Price the migration against foreground capacity

The storage team initially proposes “as fast as possible overnight.” That is not a budget. A migration competes for source reads, target writes, transaction-log bandwidth, replicas, network, cache, locks, compaction, checkpoint I/O, and operator attention. The limiting resource can move during the run.

The fixture declares:

• 720 million source records;
• 1,700 source bytes and 1,200 target bytes per average record;
• 2,800 logical writes/s at the modeled peak;
• 1.15 captured change events per logical write;
• 24,000 available source read IOPS;
• 14,500 foreground peak read IOPS;
• 3,000 IOPS reserved for growth and failure;
• 5,200 backfill records/s; and
• 7,200 change events/s of apply capacity.

The physical data volumes are:

B_source = 720,000,000 records × 1,700 B/record = 1.224 × 10^12 B
B_target = 720,000,000 records × 1,200 B/record = 864 × 10^9 B

These are decimal bytes. They do not include indexes, write-ahead logs, compaction, replicas, network framing, or temporary structures. Those belong as separate measured terms in a production capacity ledger.

The foreground-governed source budget is:

IOPS_migration ≤ 24,000 - 14,500 - 3,000 = 6,500 IOPS

At 5,200 records/s, the idealized backfill lasts 138,461.54 seconds, or 38.46 hours. It scans 8.84 MB/s and writes 6.24 MB/s before physical amplification. A completion date based on row count alone is false precision; the run pauses or slows when foreground gates bind.

During migration, a logical write produces 3,300 modeled bytes across old and new representations and target indexes instead of 1,700 baseline bytes:

A_write = 3,300 physical B/write ÷ 1,700 baseline B/write = 1.941176

That 1.94× is a fixture ratio, not a database constant. A real measurement includes log generation, replicas, index maintenance, transaction retries, compaction, and change-stream retention. The team measures those terms by phase because backfill and dual write stress different resources.

The throttle is a controller, not a fixed sleep. Every minute it evaluates foreground read and write p95/p99, lock wait, replica lag, log and capture retention, cache miss rate, target apply latency, disk and network queue, error budget, and remaining reserve. Background concurrency rises slowly when every gate is healthy and falls quickly when any protective gate is crossed. A manual kill switch stops new page claims while letting in-flight idempotent batches settle.

This asymmetry prevents oscillation. A controller that adds ten workers after one green minute and removes them after one red minute can create its own periodic overload. Rate changes are bounded, have hold periods, and record the reason.

Lag is queued migration work

At peak, change capture receives:

λ_capture = 2,800 logical writes/s × 1.15 events/write = 3,220 events/s

Apply capacity has a 3,980 event/s catch-up surplus. If the target consumer is unavailable for fourteen minutes, it accumulates 2,704,800 events. Once restored, ideal catch-up time is:

T_catchup = 2,704,800 events ÷ (7,200 - 3,220 events/s) = 679.60 s

That is 11.33 minutes only if foreground arrival rate, apply capacity, event cost, and ordering remain as modeled. The cutover gate uses age and position lag by partition, not only event count. Ten thousand large policy changes may cost more than a million tombstones. A partition at zero lag cannot hide another that is hours behind.

The team reserves retention for the worst credible outage plus recovery, not just normal lag. Losing capture history before the last durable applied position turns replay into a new snapshot or an authority gap.

Change read authority before write authority

After backfill, live capture, semantic comparison, and performance gates hold, LedgerBay enables target shadow reads without serving them. It then serves the target to internal tenants, 1%, 5%, 25%, 50%, and 100% of eligible reads. Cohorts are stable and stratified; a random sample dominated by small accounts would miss high-cardinality policy behavior.

For each cohort the release checks correctness, missing/stale/fallback populations, p50/p95/p99, cache behavior, source and target load, capture lag, and repair rate. The old read remains available, but fallback is explicit. If every target miss silently reads old state, the target can appear correct while never becoming complete. Fallback rate is a migration gate and every fallback records why it happened.

Read cutover does not yet change write authority. The old commit version still orders state. That gives a cheap read rollback: route serving reads to old while target capture continues. It does not justify deleting the target or ignoring divergences.

Write-authority cutover is the harder boundary. LedgerBay compares three designs:

LedgerBay keeps old authority through read cutover. Before changing write authority, it deploys a target-first writer with a durable outbox and a reverse compatibility projection. A request succeeds only after the target authoritative commit and outbox record commit together. Updating the old rollback shadow is asynchronous but versioned, monitored, and bounded. If the reverse projection cannot represent a new policy feature, enabling that feature is a point-of-no-return decision even if the old table still exists.

The team rejects “write both and return success if either succeeds.” That creates two possible truths. It also rejects returning success only after two independent commits without a recovery record. A timeout after the first commit can lead the caller to retry while the service does not know whether it is completing or duplicating the logical mutation.

Mutation identity and version semantics close the ambiguity. One logical command retains its identity across client, service, outbox, capture, target, reverse projection, and repair. A replay observes the terminal result or conditionally completes missing work. Versions order mutations within the logical entity; wall-clock timestamps do not arbitrate concurrent truth.

Name the points of no return

“Rollback remains possible” must identify what is preserved and how long restoration takes. LedgerBay records four increasingly expensive boundaries:

1. Target shadow only. Stop capture and backfill; old is unchanged. Rollback is minutes.
2. Target serves reads; old writes. Route reads back; retain target for diagnosis. Rollback is minutes, subject to cache and routing convergence.
3. Target writes; old receives a lossless reverse projection. Freeze new target-only semantics, drain and reconcile the reverse stream, then switch authority back. Rollback is an operated reverse migration, not one flag.
4. Target-only semantics, old contract removal, or old data deletion. The old model cannot represent accepted state. Recovery means restoring retained source evidence and applying a forward repair; old-authority rollback is no longer valid.

The point-of-no-return review requires zero unexplained divergence, bounded lag by partition, tested reverse replay, retained source and target backups, compatible disaster recovery, no old writers beyond the declared window, and business approval for features that cannot project backward. Contract happens only after this review and the observation window.

Contract removes compatibility in a safe order: block old writer generations; prove no traffic; disable reverse projection; retain immutable evidence; stop old reads; remove dual-path code; archive or delete old data under retention policy; then remove expanded compatibility fields. Removing old schema first would convert a latent caller into an outage.

Break the migration before trusting it

LedgerBay runs four trials with production-shaped keys and open-loop foreground traffic.

Mixed application deploy

Old and new application versions overlap for the maximum rollout and rollback interval. Old writers emit generation 3; new readers understand generations 3 and 4. Unknown generation 5 fails closed before mutation. The trial covers long-lived workers and cached schema metadata, not only HTTP instances.

Pass conditions: no accepted write becomes unreadable; mutation identity survives retry; foreground tails stay within the migration envelope; and rolling back the application does not require rolling back data.

Capture failure and retention pressure

The consumer is stopped for fourteen minutes while foreground writes continue. Operators must distinguish source position, transport position, per-partition apply position, and age of oldest unapplied event. After restart, the consumer catches up without starving reads or exhausting retained history.

Pass conditions: no position hole; conditional replay produces no stale overwrite; actual catch-up stays within the reserved window; and throttling protects foreground and log-retention gates.

Backfill/capture race and poison data

The trial delays one backfill range, updates keys inside it, duplicates capture events, reorders permissible partitions, and injects a record the new schema cannot represent. The poison record is quarantined with authority intact. Healthy ranges continue. Repair cannot overwrite a newer source version.

Pass conditions: the target converges to the authoritative version, quarantined keys remain visible and bounded, and completion cannot be declared while an unexplained key remains.

Cutover, target loss, and replay

During a 25% target-read canary, one target shard becomes unavailable. Later, after write-authority cutover, the reverse projector is paused and target responses time out after commit. The team practices both cheap read rollback and the more expensive authority reversal.

Pass conditions: callers obtain one stable mutation result; served state never silently moves backward; fallback and rejection are classified; recovery does not exceed foreground capacity; and operators can state which store is authoritative at every minute.

Evidence that permits each gate

The migration board aligns four clocks: request, authoritative commit, capture/apply, and reconciliation. A trace links one logical mutation across every attempt and representation. Metrics are segmented by phase, key range, tenant, schema generation, writer version, transform generation, and result state.

The minimum board includes:

• foreground offered rate, success, p95/p99, saturation, and error-budget consumption;
• source/target operations, bytes, log volume, locks, cache, replicas, and storage queues;
• backfill page rate, oldest checkpoint age, completed/quarantined ranges, and retry work;
• capture source/apply positions, lag age and count by partition, duplicates, and retention margin;
• shadow population, aligned comparisons, missing/stale/semantic differences, repair attempts, and reopen rate;
• reads and writes by authority generation, fallback reason, old-writer traffic, and unknown schemas; and
• rollback assets, reverse-projection lag, backup restore test, and time remaining before cleanup approval.

Observed evidence and modeled evidence stay separate. The 38.46-hour backfill, 1.94× modeled write amplification, and 11.33-minute ideal catch-up come from declared inputs. Production gates require measured distributions, physical amplification, failure repetitions, and uncertainty. A successful checksum of one snapshot does not prove live convergence. A green average lag does not prove the hottest partition is current.

The design draws on Stripe’s first-party account of online migrations, particularly its staged dual writing, backfill, comparative reads, and incremental retirement. That account demonstrates one approach, not a universal transaction protocol. PostgreSQL’s logical-replication restrictions make a useful implementation-specific warning: schema definitions are not automatically kept compatible. PostgreSQL’s logical-replication monitoring documentation provides concrete position and worker observability concepts. LedgerBay’s phases, rates, schema, and gates are not claims about those systems.

Run the evidence packet:

cd examples/performance-engineering-system-design-handbook/part-08/zero-downtime-storage-schema-migration
node analyze.mjs
node verify.mjs

The design decision

Decision. Keep the old commit version authoritative through expand, ordered change capture, snapshot backfill, semantic comparison, and target-read cutover. Make every migration writer version-guarded and idempotent. Move write authority only after a durable target commit/outbox boundary and a tested reverse compatibility path. Throttle all background work against foreground SLO and reserve gates.

Cutover gates. Change lag at most 20 seconds by every partition; zero unexplained divergence; at least 20% declared foreground headroom; target read p99 at most 85 ms; no unknown active writer generation; tested replay, target loss, and rollback; named authority and incident owner.

Fixture result. At the recorded gate, lag is eight seconds, unexplained divergence is zero, headroom is 27%, and target p99 is 71 ms. The fixture derives 1.224 TB of source values, 864 GB of target values, a 6,500 IOPS migration ceiling, 38.46 ideal backfill hours, 3,220 live events/s, 2,704,800 events after a fourteen-minute outage, 11.33 ideal catch-up minutes, and 720 detected differences requiring explanation.

Rejected defaults. An in-place rewrite combines too many boundaries. Starting backfill before capture creates a gap. Unconditional upsert permits stale overwrite. Two independent writes do not create atomic truth. Random shadow sampling hides risky strata. A low mismatch percentage does not explain divergence. Fixed worker concurrency ignores changing constraints. A read flag is not write-authority rollback. Keeping an old table is not rollback if new accepted semantics cannot map into it.

Revisit. Foreground tails or error budget bind; capture/apply lag exceeds retention or cutover bounds; physical amplification departs materially from the ledger; a schema or writer generation is unknown; any divergence is unexplained; quarantine grows; reverse projection is lossy; restoration evidence expires; workload mix changes 15%; or a new target feature crosses the declared point of no return.

Applied work

Field exercise: the fast backfill consumes the reserve

A team raises backfill from 5,200 to 8,000 records/s to finish before a launch. Source capacity is 24,000 read IOPS. Foreground reaches 14,500 IOPS at p99, and the failure/growth reserve is 3,000 IOPS. Each backfill record costs one source read operation. During the trial, foreground p99 remains green for ten minutes, replica lag rises, and the cache hit rate improves because the scan warms common pages.

Should the team keep the new rate? Define the next test.

Answer guide

No. The declared background ceiling is 6,500 IOPS. An 8,000-record/s scan consumes 1,500 IOPS of reserved capacity before physical effects, index access, retries, or checkpoint work. A ten-minute green p99 does not waive the reserve. Improved cache hit can be temporary and can reverse when the scan displaces a different working set. Replica lag is already evidence that the current constraint moved.

Return below the ceiling, then measure operations and bytes per transformed record, log/replica amplification, cache residency by workload, and foreground distributions under a representative peak. Step background rate through bounded levels with hold periods. Inject the declared replica or storage degradation while foreground load remains open-loop. The maximum rate is the highest one that preserves every foreground and recovery gate, not the rate that finishes before a calendar event.

Principal exercise: rollback meets an old offline writer

LedgerBay has served reads and writes from the target for six days. The old store receives a lossless reverse projection with 12-second p99 lag. No online instance uses the old schema. A monthly compliance worker, paused during migration, can replay generation-3 commands from its own queue. A new target-only policy field has not yet been enabled. Product wants to delete the old JSON field tomorrow.

Define the point-of-no-return decision and rollout.

Answer guide

The compatibility window is still open because a delayed writer can emit generation 3. First inventory and fence that worker’s queue. Either upgrade it to emit a current mutation contract, translate its commands through a versioned adapter, or prove and record that its work may be discarded under product policy. A route-level absence of old writes is insufficient.

Before deletion, pause new feature enablement, drain and reconcile forward and reverse streams, verify zero unexplained divergence, restore both representations from backup, replay duplicated and delayed generation-3 commands, and prove stable mutation identity. Remove write capability before read capability, and observe for the maximum delayed-work interval. Only then can the review declare old-authority rollback closed.

If the target-only field is enabled before reverse compatibility closes, the point of no return moves immediately: old-authority rollback becomes a forward restore and repair. That may be an acceptable business decision, but it cannot be described as “flip the flag back.”

Migration field card

Before approving an online storage or schema migration, ask:

• What is authoritative for writes, reads, repairs, and rollback in this exact phase?
• When does the compatibility window truly end, including delayed workers and recovery procedures?
• Can one logical mutation be followed across every attempt and representation?
• Does backfill carry a source version and refuse to overwrite newer captured state?
• Are deletes, defaults, missing fields, target-only fields, and schema generations explicit?
• What are the foreground reserves and the current background budget by resource?
• Is lag measured by age and position for every partition?
• Does comparison align versions before judging semantic equality?
• Can repair itself race, duplicate, or overwrite a newer value?
• Which action is the next point of no return, and what rollback remains afterward?
• Have mixed deploy, capture loss, poison data, replay, target loss, and reverse migration been exercised?
• Is cleanup gated on evidence rather than elapsed time?

The migration is complete only when the new representation is authoritative, old behavior is demonstrably absent, retained rollback evidence matches the declared recovery strategy, and compatibility code can be removed without changing accepted semantics. Until then, the migration is live production workload with its own queues, state, objectives, and failure modes.

Carry one unresolved risk into overload analysis: several locally sensible controllers can respond at once. Reconstructing their actions minute by minute reveals how a system collapses even while every component follows its configuration.