Learning center

Why a watermark copy misses a row that commits late

A copy job that resumes from the highest id it has copied skips any row whose transaction commits after a higher id was copied. PostgreSQL hands out sequence values when a row is inserted, not when its transaction commits. The skipped row is never copied, and every later run starts above it. A watermark on updated_at has the same gap.

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:

  1. Request A began a transaction and inserted an order. It received id 3 and did not commit yet.
  2. Request B inserted an order, received id 4 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.

  1. 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 TRIGGER takes a SHARE ROW EXCLUSIVE lock 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.
  2. 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 separate SELECT followed 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.
  3. Check presence and values at the end. The LEFT JOIN ... WHERE v.id IS NULL above 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.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading