Incremental Sync Missing Rows? Two Watermark Traps and How to Fix Them

You keep a PostgreSQL table in sync with SQL Server by reading the rows whose last_modified is above the value you saw last time. It works in testing. In production, a few rows never arrive. Switching from > to >= brings them back, but now every run re-reads rows it already copied.

There are two different problems hiding here, and >= only fixes the first one.

Trap 1: ties at the batch boundary

Say your job reads 1,000 rows per batch, ordered by last_modified. Row 1,000 has 10:00:00.123, and so do rows 1,001 to 1,040, which did not fit in the batch. The next run asks for last_modified > '10:00:00.123' and skips those 40 rows for good.

Fix: a compound watermark. Remember the last timestamp and the last key you processed, and order by both:

-- SQL Server source; @last_ts and @last_id are what the previous batch ended on
SELECT TOP (@batch) *
FROM dbo.Orders
WHERE last_modified > @last_ts
   OR (last_modified = @last_ts AND id > @last_id)
ORDER BY last_modified, id;

An index on (last_modified, id) keeps this cheap.

Alternative: >= with an idempotent write. If re-reading a handful of rows is acceptable, keep >= and make the target write an upsert, so a row seen twice is simply written twice:

-- PostgreSQL target
INSERT INTO orders (id, customer_id, total, last_modified)
VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO UPDATE
SET customer_id = EXCLUDED.customer_id,
    total = EXCLUDED.total,
    last_modified = EXCLUDED.last_modified;

Trap 2: transactions that commit late

This one survives >=. An application starts a transaction at 10:00:00, sets last_modified = '10:00:00', does more work and commits at 10:00:05. Your sync ran at 10:00:03 under READ COMMITTED: it could not see the uncommitted row, read everything up to 10:00:02 and moved the watermark there. At 10:00:05 the row becomes visible, with a timestamp that is already behind the watermark. No later run will ever pick it up.

Two common fixes:

• Read up to "now minus a safety lag". Only take rows with last_modified < now() - interval (a few minutes,

longer than your longest write transaction). Rows newer than that wait for the next run.

• Re-read an overlap window. On every run, start a few minutes before the watermark and rely on the upsert

above to make the repeats harmless.

A sturdier column: rowversion

If you can change the source table, SQL Server's rowversion is better than a timestamp written by the application. SQL Server assigns it at write time, it only goes up, and MIN_ACTIVE_ROWVERSION() returns the lowest value still inside an open transaction. Reading below that value avoids Trap 2 without guessing a lag:

SELECT *
FROM dbo.Orders
WHERE rv >= @last_rv
  AND rv < MIN_ACTIVE_ROWVERSION()
ORDER BY rv;

Many sync tools do not accept a binary column as a watermark. Converting it in the source query solves that: CAST(rv AS BIGINT) AS rv_num, and point the job at rv_num.

Two smaller traps

• Precision. SQL Server datetime is rounded to increments of about 3 ms; PostgreSQL timestamp keeps

microseconds. If you convert the watermark between the two, a comparison can drift by a few milliseconds. Keep the watermark as the source value and compare on the source side.

• Deletes. None of the above sees a deleted row. Use a soft-delete flag, or run a periodic key-by-key

comparison between source and target and remove what is gone.

Checklist

1. Compound watermark (last_modified, id), or >= plus an idempotent upsert.
2. A safety lag or an overlap window for late commits.
3. rowversion with MIN_ACTIVE_ROWVERSION() when you can add a column.
4. Watermark kept in the source's own type and precision.
5. A separate plan for deletes.

dbSlice runs this kind of incremental job on a schedule, from a Windows app or the CLI; a rowversion is used through a SELECT source with CAST(rv AS BIGINT). More in Sync SQL Server to PostgreSQL.