A user scrolls a list. Page one is fine. Page two is missing an item they never saw, and showing one they already did. Nothing has broken.
LIMIT 20 OFFSET 20 means count twenty rows, discard them, return the next twenty. Insert one row at the top between the two requests and the whole window shifts by one: item 86 appears twice, item 87 is never shown at all.
Offset carries a second problem that only surfaces later. OFFSET 100000 forces the database to count and throw away a hundred thousand rows before it returns anything.
Cursor pagination asks for everything after a specific row rather than everything after a position. It uses the sort key of the last row returned, which makes it an index seek — the same cost at page one and page five thousand — and it neither duplicates nor misses rows when the data moves underneath. The cursor needs a stable, unique order, so the timestamp gets paired with the ID to break ties.
Offset still wins for an admin table with page numbers over a small, mostly static dataset, where someone needs to jump straight to page 47.
Position is only meaningful when nothing moves. In a live system, we point at a thing rather than a place.