← Back to the library
BackendApplied · 7 min

Paging through a list skips or repeats items

“Users scrolling a feed see the same item twice, or an export misses records, whenever new rows arrive or old ones are deleted while they page.”

THE PRINCIPLE

A page boundary must be a value in the data, not a row count, and the sort order must be total.

FIRST MOVES

  1. Make the ORDER BY deterministic by ending it with a unique column (created_at, id); without that, even a static table can return inconsistent pages.
  2. Replace OFFSET with a keyset condition on the last row seen: WHERE (created_at, id) < ($1, $2).
  3. Back the sort with a matching composite index so each page is an index range scan, regardless of depth.
  4. Encode the cursor opaquely and losslessly: JavaScript Date drops PostgreSQL's microseconds, which silently skips or repeats rows at the boundary.
  5. Keep OFFSET only where jumping to page N matters and drift is acceptable, such as small admin tables.

TOOLS: THEN → NOW

page and pageSize query parametersOpaque cursors (after / starting_after) plus has_more, as in Stripe's and GraphQL connection-style APIs
Deep OFFSET on large tablesKeyset (seek) pagination over a composite index

PATTERN SNAPSHOT

-- First page
SELECT id, created_at, title FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: $1, $2 = created_at and id of the last row returned
SELECT id, created_at, title FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX posts_created_id ON posts (created_at DESC, id DESC);

CLOSE THE AI. EXPLAIN THIS.

Why does ORDER BY created_at alone break keyset pagination, and what happens to the user on page 3 of an OFFSET feed when five new posts arrive?

HOW IT WORKS UNDERNEATH

SOURCES

Guide reviewed