Why does an updated_at watermark miss changes in an incremental data export?

An updated_at watermark misses changes because it assumes one column can act as a complete, commit-ordered change log. That only works if timestamps are always set, strictly ordered, visible when the exporter reads, and sufficient to represent deletes. In real systems, equal timestamps, late commits, clock differences, hard deletes, and advancing the cursor too aggressively all break that contract. For best-effort exports, use a tie-breaker plus overlap and reconciliation. If you need correctness, use a durable change stream.

What assumptions must be true for an updated_at watermark to work?

A timestamp cursor is reliable only if all changed rows get a new value, that value is monotonic enough to define order, and readers observe changes in the same order the cursor advances. Those assumptions are often false.

First, timestamps are not unique. If your query is WHERE updated_at > last_seen, two rows with the same timestamp can be split across batches and one can be skipped. The minimum fix is a stable compound cursor such as (updated_at, id) with lexicographic pagination.

Second, the database does not promise that timestamp order equals commit order. PostgreSQL’s default transaction isolation documentation says a SELECT sees rows “committed before the query began” and that two successive SELECT statements can see different data. That means your exporter can advance its cursor based on one snapshot while another transaction with an older updated_at is still in flight.

How can a transaction commit behind an advanced watermark?

Worked example:

1. Transaction A starts at 10:00:00 and sets updated_at = 10:00:00 on row 42.
2. Transaction A stays open.
3. Your exporter runs at 10:00:05 and reads rows with updated_at > 09:59:59.
4. Because A is uncommitted, the exporter does not see row 42 under Read Committed.
5. The exporter sees other rows up to 10:00:05 and stores that as its new watermark.
6. Transaction A commits at 10:00:06.
7. Next export asks for updated_at > 10:00:05.
8. Row 42 is now committed, but its timestamp is 10:00:00, so it is missed forever.

This is the classic late-commit gap. A tie-breaker does not solve it because the missing row is older than the stored cursor.

When is overlap enough, and when do you need a change stream?

Overlap is enough when bounded duplication is acceptable and you can reconcile. Instead of exporting from the exact last cursor, re-read a safety window such as the last few minutes or last N ids at the boundary, then de-duplicate downstream by primary key and newest version. Add periodic full or range reconciliation to catch rare misses and hard deletes.

Hard deletes are the other major blind spot. Once a row is physically removed, an updated_at scan has nothing left to read. If consumers must learn about deletions, you need tombstones, a separate delete log, or a database change feed.

A durable change stream is the better contract when correctness matters. PostgreSQL logical decoding documentation describes logical decoding as extracting “all persistent changes” and says a slot is a stream of changes replayed “in the order they were made.” That is much closer to what incremental export systems actually need: ordered inserts, updates, and deletes tied to WAL positions rather than mutable wall-clock timestamps.

A short decision checklist:

• Use (updated_at, id) if ties are your main problem.
• Add overlap if commit timing can drift behind the cursor.
• Add tombstones or delete logs if deletions matter.
• Reconcile periodically if you can tolerate eventual repair.
• Use logical decoding or another CDC mechanism if you cannot tolerate silent loss.

Follow-up questions?

Can a stricter isolation level fix this by itself?
Not usually. Isolation affects what one read can see, but it does not turn updated_at into a durable commit-ordered sequence.

Can I use application timestamps instead of database timestamps?
Only if you fully control clock behavior and update discipline. In practice, app-assigned times usually make skew and missed updates worse, not better.

Next step: audit one export job and write down its actual cursor contract, including tie handling, delete handling, overlap window, and reconciliation plan. If you cannot state those precisely, switch the design before scaling it.

Reviewed: 2026-09-05