Offset Pagination That Skips or Repeats Rows

Offset pagination counts positions, and positions move when rows are added or removed. A row inserted above a reader's place while they page shifts every row below it down by one, so the next page repeats a row; a row deleted shifts them up, so the next page skips one.

The symptom. Readers scrolling a feed see the same post twice in a row at a page boundary. Others report a post they know exists never appeared in their scroll.

The trigger. A feed of posts 1 to 10, newest first, five to a page, read with ORDER BY id DESC LIMIT 5 OFFSET ?. A reader loads page 1. Before they load page 2, post 11 is published.

The mechanism. Offset 5 means "skip the first five rows as they are now", not "start after post 6". The insert pushed post 6 from fifth to sixth place, so skipping five lands on it again. Run the other way, deleting post 9 before page 2 returns 4, 3, 2 and 1: post 5 moved up into fifth place, was skipped, and the reader never sees it.

The cost. Duplicates are an annoyance. Skips are worse, because nobody notices them, and in an export or a sync a skipped row is data silently missing.

The remedy. Use a cursor for any list that changes while readers page through it: WHERE id < 6 for the next page of a newest-first feed starts after post 6 whatever was inserted above it. Where offsets must stay, for page numbers, accept the drift and say so, or page through a fixed snapshot.

So never page with offsets through data that changes while the reader pages, because the offset counts positions and the positions move.