skip to content

Users paging with LIMIT and OFFSET see the same row on two pages — why?

level: seniorimportance: should knowfreq 50%

answer

  1. the offset is a position, not a bookmark
  2. the result is recomputed every request
  3. writes ahead of the boundary shift rows
  4. inserts duplicate, deletes silently skip

basics

~20 s

OFFSET counts positions in a result recomputed from scratch for every request. Rows inserted ahead of the offset between requests push everything later, so a row already shown reappears on the next page; deletions shift rows earlier and hide them entirely.

solid answer

~50 s

An offset is a **position**, not a bookmark. Each page request re-runs the whole query and counts `n` rows into a freshly computed result, so it silently assumes nothing before position `n` changed since the previous request. On a busy table that assumption fails: five rows inserted ahead of the current offset push five already-seen rows past the boundary, and the user sees them again on the next page; five rows deleted ahead of it pull five unseen rows above the boundary, and they are skipped for good. Updates that change the sort key cause both. A non-total ordering makes it worse by letting tied rows reshuffle between requests. The fixes, in order: make the ordering total with a unique tie-breaker; then page by **anchor** — remember the last row's key and ask for rows beyond it (`WHERE id < :last_seen_id ORDER BY id DESC`), which is immune to churn ahead of the cursor. Where an anchor is impossible, snapshot the result set or accept the drift explicitly.

code

sql · 12 lines
sql
-- Drifts: position-based, recomputed per request
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 20;

-- Stable: anchored on the last row the client actually saw
SELECT id, title, created_at
FROM articles
WHERE id < :last_seen_id
ORDER BY id DESC
LIMIT 20;

go deeper

for a junior

Understand that each page request re-runs the query from scratch, so rows added or removed in the meantime move the page boundary. The database is not remembering your last page.

for a middle

Explain both directions concretely — inserts ahead of the offset cause repeats, deletes ahead of it cause silent skips — and name a total ordering as the prerequisite for any stable paging scheme.

for a senior

Diagnose the load-dependent bug report from symptoms, rule out the non-unique ORDER BY first, and propose an anchored page predicate while stating plainly what random page access you give up.

for a principal

Decide the paging contract for the platform: which lists get stable anchored iteration, which may drift and say so, and how batch jobs iterate large tables without silently skipping records under concurrent writes.

## What OFFSET actually promises `OFFSET n` means: compute the result, order it, discard the first `n` rows. Every word of that happens afresh on each execution. Nothing links one request to the next — the database holds no memory of what page 1 returned, and `OFFSET 20` does not mean "after the rows I gave you last time", it means "after whatever the first twenty rows are *now*". That is exactly the assumption a paging UI breaks. Between the user seeing page 1 and clicking "next", other sessions have been writing. ## The two failure shapes Take a newest-first list, `ORDER BY created_at DESC, id DESC`, 20 rows per page. **Insertions ahead of the offset → duplicates.** The user fetches page 1 (`OFFSET 0`) and sees rows A1…A20. Five new articles are published; because the sort is newest-first, they occupy positions 1–5. The user clicks next and asks for `OFFSET 20`, which now lands five rows earlier in the shifted sequence — so A16…A20 come back a second time. The user sees five repeats and, worse, believes they have reached new content. **Deletions ahead of the offset → skipped rows.** The mirror image. Five articles above the boundary are deleted, everything shifts up five positions, and `OFFSET 20` now begins five rows further along than the user's last row. Those five rows are never displayed on any page. This one is the dangerous variant: nothing on screen looks wrong, so the loss is silent. If the pager drives a batch job — "process every row, page by page" — the skipped rows are simply never processed. **Updates that change the sort key do both**, because an update to `created_at` is a delete-and-reinsert as far as the ordering is concerned. ## The compounding problem: ties If the ordering is not total — say `ORDER BY created_at DESC` alone, with many rows sharing a timestamp — then even with *no* concurrent writes, two executions may order the tied block differently. The page boundary can fall in a different place inside the block each time, producing duplicates and gaps with no data change at all. Always fix this first: append a unique column so no two rows compare equal. It is a one-line change and it removes an entire class of report you would otherwise chase as a concurrency bug. ## Anchored (keyset) paging The structural fix is to stop describing the page by position and start describing it by **content**. The client remembers the sort-key value of the last row it received and sends it back; the next page is defined by a predicate rather than a count: ```sql SELECT id, title FROM articles WHERE id < :last_seen_id ORDER BY id DESC LIMIT 20; ``` Because the boundary is a value in the data rather than a count of rows, churn elsewhere in the table cannot move it. Rows inserted above the anchor simply are not in this page's range; rows deleted above it change nothing about where this page starts. The anchor must be part of the ordering and the ordering must be total, otherwise the predicate cannot uniquely identify "after that row". What you give up is worth naming honestly: no jumping to an arbitrary page number, no "page 7 of 40" affordance, and going backwards needs the reversed predicate and ordering. For an infinite-scroll feed or a paged batch job that is a fine trade; for a UI that genuinely needs random page access it is not. ## Other options when an anchor does not fit - **Freeze the candidate set.** Materialise the matching identifiers once — into a temporary table, a cached list, or a search index result — and page over that fixed list. The page contents are then stable by construction; the cost is staleness and storage. - **Order by something immutable.** If the list is ordered by an insert-only, never-updated key, insertions land at one end only, and if you page from that end the drift disappears for one of the two directions. - **Accept and disclose.** For a search UI where approximate paging is acceptable, document it. That is a legitimate choice; making it silently is not. ## How to recognise it in a bug report Symptoms that point here: "item appears on page 2 and page 3", "the export is missing rows but the count matches", "scrolling repeats items only in production", "the batch job skipped records under load". The tell is that all of them are load- and timing-dependent and none reproduce on a quiet database. Before reaching for isolation levels or locking, check whether the pager is position-based and whether the ordering is total — that explains the overwhelming majority of these reports.

  • Which is the more dangerous direction of drift — duplicates or skipped rows?
    Skipped rows. A repeated row is visible and users report it; a row that shifted above the boundary is simply never displayed, and nothing on screen indicates a loss. When a pager drives a batch job, skipped rows mean records that were never processed, with a run that reports success.
  • Does raising the transaction isolation level fix offset paging drift?
    No, because the pages are separate transactions and separate requests. Isolation guarantees a consistent view *within* one transaction; it says nothing about two statements issued seconds apart from a stateless HTTP endpoint. Holding one transaction open across user think-time is not a viable alternative.
  • What does anchored paging give up compared with OFFSET?
    Random page access. You can move to the next or previous page relative to a known row, but you cannot jump to page 7 or render "page 7 of 40" without extra work, and backward paging needs the predicate and ordering reversed. It suits feeds, infinite scroll and batch iteration better than a numbered pager.
  • Why does adding a unique tie-breaker matter before blaming concurrency?
    Because a non-total ordering reshuffles tied rows between executions even with no writes at all, producing the same duplicates and gaps. It is a one-line fix and it removes a whole class of false concurrency reports, so check the ORDER BY for uniqueness before investigating anything else.

saying these in an interview costs you the question

  • Blames the ORM or the connection pool for duplicates
  • Says a higher isolation level would fix paging drift
  • Thinks OFFSET remembers the previous page's rows
  • Assumes only deletes, never inserts, cause drift
  • Treats skipped rows as harmless because nothing errored

context

resolving links…