The rollback that made the new column lie

Consider a column rename using expand-and-contract. entries.minutes is becoming entries.duration_minutes. You added the new column as nullable, backfilled it in batches, and ran the check that says the copy is complete. The check comes back clean: zero rows where the two columns disagree. So you move your readers onto the new column and get ready to drop the old one.

Then something unrelated goes wrong in the release, and you roll the writer back to the last known-good build. That build predates the rename. It only knows minutes. It is still a correct, shipped version of your code. And from the moment it writes, the new column your readers now trust can go quietly wrong.

This test examines the window where readers have moved forward and a writer has moved back. Claude implemented a small test on real PostgreSQL to observe what breaks, which read survives in this setup, and what the clean-up involves. Everything below is synthetic data on a throwaway database. Nothing here ran against a shared or production system, and none of it was run concurrently or at scale.

Where this came from

The repo’s release-and-rollback skill teaches expand-and-contract, also called parallel change, for exactly this rename. Its worked example proves the easy direction: after you add a nullable column, old code that inserts only minutes keeps working. The skill notes that PostgreSQL was not available when that example ran, so it used a local SQLite copy.

That example answers one question: do old writers survive the expand step? Yes. It does not answer the one that bites you later: what happens to new readers when an old writer comes back? So Claude kept the same synthetic rename and built a second test on real PostgreSQL 16.15, writing and running the SQL on a disposable cluster. The SQL, recorded transcript, and results are available with this article. Run the SQL only in a disposable PostgreSQL database: it drops and recreates a table named entries.

The pattern under test is parallel change, written up on martinfowler.com by Danilo Sato in 2014. It describes three phases, expand, migrate, and contract, and makes the expand step deliberately reversible. The question this test poses is what “reversible” costs once readers have moved on.

The schema starts after a clean backfill

The schema was frozen at the migrate phase: the column is added, nullable, and the backfill has run and been verified.

CREATE TABLE entries (
  id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label            text    NOT NULL,
  minutes          integer NOT NULL,   -- old column
  duration_minutes integer             -- new column, still nullable here
);

Three seed rows, backfilled so duration_minutes equals minutes on every one. The skill’s own trigger query confirms it:

 id | label | minutes | duration_minutes
----+-------+---------+------------------
  1 | a     |      60 |               60
  2 | b     |      30 |               30
  3 | c     |      45 |               45
drift_rows (IS DISTINCT FROM) = 0

This is the moment a team relaxes. The migration dashboard is green. Readers get moved to the new column. The old column starts to feel like dead weight. That is where the test injected the old-only writes.

The legacy writer only knows one column

A legacy writer here is the pre-rename code. It writes minutes and has never heard of duration_minutes. That is what a rollback to the last known-good build gives you, and it is also what a straggler instance that missed the deploy looks like. During the window, that writer does two ordinary things.

It updates a row that was already backfilled:

UPDATE entries SET minutes = 90 WHERE id = 1;   -- was 60 / 60

And it inserts a new row after the backfill is done:

INSERT INTO entries(label, minutes) VALUES ('w2', 120);

Neither statement is wrong on its own terms. Neither touches duration_minutes, because the code that wrote them does not know it exists. Here is the raw state afterward.

Those two rows are the whole story, and they fail in two different ways. Id 1 is a stale non-null value: duration_minutes is 60, a real number, just the old one. The update to minutes had no matching write to duration_minutes, so the new column kept its old value. Id 4 is a missing value: duration_minutes is NULL because the insert never set it. A reader that trusts the new column will mishandle each one, but not identically, and the difference matters for how you detect and repair them.

Strategy A trusted the new column, and got two rows wrong

Strategy A is the reader that has fully cut over. It reads duration_minutes and nothing else. After the two writes, here is what it returned beside the value that was authoritative in this setup.

Strategy A was wrong on two of the four rows. On id 1 it reported 60, a plausible, stale number that no type check and no NOT NULL constraint would catch. On id 4 it reported NULL, so the new row was either invisible or incomplete depending on how the reader treats a missing duration.

A tempting one-line patch is to read COALESCE(duration_minutes, minutes). Reasoning through it rather than running it, because this was not part of the recorded run: it fixes id 4, where duration_minutes is NULL, so COALESCE falls back to minutes and returns 120. It does nothing for id 1, where duration_minutes is 60, a real non-null value; COALESCE keeps that 60 and the read stays wrong. The fallback covers the missing value, not the stale non-null one, so it closes one of the two failure modes and leaves the other open.

Strategy B read the old column and stayed correct in this run

Strategy B is the compatibility read. During the window it treats minutes as the source of truth. It returned 90 for id 1 and 120 for id 4. Correct on all four rows in this run.

The reason is not luck. Every write issued during this run was a legacy write, and each one kept minutes current, because minutes is the only column it writes. So the old column stayed correct as long as all the relevant writes maintained that authority. That is the condition, and it is worth stating as a condition rather than a guarantee: this serial run had no writer that touched duration_minutes and skipped minutes, and it had no mix of old and new writers running together. A real rollback window may have both. Reading the old column is reading the thing that these writes kept maintained; whether that holds in your system depends on which writers can still run.

