CDC change ordering: __pos is not a total order — add __seq

Shipped in 0.17.0 — this page is the design rationale, not a proposal. __seq is a live CDC output column; the current reference is reference/cdc.md § Output shape. This page records why (__pos, __seq) is the total change order. The per-engine population below covers the three SQL engines; MongoDB (added later) gives every change-stream event a distinct __pos, so its __seq is always 0.

Problem (verified live on all three engines)

The CDC output columns are __op, __pos, and the after-image. __pos is the commit position of the change’s transaction:

Commit position is exactly right for resume/checkpoint (all engines resume at a commit boundary). But it is not a total order over changes: every change in one transaction shares it. Proven — 8000 UPDATEs of one PK in a single transaction produced COUNT(DISTINCT __pos) = 1 on all three engines.

Downstream, the current-state dedup view

ROW_NUMBER() OVER (PARTITION BY <pk> ORDER BY <parsed __pos> DESC) = 1

has 8000 tied rows, so ROW_NUMBER picks an arbitrary one. Live result: the view returned counter = 1 for a row whose committed value was 8000 — silently wrong current state. This bites any transaction that touches the same PK more than once (triggers, ORMs, read-modify-write loops, batch upserts). The row order inside the Parquet part is the change order, but that order is lost the moment the log is loaded into an (unordered) warehouse table.

Design: emit a per-change __seq (intra-transaction ordinal)

Add a __seq column to the CDC output: the change’s ordinal within its commit group. Keep __pos unchanged (still the commit position, still what resume uses). The pair (__pos, __seq) is then a total order that:

• matches log order (commit order across transactions, emission order within),
• is deterministic and log-derived, so a re-emitted change (at-least-once, crash-before-ack) carries the same (__pos, __seq) — the dedup tiebreak is a true tie between identical rows, so either wins and the value is right,
• survives the load (it is a column, not row order).

Dedup view becomes:

ROW_NUMBER() OVER (PARTITION BY <pk> ORDER BY <parsed __pos> DESC, __seq DESC) = 1

Per-engine population

__seq is a 0-based counter over the changes of one commit, in log order:

• SQL Server — use the native __$seqval from the change table (it already orders operations within __$start_lsn). __seq = dense_rank of __$seqval within __$start_lsn (or __$seqval rendered as a comparable fixed-width value). No invention — the engine hands us the order.
• PostgreSQL — logical decoding yields the transaction’s changes in order; assign 0,1,2,…, resetting when __pos (commit LSN) advances.
• MySQL — binlog row events arrive in order within the transaction; assign 0,1,2,…, resetting at each commit __pos.

Because the reset key is __pos (the commit position), the ordinal is reproducible from the log alone on every run — the at-least-once property above holds without any persisted counter.

Why not alternatives

• A single global run counter (0,1,2,… over the whole run) breaks across runs: run 2 resets to 0, so a newer change gets a smaller counter than an older one from run 1. (__pos, __seq) avoids this by resetting per commit, keeping the ordinal log-derived.
• Folding __seq into __pos (making __pos distinct per change) would break resume, which must stop on a commit boundary, not mid-transaction.
• Relying on Parquet row order — lost on load into a warehouse table.

Blast radius

• CDC sink schema gains one column (__seq INT64), all engines.
• One capture-agnostic path populates it — the shared TxnSeq per-commit ordinal, stamped as the stream is consumed on every engine (SQL Server’s __$seqval only orders the change-table read; MySQL/Postgres from a per-commit ordinal).
• validate.rs can additionally assert (__pos, __seq) is strictly increasing in part→row order (today it only checks __pos non-decreasing).
• The rivet-pro dedup view template orders by (__pos, __seq).
• Regression test per engine: N changes to one PK in one transaction → the dedup view returns the last change’s value, not an arbitrary one.