Schema Changes Are Multi-Version Protocols

A column rename looks atomic in a schema diff. A deployed system rarely experiences it that way.

During a rolling release, old application processes can remain active after new processes start. Background jobs may run code built from another release. Replicas can lag behind a primary. Queued work can outlive the binary that created it. Data written before the change remains present after the new schema exists. The migration therefore crosses several versions of code and data at once.

This makes a schema change closer to a protocol transition than a file edit. The relevant question is not only whether the final schema is valid. Each intermediate combination of reader, writer, and stored representation also needs a defined meaning.

The compatibility matrix exists even when nobody writes it down

Consider an application that stores a customer state in status and intends to rename the column to account_status. The desired endpoint is simple:

before: customers.status
after:  customers.account_status

A direct rename can be correct when application deployment is strictly stopped, the migration runs while no incompatible process can access the database, and all code restarts against the new schema. Those conditions are stronger than a rolling deployment provides.

With overlapping application versions, at least four combinations matter:

The transitional schema is not incidental. It is the shared protocol surface that permits old and new code to coexist.

For a rename, that surface can contain both columns for a period. The difficult part is not adding the second name. It is defining which writes populate which representation, how reads choose between them, and when evidence is sufficient to remove the first representation.

Additive changes create room for overlap

Adding a nullable column generally changes fewer assumptions than immediately removing or renaming an existing column. Existing statements that name their columns explicitly can continue to operate, while newer code gains a place to write the new representation.

That property makes additive changes useful as compatibility scaffolding. It does not make every ADD COLUMN operation operationally free. Database engines differ in locking, table rewrite behavior, default handling, and online DDL capabilities. The protocol argument is separate from those engine-specific mechanics.

Suppose the transitional schema contains both names:

ALTER TABLE customers
ADD COLUMN account_status text;

At this point, old code still reads and writes status. New code cannot treat account_status as authoritative until a consistency mechanism exists. There are several possible mechanisms, each with a different boundary.

Application dual writes can populate both columns for new mutations. A database trigger can centralize mirroring inside the database. A later application version can write only the new column after old writers have disappeared. None of these choices is universally superior; the important property is that the transition defines how concurrent versions preserve the intended value.

Dual writes inside one database transaction can make the two column updates atomic with respect to that transaction. Two independent statements without a shared transaction do not have that property.

Backfill repairs history, not concurrent writes

Once new writes maintain both representations, older rows still have NULL in the new column. A backfill can copy historical values:

UPDATE customers
SET account_status = status
WHERE account_status IS NULL;

The statement is concise, but its interaction with live writes depends on the database isolation model, query plan, lock behavior, and application write pattern. Large tables often require bounded batches for operational reasons, but batching introduces another concern: a backfill is now a long-running participant in the transition.

A safe backfill predicate should distinguish rows that still need conversion from rows already handled by current traffic. If a current writer can legitimately set the new field while the backfill is running, an unconditional assignment can replace a newer value with an older one.

This is a general migration property. Historical conversion and current mutation are separate streams. The migration needs an ordering or predicate that prevents historical work from clobbering current state.

The same issue appears in representation changes. Suppose an integer amount moves from cents to a structured monetary representation. Converting every row once is not enough if old writers can continue producing the former representation after the converter has passed that row. Compatibility requires control over ongoing writes, not only a complete scan of existing records.

Read paths expose the semantic cutover

During the overlap period, a reader can prefer the new representation and fall back to the old one:

value = account_status if account_status is present else status

That expression is useful only if absence has the intended meaning. If NULL is a valid domain value, it cannot also serve unambiguously as a migration marker. A separate migration state, version field, or other explicit discriminator may be required.

Read fallback creates a useful asymmetry. New code can consume rows from before and after the backfill while old code continues using the original field. Once the backfill is complete and all active writers populate the new representation, the fallback path can become unnecessary.

The cutover is semantic rather than merely temporal. A date on a deployment calendar does not prove that no old writer remains. Long-lived workers, delayed jobs, rollback procedures, and independently deployed services can extend the compatibility interval.

Evidence for removal therefore comes from the system boundary: deployed version inventory, query telemetry, application metrics, or database observations that can establish that the old representation is no longer read or written under the stated operating model.

Constraints belong after the data satisfies them

A target schema may require account_status to be non-null. Adding that constraint before old rows are converted would reject the existing state. Adding it while old writers can still omit the new column would reject otherwise valid old-version writes.

The constraint becomes compatible only after two conditions hold: existing rows satisfy it, and every writer that can still reach the table also satisfies it.

This ordering separates data convergence from invariant enforcement. First, the system reaches a state in which the invariant is already true. Then the database is asked to reject future states that violate it.

Some database engines provide mechanisms to validate constraints separately from their initial declaration or to reduce locking during validation. Those details are engine-specific. The broader protocol remains the same: enforcement cannot safely precede the behavior it requires unless incompatible writers are already excluded.

Rollback changes the direction of compatibility

A migration plan that supports forward deployment can still make rollback unsafe.

If new code starts writing a representation that old code cannot interpret, switching application binaries back does not restore the previous data semantics. The database now contains states produced by the newer version. A rollback is therefore another version transition, not a reversal of time.

This matters for destructive conversions. Dropping a column, narrowing a type, collapsing several states into one, or rewriting values into a form old code cannot parse can remove information required by the previous release.

A compatibility window can preserve rollback options by delaying destructive operations. Old structures remain present until the new release has operated long enough that returning to the old release is no longer part of the supported transition. The exact duration is an operational policy, not a database constant.

Cleanup is a protocol phase

Removing the old column is often presented as housekeeping:

ALTER TABLE customers
DROP COLUMN status;

In a multi-version system, that statement closes a compatibility path. It is safe only after no supported reader, writer, rollback target, job payload, stored procedure, or other database client depends on the old name.

This is also the point at which temporary migration machinery can disappear. Dual-write code, fallback reads, triggers, conversion markers, and compatibility metrics have value during overlap but add ambiguity after the transition is complete. Keeping them indefinitely can leave two apparent sources of truth.

The final schema is simpler precisely because the system spent time in a more complex intermediate state.

A schema diff is only the endpoint

Schema tools naturally display migrations as ordered DDL statements. That representation is necessary, but it omits the application versions and stored states that coexist between those statements.

Treating the change as a multi-version protocol makes those hidden states explicit. Additive structure opens a compatibility interval. Writers establish a new representation without abandoning the old one. Historical data converges while current mutations remain protected. Readers cross the semantic boundary. Constraints encode an invariant only after participating writers already obey it. Destructive cleanup closes the interval.

The lasting engineering object is not the migration script alone. It is the set of version combinations the system permits while moving from one stable representation to another.