Lists, Pagination & Infinite Scroll — Mobile API Best Practices

Infinite-scroll feeds need cursor (keyset) pagination: offsets skip or duplicate items when new rows arrive at the top. Return an opaque next_cursor, a stable sort (e.g. created_at DESC, id DESC) and a page size cap. For pull-to-refresh, request items newer than the first item's cursor (before/after cursors). Include has_more so clients stop requesting.

Pagination for mobile lists

Criterion	Offset/limit	Cursor (keyset)	Page token (opaque)
New items inserted at top	Duplicates/skips	Stable	Stable
Jump to page N	Yes	No	No
DB cost at depth	Grows (OFFSET scans)	Constant (index seek)	Constant
Bidirectional (refresh)	Awkward	before/after cursors	prev/next tokens
Best for	Admin tables	Feeds, timelines	Public APIs (hides implementation)

{
  "data": [{"id": "p_981", "title": "…"}, {"id": "p_980", "title": "…"}],
  "page": {
    "next_cursor": "eyJjIjoiMjAyNi0xMC0xMVQwODo0MjoxMFoiLCJpIjoicF85ODAifQ",
    "prev_cursor": "eyJjIjoiMjAyNi0xMC0xMVQwOToxMjowMVoiLCJpIjoicF85ODEifQ",
    "has_more": true
  }
}

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT :limit + 1;      -- fetch one extra row to compute has_more

Common pitfalls

Using offsets for a live feed.
Non-unique sort keys (ties cause duplicates).
Unbounded page sizes from the client.

Key points

Keyset cursors for feeds
Unique, stable sort order
before/after cursors for refresh

Further reading: Use The Index, Luke: No Offset

Practice questions

Why does offset pagination produce duplicates in a live feed?

Answer

When new items are inserted at the top, every existing item shifts down; requesting the next offset returns items the user has already seen.

How do you implement pull-to-refresh with cursors?

Answer

Ask for items after the newest item the client has (an 'after/prev' cursor) and prepend them; if too many arrived, reset the list.