Give both app versions the same order#
An orders API is renaming status to state. A worker running the previous release reads status to decide whether an order still needs shipping. The replacement reads state. To test rollback, have the replacement ship an order, then ask the old worker whether it has shipped.
Run the SQL blocks in order in one psql session. We ran them on PostgreSQL 17.10. The temporary table lasts for that connection. The two prepared queries stand in for the reads in the two application releases. When adapting this lab to a service, exercise its actual previous and candidate binaries against one disposable database.
CREATE TEMP TABLE orders (
id integer PRIMARY KEY,
status text NOT NULL
);
INSERT INTO pg_temp.orders VALUES (1, 'packed');
ALTER TABLE pg_temp.orders ADD COLUMN state text;
UPDATE pg_temp.orders SET state = status;
PREPARE old_reader(integer) AS
SELECT status FROM pg_temp.orders WHERE id = $1;
PREPARE new_reader(integer) AS
SELECT state FROM pg_temp.orders WHERE id = $1;
Run the old reader after a new write#
The first release of the new app still updates both columns in one statement. It can read the new column while the old worker processes orders from the same table.
UPDATE pg_temp.orders
SET status = 'shipped', state = 'shipped'
WHERE id = 1;
EXECUTE new_reader(1);
EXECUTE old_reader(1);
Both return shipped. This is the positive control. If it fails with your real binaries, fix the compatibility problem before testing a later migration step. Include an old writer in the exercise if one will still be serving traffic. An old writer that updates only status can also leave the new reader behind.
Find the first write that breaks rollback#
In the next release, the app starts writing only state. The migration relaxes the old column's constraint so a new order can be inserted without providing status. The old column still exists when the new version records a delivery and creates another order.
ALTER TABLE pg_temp.orders ALTER COLUMN status DROP NOT NULL;
UPDATE pg_temp.orders SET state = 'delivered' WHERE id = 1;
INSERT INTO pg_temp.orders (id, state) VALUES (2, 'packed');
SELECT id, status AS old_app_reads, state AS new_app_reads
FROM pg_temp.orders ORDER BY id;
| Order | Old app reads | New app reads |
|---|---|---|
| 1 | shipped | delivered |
| 2 | NULL | packed |
Rolling back the app leaves the database where it is. For order 1 the worker reads a plausible value that is already obsolete. For order 2 it loses the status entirely. Both queries still execute. A smoke test that checks only for a successful response would miss both failures.
Put the rollback decision in the pull request#
For this release, the rollback boundary is the first write that updates only state. Dropping status later makes the incompatibility obvious, but the data diverged earlier. If the team still needs the old release as a rollback target, keep both columns current through that window. Otherwise name the earliest compatible release in the rollout instructions.
For a real migration, repeat the test with a row created before deployment, a row created during it, and an update from each writer that can be live. Record the value each version actually read and which worker or request path produced it. The migration review checklist covers the surrounding release sequence, including backfill, reconciliation and cleanup.