---
title: "Why a watermark copy misses a row that commits late | Proofline"
description: "A copy job that resumes from the highest id it has copied skips any row whose transaction commits after a higher id was copied."
url: https://proofline.sh/learn/postgres-backfill-watermark-late-commit/
---

[Learning center](/learn/)

# 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.

Breaking migrations The Proofline team November 22, 2026 4 min read

## 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](https://www.postgresql.org/docs/16/functions-sequence.html) 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](/learn/postgres-sync-trigger-old-writer/) 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](https://www.postgresql.org/docs/16/explicit-locking.html) 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](/learn/postgres-backfill-read-modify-write-race/) and in [move a counter to a new table without losing increments](/learn/postgres-counter-migration-deltas/). The touch also fires the table's other update triggers; [keep a backfill from firing the table's update triggers](/learn/postgres-backfill-side-effect-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](/learn/postgres-chunked-table-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](/learn/data-migration-across-service-versions/).

## 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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Compare two PostgreSQL tables in chunks with a checksum](/learn/postgres-chunked-table-checksum/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [Use a trigger when the old release must keep filling the new column](/learn/postgres-sync-trigger-old-writer/)
    
    Schema conflicts The Proofline team November 22, 2026 3 min read
