The skipped row#
The job copies orders into orders_v2, an upsert per row, and stores the largest id it has seen. We ran this on PostgreSQL 16.15 with psycopg 3.2.
SELECT id, status FROM orders WHERE id > $watermark ORDER BY id;
-- for each row: INSERT INTO orders_v2 ... ON CONFLICT (id) DO UPDATE ...
-- then: watermark := largest id returned
The first run copied rows 1 and 2 and stored a watermark of 2. Then two requests from the running release arrived:
- Request A began a transaction and inserted an order. It received
id3 and did not commit yet. - Request B inserted an order, received
id4 and committed.
The second run read id > 2. Row 3 was invisible to it because its transaction had not committed, so the run copied row 4 and stored a watermark of 4. Request A then committed. The third run read id > 4 and found nothing.
SELECT o.id FROM orders o LEFT JOIN orders_v2 v USING (id) WHERE v.id IS NULL;
-- 3
The sequence documentation notes that nextval is never rolled back and that values may be used out of order by concurrent transactions. An id order is an allocation order, not a commit order.
The timestamp watermark has the same gap#
now() returns the start time of the current transaction. We updated order 10 inside a transaction, waited one second, read now() from another session as the job's mark, then committed. The row's updated_at was earlier than the mark, so a job that reads updated_at > mark on its next run never sees that change. clock_timestamp() narrows the gap but does not close it, because the row is still invisible until its commit.
What closes it#
Copy rows the running release writes with a trigger, and copy the rows that existed before the trigger with an update of the source rows that fires it. Then no step reads a value in one statement and writes it in another.
- Create the trigger first. Use a trigger when the old release must keep filling the new column shows one for a column; for a new table, the trigger upserts the row into
orders_v2.CREATE TRIGGERtakes aSHARE ROW EXCLUSIVElock on the table, which waits for every open transaction that has written to it, as the explicit locking chapter lists. A row like order 3 is therefore either committed before the trigger exists or written after it, when the trigger copies it. - Copy existing rows by touching them. Walk the table in key ranges with
UPDATE orders SET status = status WHERE id > $1 AND id <= $1 + 1000. Each row is locked and re-read as it is updated, and the trigger copies the current value in the same transaction. A separateSELECTfollowed by an upsert can write a value that a concurrent update has already replaced and synced, which is the race in compute the backfilled value in SQL, not in the job and in move a counter to a new table without losing increments. The touch also fires the table's other update triggers; keep a backfill from firing the table's update triggers covers that. - Check presence and values at the end. The
LEFT JOIN ... WHERE v.id IS NULLabove found row 3 directly. Run it after the copy, together with a value comparison such as compare two tables in chunks with a checksum, since a row can be present and stale.
We replayed the lab this way. With request A's insert of order 3 still open, CREATE TRIGGER waited 503 ms, until A committed. After one more insert and the touch update, the anti-join returned no row and all four orders read PAID in orders_v2.
If you keep a watermark copy instead, re-reading an overlap of earlier rows does not make it correct: the job still writes values it read earlier, and no setting bounds how long a transaction stays open. statement_timeout limits each statement and idle_in_transaction_session_timeout each idle gap, and a transaction of many short statements can outlive both.
The rollback limit#
Until both checks pass, the new table is missing or misstating writes, and a reader switched to it shows orders that are absent or out of date. Keep readers on the old table until they do. Moving the reader back is then a configuration change, because the old table was never stopped. The rest of the sequence is in moving data while two application releases share one database.