Keyset pagination needs the clock it was indexed on
Moving list endpoints to keyset pagination silently skipped rows at page boundaries: the JS Date cursor carried milliseconds while the column stored microseconds, so the '> cursor' comparison landed inside a same-instant cluster. Keyset is only correct when cursor precision exactly matches the stored column.
A new product in early scaffolding moved two list endpoints from offset
to keyset (cursor) pagination, ordered by created_at. It worked in
every test until rows shared a timestamp at a page boundary — then some
of them silently disappeared. The cursor was a JS Date, which carries
millisecond precision; the column stored microseconds. The > cursor
comparison landed inside a cluster of same-instant rows, and the ones
whose sub-millisecond fraction fell below the truncated cursor never
came back.
The bug lives in the gap between two clocks
Offset pagination hides this. LIMIT/OFFSET counts rows, so precision
never enters the comparison. Keyset replaces the count with an
inequality against the ordering key, and an inequality is only as
trustworthy as the values on both sides. When the application layer can
represent milliseconds and the database keeps microseconds, the cursor
you hand back is a rounded version of the row you stopped at. Feed that
rounded value into > cursor and it no longer points at a boundary —
it points into the middle of a tie group.
Ties at the edge are where correctness is decided
The failure only surfaces when multiple rows share the ordering value at
exactly the page seam. That makes it rare, non-deterministic, and
invisible to a test suite that seeds distinct timestamps. But a batch
insert, a bulk import, or any write path that stamps created_at in a
tight loop produces exactly those clusters. The general rule: a keyset
cursor must be total over the order it claims to paginate. If two rows
can tie on the key, the key alone can’t cut cleanly between pages, and
whatever precision mismatch exists becomes a silent row-dropper.
Pin the column to the precision you can actually carry
The fix was a migration switching the created_at ordering columns to
millisecond precision — timestamp(3) — so the stored value matches
what a JS Date can represent. The column now speaks the same clock as
the cursor, and > cursor becomes a total, reproducible comparison.
The principle transfers to any keyset key: don’t index on a precision
your application layer can’t reproduce. The cursor is a promise that the
next page starts exactly where this one ended, and a rounded timestamp
can’t keep it.