Cursor Columns and Where the Next Incremental Read Starts

The simplest incremental extract is a query with a moving lower bound. The connector remembers the largest value it has seen in some column, then asks the source for rows above it:

SELECT id, email, updated_at
FROM accounts
WHERE updated_at > :last_seen
ORDER BY updated_at
LIMIT 1000;

After the batch, last_seen becomes the maximum updated_at in the result. Repeat on an interval. The design is appealing because it needs nothing from the source but an indexed column, and it works identically against systems that offer no log, no replication slot, and no change feed at all. It is also wrong in four distinct ways, and each way corresponds to a decision the connector has to make explicitly rather than inherit.

What the cursor actually records

The stored value is not “the rows already delivered”. It is a position in an ordering, and the mapping from position to delivered set is only as good as the ordering’s properties. For the scheme above to be correct, three conditions must hold simultaneously:

1. Every row that is inserted or modified has a cursor value strictly greater than the cursor value of every row delivered before it.
2. No two rows that the connector must distinguish share a cursor value, or ties are handled by reading past the end of the tie group.
3. The source’s ordering agrees with the values the connector compares against, including the timezone and precision of the comparison.

Condition 1 is the one that fails most often, because updated_at is usually written by application code rather than by the database. A row’s timestamp comes from whatever clock the writer had, and clocks on different hosts disagree. A writer whose clock is two seconds behind can commit a row at time $T$ while another writer has already committed at $T + 2$, and the connector’s cursor has advanced past $T + 2$. That row is now permanently behind the cursor. It will never appear in a subsequent batch, because the predicate excludes it forever.

The fix is not a better clock. It is either an overlap window — always re-reading the last $k$ seconds and accepting duplicates — or a cursor the database itself generates in commit order. The second option is what log-based capture provides, and it is the reason reading the source’s log exists as an alternative to polling. With an overlap window the connector must be prepared to receive the same row twice, which pushes the problem downstream into idempotent writes; committing offsets after the write covers the ordering side of that.

Ties: why one column cannot be advanced safely

Take 1000 rows with updated_at equal to the same microsecond and a batch limit of 1000. The connector reads all of them, advances the cursor to that microsecond, and continues. Fine. Now take 1500 rows at the same microsecond. The first batch returns 1000, the cursor advances to the shared value, and the next query asks for rows strictly greater than it. The remaining 500 rows are excluded. This is a silent partial delivery that no error surfaces: the batch succeeded, the cursor moved, the pipeline reports progress.

Changing the predicate from > to >= converts the silent loss into a permanent re-read of the entire tie group on every poll. If a busy table writes a few thousand rows per second and the timestamp resolution is one second, the tie group can exceed the batch limit, and >= will return the same first 1000 rows forever while never reaching the rest. Neither comparison is correct on its own.

The standard resolution is a composite cursor and a lexicographic predicate. The cursor is the pair (timestamp, primary key), and the query becomes:

SELECT id, email, updated_at
FROM accounts
WHERE (updated_at, id) > (:last_ts, :last_id)
ORDER BY updated_at, id
LIMIT 1000;

Now the ordering is total: the primary key breaks every tie, so a batch boundary can fall inside a tie group without losing rows. The predicate is still a strict inequality, so no row is re-read merely because it shared a timestamp with the boundary. The cost is that the source must be able to evaluate the row comparison efficiently, which means an index on (updated_at, id) in that column order; without it the predicate degenerates into a scan whose cost grows with table size.

Row-value comparison is standard SQL and most engines plan it as a range scan when a matching composite index exists. Engines that do not support the row constructor form need the expanded version, updated_at > :ts OR (updated_at = :ts AND id > :id), which some planners handle less well than the tuple form.

Timestamp resolution sets the size of tie groups before the key is added. A MySQL DATETIME without a fractional-seconds argument stores whole seconds; DATETIME(6) stores microseconds. A PostgreSQL timestamp stores microseconds. A source that stores whole seconds and receives a few thousand writes per second produces tie groups in the thousands, so a single-column cursor is not merely imprecise there; it is unusable, and the composite form is mandatory rather than an improvement.

Clocks that disagree with the cursor

Application-assigned timestamps have a second failure mode beyond skew between hosts: the value can move backwards for a single row. An update that sets updated_at = now() on a host whose clock was stepped backwards by NTP produces a value below the row’s previous value, and below the connector’s cursor. The row’s change is then invisible even though the row itself is visible. A reconciliation pass that compares row counts will not detect this, because the row exists and is counted; only a comparison of contents will, which is the subject of reconciling a load for completeness.

Monotonic clocks do not solve it either. A monotonic clock measures elapsed time since an arbitrary epoch and is not comparable across processes or across restarts, so it cannot be stored as an absolute timestamp that a later query filters on.

The database-generated alternatives are a sequence, a commit timestamp, or a log position. A sequence gives a total order only within one node; a sequence with a cache of 100 hands out blocks to concurrent sessions, so a session that took the block first can commit last, and the sequence order no longer matches commit order. A commit timestamp from a source that maintains one is close to correct but still only orders transactions the source knows about. A log position is exact with respect to commit order, at the cost of requiring log access.

The rows a cursor can never see

Deletes are invisible to any predicate over row contents, because a deleted row has no contents left to match. A polling connector on updated_at therefore cannot detect deletion at all unless the application writes a soft-delete flag, in which case the delete becomes an update and is visible. Hard deletes require either a tombstone table populated by triggers, or log-based capture, and even then the sink needs a way to represent removal, which deletes and tombstones in append-only sinks treats separately.

A second class of invisible rows comes from the isolation level of the poll. Under read-committed, each statement sees a fresh snapshot, so a batch can straddle a transaction boundary and deliver a row whose later modification lands in a batch the connector has already passed. Under repeatable-read or a snapshot isolation level, a long-running batch sees one consistent point in time, but a transaction that commits after that snapshot and before the cursor advances can still produce a row below the new cursor. The overlap window mitigates both, at the cost of duplicates.

The third class is rows the source never stamps. Tables with no updated_at column, or where a bulk load bypasses the application and does not set the column, contain rows that will never satisfy the predicate. A bulk load that leaves updated_at at its default is a common way to introduce a gap that looks like a connector bug and is really a write-path bug.

Choosing between a cursor and a log

A cursor column is the only option when the source exposes nothing else: a third-party API with a modified_since parameter, a reporting replica the connector may not replicate from, a table maintained by an application that cannot be changed. Its costs are the overlap window, the composite key, the index the source must carry, and the absence of deletes. Its benefit is that it needs no privileges beyond a read-only account and no retention guarantee from the source.

A log gives commit order, deletes, and no dependence on an application-maintained column. It costs a replication privilege, a retention window the connector must not fall behind, and a snapshot phase to establish a starting position, which is where the snapshot-to-stream handoff becomes the hard part.

The decision is not permanent. A connector that starts on a cursor and later moves to a log must translate its old position into a new one, and the safe way to do that is to stop the cursor, record its value, start the log from a position known to precede it, and let the duplicates land on an idempotent sink.