Learning center

Compute the backfilled value in SQL, not in the job

A backfill job that reads rows, computes the new value in application code and writes it back can overwrite a change the running release made in between. In our lab an order cancelled by the old release was written back as PAID, and a sync trigger on the table did not prevent it. Computing the value inside the UPDATE lets PostgreSQL recompute it from the row's newest version.

The race#

The orders table has status (written by release 2026.11.3) and a new state that the job fills with upper(status). We ran this on PostgreSQL 16.15 with psycopg 3.2. The job reads a batch, then writes each row:

rows = job.execute("SELECT id, status FROM orders WHERE state IS NULL ORDER BY id").fetchall()
# meanwhile, 2026.11.3 runs: UPDATE orders SET status = 'cancelled' WHERE id = 1
for order_id, status in rows:
    job.execute("UPDATE orders SET state = %s WHERE id = %s", (status.upper(), order_id))
idstatusstate
1cancelledPAID
2paidPAID

The job wrote what it had read, and the read was stale by the time of the write. Nothing in the second statement refers to the value it is replacing, so PostgreSQL has no reason to object.

We repeated the run with a trigger that sets state whenever status changes. The old release's update fired it, and the row briefly read cancelled, CANCELLED. Then the job's write arrived and set state back to PAID. The trigger fires on writes to status; the job writes only state.

Compute it in the statement#

UPDATE orders SET state = upper(status) WHERE state IS NULL;

We started a transaction that cancelled order 1 and held it open for half a second while this statement ran. The UPDATE waited 502 ms for the row lock, then produced:

idstatusstate
1cancelledCANCELLED
2paidPAID

Under READ COMMITTED, an UPDATE that finds its target row locked waits for the other transaction. If that transaction commits, PostgreSQL re-reads the updated version of the row, checks the WHERE clause against it and computes the SET expressions from it. The transaction isolation chapter describes this behavior. The value comes from the row as it is when the write happens.

Under REPEATABLE READ the same statement fails instead, with SQLSTATE 40001, could not serialize access due to concurrent update. That is also correct, as long as the job retries the batch.

When the value cannot be computed in SQL#

Some conversions need application code: a call to another service or a parser the database lacks. Make the write conditional on the value you read:

UPDATE orders SET state = $1 WHERE id = $2 AND status = $3;

When we cancelled order 1 between the read and the write, this statement updated no row for it, and the job put the id on a retry list. Order 1 kept a null state until the retry, which a reconciliation count reports. A wrong value would look complete. Use IS NOT DISTINCT FROM in place of = if the old column can be null.

The test#

Run the job's batch in a test while a second connection changes the old column of a row in the batch between the job's read and its write. Pass when the row ends with the new value computed from the change, or stays unfilled and is retried. Find every backfill mismatch with IS DISTINCT FROM is the count to run afterwards. More backfill labs are 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