How online schema migration orders backfill, live writes and authority changes

A backfill reads a row, transforms it, and writes the new representation. While the transformation runs, a user changes the row. If the backfill writes last, it can replace the new value with an older one even though every individual copy succeeded. Completing a list of rows is therefore insufficient evidence that a new schema is ready to become authoritative.

An online schema migration needs a state machine for representation authority. It must say where accepted writes occur, how their effects reach the other representation, and which event permits readers and writers to change authority. Backfill ordering, partial dual-write failure, deletes, and rollback belong in that protocol.

Define the old and new representations

Use a stipulated database with stable row identities and monotonically increasing row versions. The old representation stores a case-preserving ASCII display name. The new representation stores its lowercase key. Transform F maps each old name to its lowercase form. For example, F("Alice") is "alice", and F("ALICE2") is "alice2".

F is not reversible: "Alice", "ALICE", and "alice" all produce the same new key. A system that later stores only the key cannot recover the original spelling from that key. The migration must decide whether case information remains necessary and retain it separately if rollback or application behavior depends on it. Lowercasing is a bounded example, not a general recommendation for person-name storage.

Initially the old representation is authoritative. Successful application writes change it, assign a new version, and follow the chosen propagation contract. The new representation is a derived copy until readiness is established. Readers requiring authoritative behavior continue to use the old representation during this phase, unless another explicitly defined read protocol applies.

The migration's intermediate states can be described as old-authoritative with capture active, backfilling with capture active, caught up with validation pending, and new-authoritative after a guarded transition. State names are useful only when their entry evidence exists. A "backfill complete" flag cannot substitute for captured concurrent mutations or a tested authority boundary.

Expose the database backfill overwrite race

At row R version 4, the old display name is "Alice". The backfill reads that name and version. Before it writes the transformed result, a live update changes R to version 5 with "ALICE2". Suppose the live propagation also sets the new key to "alice2" with version 5. An unconditional backfill write of "alice" now overwrites newer derived state.

F("Alice") was computed correctly, but its version-4 result was installed after the version-5 mutation. The old row ends at "ALICE2" while the new row ends at "alice". A copied-row count hides that conflict. The protocol must reject the stale installation or ensure it precedes the newer update's effect.

One approach uses a version-conditioned database backfill under an explicit transactional contract. The worker reads source version v and computes F. Its installation transaction verifies that the source still has that same identity and version and that no newer target state is being replaced. It writes the target result and version only if those conditions hold atomically with the installation.

In the example, source version 5 causes the version-4 installation to fail its condition. The worker records that outcome and obtains an admissible current value through the migration protocol. Simply checking the source in one transaction and writing the target later would reopen the race. The required comparison and install must share a contract that excludes an intervening incompatible mutation.

Deletion needs ordering too. If R is deleted after the worker reads it, its stale backfill must not recreate R in the new representation. A deletion version or tombstone can preserve the ordering fact. If a row identifier can be reused, an incarnation identity is also needed; matching the same numeric row ID does not prove that a new row is the old row the worker read.

Compare ordered replay with conditioned installation

Another approach obtains a consistent source snapshot and an associated mutation-log position q. It backfills that snapshot, then replays all committed mutations after q in the required order. The snapshot and log boundary must fit together without missing or duplicating changes in a way that corrupts the derived state. A casually recorded timestamp is not automatically such a boundary.

Source writes and their captured mutation records must commit under a declared atomic contract. Otherwise the source can change without a durable record for the new representation. The migration must preserve updates and deletes, bind records to row identity and version, and apply them idempotently or with ordering conditions if delivery can repeat.

The atomic-operations guide supplies a complete-transition comparison for database backfill. Its separately atomic reads and writes can admit two holders when an eligibility check remains disconnected from the claim. Our source-version comparison likewise needs to protect the installation it authorizes. The same principle applies to the declared source-and-capture transaction: both effects need a joint contract. The peer concerns abstract local ownership, without establishing this database's isolation, mutation log or snapshot boundary.

A snapshot version-4 value may temporarily produce "alice" in the new table. Replay of the version-5 update subsequently produces "alice2". In this approach, correctness comes from finishing the ordered suffix before switching authority. If live application and replay workers both update the target concurrently, they still need a common ordering or version contract; naming one worker "replay" does not resolve conflicting writes.

Conditioned installation rejects stale work at the write boundary. Ordered replay establishes a sequence from a coherent baseline. Both require progress and gap evidence under their stipulated guarantees. The database's actual transaction and capture capabilities determine which protocol can satisfy that evidence.

Handle partial dual writes without accepting divergence

Consider independent writes to old and new stores. The old write to version 6 succeeds, but the new write fails. Returning success can leave accepted business state missing from the candidate representation. Returning failure does not necessarily undo the old commit, and an application retry may repeat an already committed action. The saved commit and failure evidence must be reconciled.

If both representations can be written in one supported transaction, that contract can make their joint update atomic. If they cannot, the migration needs another explicit arrangement, such as old-authoritative writes with a durable change record and a reconciler that applies the new representation. The new side stays nonauthoritative while required effects remain unresolved.

A reconciliation worker needs an identity for each accepted mutation and a rule for applying it once or recognizing its already-applied result. An uncertain target write must be checked before repeating it. New counters or attempt IDs do not establish whether the earlier effect happened. Migration status should retain the exact unresolved source version, intended target result, and observed target state.

A second dual-write failure can occur in the reverse direction if the new write succeeds and the old write does not. Without a defined authority rule, the system cannot tell which committed value is authoritative. The protocol should avoid admitting that ambiguity, or own a deterministic recovery rule that preserves accepted data. Independent API success responses supply no joint transaction contract.

Require evidence for schema cutover

Readiness includes more than visiting every source row. The migration must establish that its snapshot or conditioned-copy coverage is complete, concurrent updates and deletes have reached the candidate state through a specified boundary, validations pass, and no unresolved divergence remains. Counts can reveal missing rows. Content and ordering checks are still needed when the counts agree.

Validation should compare the intended transformed state, row versions or other ordering evidence, and required application invariants. A transform that collapses several old names into one key may introduce collisions if the new schema requires unique keys. The migration must resolve or reject those conflicts according to its declared semantics before admitting the new schema as ready.

The schema cutover needs a writer boundary. One stipulated method briefly stops admission of old-authoritative writes, waits for already admitted transactions to finish, applies captured mutations through a recorded barrier, validates readiness, and changes the authority generation. New writes then enter through the new-authoritative route. This is a model, not an availability promise or a product command sequence.

If writes must continue across the boundary, the system needs an equivalent protocol that assigns each write unambiguously to an authority generation and prevents late old-generation commits from escaping propagation. Reader switches and writer switches must follow that protocol. Changing a client configuration while an old transaction is still in flight can leave a post-switch write accepted only by the old side.

The runtime-code-replacement guide adds an authority-boundary comparison for schema cutover. Redirecting new calls leaves old active frames and retained callbacks to reconcile, and an object's committed format needs a reader that understands it. Here an old-generation transaction similarly needs its mutation propagated before authority changes admit an incompatible history. The peer examines live program state and executable references; the database snapshot, replay and writer barrier require their own guarantees.

Test rollback after new-only writes

Before new-only writes begin, rolling back may be a controlled return to a still-current old representation, provided the authority boundary and all admitted writes are reconciled. After new-only writes, the old representation can be stale. Switching readers back would expose that stale state unless the protocol has kept it synchronized or can reconstruct compatible old data.

For the lowercase example, a new key "alice3" does not specify which display spelling the old application should receive. A policy can choose a spelling, but that is new semantics rather than recovery of lost information. If exact old-format behavior is required, preserve the necessary source information, restrict new writes during the rollback window, or provide a separately validated reverse transformation.

Rollback readiness therefore has its own evidence: which new mutations occurred, what old-compatible state they require, whether those effects were applied, and which application contracts still accept the result. A successful reverse configuration change cannot answer those questions. Irreversibility should be identified while designing the transformation, before relying on rollback as the recovery plan.

Record version conflicts and the authority barrier

Use a version-conflict worksheet to inspect the stale install on the Lenovo IdeaPad 15.6-inch FHD touch laptop. In its entry, i5-1334U identifies the processor; storage is listed as 512GB PCIe SSD and memory as 16GB DDR4. Record the worker's source version four beside current version five, then preserve the rejected condition and the newer "alice2" target. The worksheet tests the stated ordering rule. Hardware specifications supply no transaction-isolation or atomic-capture evidence.

For the authority change, a barrier table displayed with the ASUS Vivobook 14 FHD laptop can keep old-write admission, drained transactions and captured mutations in separate rows. The native product description specifies Core 5 120U with 16GB DDR4. Add the recorded replay boundary and content-validation result before changing the authority generation. The catalogue identifies the workspace, while the saved transaction and capture evidence determine readiness.