Expand-contract migrations: compatible SQL can still return stale data

Test the data as well as the schema

Adding a replacement column can keep old and new application versions running at the same time. It can also leave them reading different values. Our SQLite example passes every SQL compatibility check against an expanded schema, yet a legacy write after backfill leaves the replacement column stale.

The example changes display_name to public_name without changing the meaning of the value. It uses three schema fixtures and three small client implementations. Each of the nine compatibility cells runs a read, an update and another read. Separate write-order cases inspect both columns, rather than accepting the absence of a SQL error as success.

Here, the legacy client reads and writes display_name. The bridge client still reads that column but writes both names in one update. The new client reads and writes only public_name. Expanded fixtures start with both values set to Ana so that the checks isolate compatibility and subsequent writes, not initial backfill completeness.

These are independent, single-connection fixtures. The matrix does not run a rolling deployment or execute a production schema migration. A passing cell proves only that this client's three operations completed and returned the expected value within its own fixture.

Expand the interface before moving its users

Danilo Sato's explanation of Parallel Change describes three phases: expand the interface, migrate its consumers, then remove the old interface. Applied to this column change, the expanded schema creates a period in which both names exist while application versions move through the transition.

The matrix shows why release order matters. Deploying the bridge client before the replacement column exists fails. Removing the old column while that client still runs also fails, even though it already writes the new column. Its read path and dual-write statement still depend on the legacy name.

List the consumers before choosing the transition window. Web processes may update quickly while scheduled jobs, queue workers or a reporting integration remain on an older version. Include commands run by operators and scripts that write directly to the database. A successful web rollout is weak evidence that every old writer has stopped.

This example is a rename with the same value semantics. A change in units, validation rules or meaning needs a defined transformation and conflict policy. Copying values between fields is insufficient when the fields represent different facts.

A completed backfill can become stale again

Start with both names equal to Ana. A backfill copies display_name into public_name. The legacy client then changes only display_name to Bea. Every statement succeeds, but a new client still reads Ana from the replacement column.

Backfilling only rows where the replacement column is null would not repair the first case: public_name contains a non-null, outdated value. A fallback read that prefers the replacement whenever it is non-null has the same problem. The system needs an authoritative source and a way to establish that the copy is current.

For the rollout described here, keep display_name authoritative while legacy writers can still run. The bridge reads that source and writes both fields atomically. After legacy writers have been drained, perform the final reconciliation from the authoritative field and verify equality before switching reads. The two sequential orders in the table demonstrate the dependency; they do not test races between concurrent processes.

Keep write atomicity separate from source authority

The bridge's successful update changes both fields in one statement. The failure case deliberately takes a different path: it updates the old field inside a transaction, raises an exception before touching the second field and rolls the transaction back through the Python connection context. Both values remain Ana.

That result establishes the behavior of the tested transaction boundary. It does not make an old-only write update the new field, and it does not define which field should win after conflicting writes. Atomicity protects a grouped operation; authority determines which value subsequent operations should trust.

SQLite's transaction documentation distinguishes explicit transaction control from automatically started transactions and documents error cases with different rollback behavior. The example handles the injected application failure through its transaction wrapper. Do not generalize that result into a claim that every database error automatically reverses every previous statement.

The same distinction appears in the API idempotency experiment: the useful unit of protection is a precisely defined operation. An application release, a background repair job and a schema change are different operations with different failure boundaries.

Move the authority switch through explicit gates

A workable sequence for this same-meaning rename is:

1. Add the replacement column while keeping the legacy schema contract available.
2. Deploy a bridge that reads the legacy field and atomically writes both fields.
3. Drain legacy writers, reconcile the replacement from the authoritative field and verify the result.
4. Switch reads to the replacement while retaining dual writes for the chosen rollback window.
5. Retire dependencies on the legacy field, deploy a new-only client and remove the old column when rollback policy permits.

The read-new, dual-write variant in step 4 is a proposed additional release stage. It is not a fourth client hidden inside the nine-cell model. Add it to your compatibility tests if your rollout uses it, including the rollback target it must support.

Define how you will prove that a gate is closed. Inventory running versions and scheduled execution paths. Track mismatched or missing replacement values, using the application's intended null semantics. Sample meaningful business reads in addition to counting rows. A zero mismatch count from an earlier run is insufficient if an untracked old writer can change the source afterward.

For large datasets, make reconciliation resumable and bound its work per batch. Test what happens when it restarts after a partial batch or overlaps an application update. A database-side update from the current authoritative column can avoid copying an already stale application-side snapshot, but its locking and concurrency behavior still need validation on the real engine. The supplied fixture does not implement a batched backfill.

Define where application rollback stops being safe

Keeping both columns is not enough to preserve rollback. In the fourth write case, the new-only client changes public_name to Bea while display_name remains Ana. Rolling application code back to a legacy reader would expose that older value even before the column is dropped.

Treat ending dual writes and removing the old column as explicit changes to the rollback contract. Record the oldest application release still supported by the current data state. Retaining an old container image does not establish that its queries remain valid or that the fields it reads are current.

The broader PaaS migration decision uses the same constraint at infrastructure scope: moving traffic back is useful only when the receiving environment can serve authoritative data. A database transition needs that reasoning at the field and writer level.

Schema compatibility does not remove database locks

An expanded interface addresses application compatibility. It does not establish that the DDL used to create it can run without blocking traffic. PostgreSQL's ALTER TABLE documentation states that an ACCESS EXCLUSIVE lock is acquired unless a subform explicitly specifies otherwise; different operations have different requirements.

Review the exact statements against the engine and version you operate. Test lock acquisition with representative concurrent work, inspect long transactions and define what to do when the operation cannot acquire its lock within the deployment's allowed window. Measure the actual operation instead of treating the phrase "expand and contract" as a latency guarantee.

Also inspect dependencies outside ordinary application queries. Views, constraints and data export consumers can retain the old contract after the main service has moved. The local fixtures intentionally omit those dependencies and make no claim about table-rewrite cost, replication lag or production availability.

Reproduce the boundary cases before adapting the procedure

Download the SQLite compatibility model, save it as experiment.py and run it with Python 3. It uses only the standard library. The saved results contain all nine compatibility cells, their SQL error details and the final values from five write-order cases.

The script asserts the expected result of every cell and case, then repeats the run and requires identical output. Each fixture is created in memory and discarded. No application deployment, live DDL transition, concurrent writer, multi-row backfill, process crash or external database is exercised.

Use the example to build a more specific release test: name every supported application version, its read source and its write targets. Assert the final business value after the operations that can overlap during your rollout. Then test the real migration statements and operational gates separately. The release decision should rest on both query compatibility and data that remains correct as ownership moves.

Sources

Documentation checked September 5, 2026.

1. Danilo Sato: Parallel Change
2. SQLite: transaction control
3. PostgreSQL: ALTER TABLE