This is the sequencing idea the expand-and-contract table implies but does not state outright: keep reads on an authoritative representation until every permitted writer maintains the representation readers will use. Moving reads to the new column is not justified by a green backfill alone. It is justified once you can account for every writer that can still write.

The drift query hinges on three-valued logic

The skill’s trigger query is the thing that is supposed to warn you. It counts rows where the two columns disagree, and the release plan treats any non-zero result as a stop-and-page signal. During the window it counted 2, which is right: the stale row and the NULL row.

The detail that decides whether that query works is the operator. IS DISTINCT FROM counted 2. The plain inequality operator counted 1.

The reason is three-valued logic. NULL <> 120 does not evaluate to true. It evaluates to unknown, so a WHERE filter built on the plain operator drops that row. And the NULL row is exactly the one a post-rollback audit most needs to see, because a NULL is the signature of a legacy insert. PostgreSQL’s documentation of comparison functions and operators states that ordinary comparisons involving NULL yield unknown, while IS DISTINCT FROM treats NULL as a comparable value and is null-safe. In this run, that was the difference between a drift check that catches the new row and one that waves it through.

Reconciling before the writer is retired re-opened drift

Finding the drift is half the job. The other half is fixing it, and the order of operations matters.

The fix is a reconciliation: copy minutes into duration_minutes wherever they disagree.

UPDATE entries SET duration_minutes = minutes
 WHERE duration_minutes IS DISTINCT FROM minutes;

The test ran that while a legacy writer was still active. The trigger query read 0 immediately after. Clean, for an instant. Then one more legacy write landed, an update of id 2 setting minutes = 75, and the drift count went back to 1.

Then the test ran the other order. It modeled retirement by issuing no further old-column-only writes, and only then reconciled. The final trigger query read 0; no further old-only writes were issued. Strategy A and Strategy B agreed on every row: 90, 75, 45, 120.

Read that carefully. This models “the writer is retired” as “no more old-only writes occur in a serial run.” It does not prove operational retirement in a running system, sustained stability over time, behavior under concurrency, or that retiring the writer is the only way to close the window. A sync trigger or a different write path could also keep the two columns in step; those were not tested here.

The reconcile statement is the same in both sequences. One sequence contains another old-only write afterward; the final sequence does not. This demonstrates that a clean reconciliation can be invalidated by a later unsynchronized write. It does not establish that writer retirement is the only solution: maintaining both columns on every permitted write is another approach that would need its own test.

What I did not check

This is a small, deliberate test, and the shape of what it does not cover is as important as the numbers.

• No concurrency. Every write was serial on one connection. A real rollback window has many clients. Running UPDATE ... WHERE duration_minutes IS DISTINCT FROM minutes in batches against live traffic has locking and visibility behavior this run never exercised. That is a hypothesis, not a measurement.
• No mixed writers. Every write in this run kept minutes current. A real window can have old and new code writing at once, including a writer that sets duration_minutes but not minutes. The old-column-authoritative read was correct here only because that case did not occur; it was not tested.
• No generated column or sync trigger, on purpose. PostgreSQL’s generated columns are stored and computed on insert and update, and per the documentation cannot be assigned directly except to DEFAULT. That means a generated column is not a drop-in, bidirectionally writable rename, and it was not used or tested here. A sync trigger is the other standard approach. The article names these as reasoning about what could close the window, not as fixes it executed.
• Scale. Four synthetic rows. Nothing here speaks to backfill batch sizing, how long the drift query takes on a large table, or replication lag. The skill’s reference already covers batched backfill; this run did not re-measure it.
• The schema moves only one way. A nullable column was added and, in the second run, reconciled. The ALTER TABLE steps that add a nullable column, set it NOT NULL, or drop the old one are documented there, but running the drop or the NOT NULL promotion was not part of this run, and the documentation existing is not proof the migration is safe.

What one serial recorded run does support, stated plainly: with this schema and these writes, the specified writes produced the failure without any concurrent race, and the cost of relaxing at a green backfill was two wrong rows out of four.

Seven checks before you move reads to a renamed column

1. Name the writers that could still write only the old column: a rollback to the last known-good build, a straggler instance, a job on an old image.
2. Keep reads on a representation maintained by every relevant writer; do not treat a green backfill as sufficient.
3. Write the drift check with IS DISTINCT FROM, never the plain inequality operator.
4. Test that drift check against a row with a NULL in the new column, because that is the one the plain operator silently skips.
5. Treat a non-zero drift result as a stop-and-page signal, the way the release plan does.
6. Confirm no writer can still write the old column on its own, then run the final reconciliation and require an exact 0.
7. Only then move reads to the new column, and schedule the drop of the old one for a later release.

If you are planning a rename like this and want a second pair of eyes on the rollback window, join the newsletter or get in touch.

---

Previously in Human in the Loop: The row that never reached the comparison.

AI-assisted and independently reviewed.

How this was made: the predictions were written and frozen before the run. Claude implemented the SQL and ran it on a disposable, single-connection PostgreSQL 16.15 cluster with four synthetic rows; the SQL, raw transcript, and results are linked above. Local filesystem paths and trailing whitespace have been removed from the public transcript. The diagrams are drawn as SVG from the recorded results by a committed generator; there are no screenshots, because the test has no application UI to capture. The sources were independently checked on 2026-11-13.