With OFFSET paging over a live feed, why do users see duplicated rows?

## Why positions are not stable A paginated listing is not one query — it is a sequence of independent statements, one per user request, each evaluated against whatever the table contains at that instant. There is no continuous result set spanning the requests. `OFFSET` therefore counts positions in a set that is recomputed from scratch every time, over data that other users are changing in between. ## Insertions cause duplication Order a feed newest-first and page 10 at a time. The user fetches page 1 (`OFFSET 0`) and reads rows A..J. While they read, three new posts arrive. Those posts sort ahead of everything, so every previously seen row moves down three positions: A now sits at position 4, J at position 13. The user requests page 2 (`LIMIT 10 OFFSET 10`), which now returns positions 11–20 — that is rows H, I, J (already read) followed by K..Q. Three rows are shown twice. The rule generalises: any row inserted at a position ahead of your current offset causes exactly one already-seen row to reappear. ## Deletions cause silent skipping The mirror image is worse because nobody notices. If three rows ahead of the offset are deleted between requests, everything shifts up three positions, and positions 11–20 now begin three rows further along than the user's reading position. Rows K, L, M are never displayed to anyone. A duplicate is a visible annoyance; a skipped row is a support ticket months later, or a data-export job that quietly missed records. ## Sort-key mutation causes both at once Ordering by something that changes — a score, a `last_activity_at`, a priority — reshuffles rows between requests without any insert or delete. A row can move from position 30 to position 5 and be seen twice, or from 5 to 30 and be missed. This affects offset and keyset paging alike and is the one case keyset does not fix. ## Why keyset is stable Keyset pagination asks "what comes after this value?", and the answer does not depend on how many rows exist before the anchor: ```sql SELECT id, created_at, title FROM posts WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 10; ``` Three posts arriving mid-traversal sort ahead of the anchor, so they are outside the predicate entirely and change nothing about the next page. The user continues exactly where they stopped and sees the new posts when they return to the top — the behaviour every feed UI already implies. Deletions behind the anchor are equally harmless: the remaining rows keep their values, so the boundary still means the same thing. One genuine subtlety: if the anchor row itself is deleted between requests, keyset still works. The predicate compares values, not identity, so it selects everything sorting after those values whether or not a row with them still exists. That is a nice property — a server-side cursor pinned to a row id would break. ## What keyset does not fix - **Mutating sort keys.** Order by a value that changes and rows still move across the boundary. If stability matters, sort by something immutable (creation time plus id) and display the volatile value as data rather than sorting by it. - **Rows changing so they no longer match the filter.** A row that stops satisfying the `WHERE` clause disappears from the traversal, keyset or not. - **Rows inserted behind the anchor.** A back-dated insert lands in a region the user has already passed and will not be shown. That is inherent to any incremental traversal; only re-reading from the start surfaces it. ## How to talk about it in an interview Frame it as a correctness problem, not only a performance one. Deep `OFFSET` is slow; *any* `OFFSET` over changing data is unstable, including shallow pages. That is why the recommendation to move a feed to keyset pagination stands even when the table is small enough that the cost argument does not apply. When the product truly needs numbered pages over volatile data, be explicit that the pages are a best-effort view of a moving target, and keep loops that must see every row — exports, reconciliation, migrations — on keyset traversal, where completeness is guaranteed.