Add the column and fill it#
The orders table has 1,000 rows with a status column. The deploy runs migrations, then replaces the 2026.11.3 pods with 2026.11.4 pods one at a time. We ran this on PostgreSQL 16.15. The migration adds state and fills it:
-- V42__add_state.sql
BEGIN;
ALTER TABLE orders ADD COLUMN state text;
UPDATE orders SET state = upper(status);
COMMIT;
The migration committed, and the old pods kept serving. They know nothing about state:
-- 2026.11.3, after V42
INSERT INTO orders (status) VALUES ('paid'), ('paid'), ('paid');
UPDATE orders SET status = 'cancelled' WHERE id = 10;
SELECT count(*) FILTER (WHERE state IS NULL) AS no_state,
count(*) FILTER (WHERE state IS DISTINCT FROM upper(status)) AS wrong_or_missing
FROM orders;
| no_state | wrong_or_missing |
|---|---|
| 3 | 4 |
Three new orders have no state, and order 10 says PAID although it was cancelled. Nothing failed. The migration's history table records V42 as applied, so a second deploy does not repair them either. Order 10 is the costlier result: a reader of state sees a wrong value, not a missing one, so a check for nulls passes.
Put the trigger in the migration#
The fix moves the only step that has to happen at deploy time, the column and a trigger that keeps it filled, into the migration. The data step becomes a job.
-- V42__add_state.sql
BEGIN;
ALTER TABLE orders ADD COLUMN state text;
CREATE FUNCTION orders_fill_state() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP = 'INSERT' AND NEW.state IS NULL
OR TG_OP = 'UPDATE' AND NEW.status IS DISTINCT FROM OLD.status
AND NEW.state IS NOT DISTINCT FROM OLD.state THEN
NEW.state := upper(NEW.status);
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER orders_fill_state BEFORE INSERT OR UPDATE OF status ON orders
FOR EACH ROW EXECUTE FUNCTION orders_fill_state();
COMMIT;
We ran the same old-release writes against this migration, then the backfill job:
UPDATE orders SET state = upper(status) WHERE state IS NULL AND id BETWEEN 1 AND 1000;
The same count returned 0 and 0. The trigger handled the three inserts and the cancellation, and the job filled the rows that existed before V42. Because the trigger is in the same transaction as ADD COLUMN, no write can land between the two. A trigger created in a later migration leaves a gap in which an old write reaches neither.
Why the job, not the migration#
Keeping the backfill out of the migration file also keeps it out of the migration's transaction. A single UPDATE over the whole table holds a row lock on every row it touches until it commits, and the release that is still serving waits on those locks. Backfill in batches so the application never waits on row locks measures that wait. The job can also be rerun: its WHERE state IS NULL makes a second run skip finished rows.
The compatibility test#
In staging, apply the migration, then run the previous release's write paths, then run the job. Pass when state IS DISTINCT FROM upper(status) matches no row. Find every backfill mismatch with IS DISTINCT FROM shows why the comparison uses IS DISTINCT FROM. The rest of the sequence is in moving data while two application releases share one database.