frontend / apis data fetching / 04_pagination_and_infinite_queries.md

Pagination — Offset vs Cursor, and Infinite Queries

5 min read source

Pagination — Offset vs Cursor, and Infinite Queries

TL;DR

Offset pagination (?page=3&size=20) is dead simple but breaks under high write traffic — pages shift as items are inserted/deleted, and skipping deep pages gets slow at the database. Cursor pagination (?after=<opaque_id>&limit=20) is stable under writes and scales to any depth — it’s the right default for infinite feeds, activity logs, and anything live. TanStack Query’s useInfiniteQuery is the cache layer for “pages of a list.”

Interview Q&A

Q: Offset vs cursor — what actually differs?

A:

Offset Cursor
URL ?page=3&size=20 ?after=cur_abc&limit=20
DB query OFFSET 60 LIMIT 20 WHERE id > 'cur_abc' ORDER BY id LIMIT 20
Insertions during paging items shift; user sees duplicates or skips stable — cursor anchors position
Deep pages slow (DB skips N rows) constant time (index lookup)
“Jump to page 50” trivial impossible (or expensive)
Total count natural (extra COUNT(*) query) usually unknown
Caching bad — page numbers shift good — cursor is stable
Implementation trivial needs sort order + tiebreaker

Offset wins for admin tables with stable data + jump-to-page UX. Cursor wins for infinite scroll feeds, append-only logs, and high-write lists.

Q: Show me a cursor-paginated endpoint contract.

A:

GET /api/orders?limit=20                      → first page
GET /api/orders?after=cur_abc&limit=20        → next page
GET /api/orders?before=cur_xyz&limit=20       → previous page (bidirectional)

Response:

{
  "items": [...],
  "nextCursor": "cur_def",      // null if last page
  "prevCursor": "cur_xyz"
}

The cursor is opaque to the client — base64’d {sortValue, tiebreakerId} is typical. Don’t expose primary keys directly; tomorrow you’ll want to change the sort and the cursor format will follow.

Q: Why does an offset query get slow at page 1000?

A: OFFSET 20000 LIMIT 20 requires the DB to scan and discard 20,000 rows before returning 20. There’s no index trick for it. Cursor’s WHERE id > 'cur' is an index seek.

-- Offset — scans 20,020 rows
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 20000;

-- Cursor — seeks the index, reads 20 rows
SELECT * FROM orders
WHERE (created_at, id) < ('2026-04-12 09:00:00', 'ord_abc')
ORDER BY created_at DESC, id DESC LIMIT 20;

The (created_at, id) tuple is the cursor; the tiebreaker id prevents ties on created_at from skipping rows.

Q: TanStack Query useInfiniteQuery — show the shape.

A:

const {
  data, fetchNextPage, hasNextPage, isFetchingNextPage,
} = useInfiniteQuery({
  queryKey: ["orders", "infinite", filters],
  queryFn: ({ pageParam }) => fetchOrders({ after: pageParam, limit: 20 }),
  initialPageParam: undefined as string | undefined,
  getNextPageParam: (lastPage) => lastPage.nextCursor ?? undefined,
});

// data.pages is an array of page responses
const flat = data?.pages.flatMap((p) => p.items) ?? [];

<List items={flat} onEndReached={() => hasNextPage && fetchNextPage()} />

The cache stores data.pages and pageParams; refetch refreshes all pages in order. The select option (select: (d) => d.pages.flatMap(...)) is the clean way to flatten for the UI.

Q: How do you detect “scroll near bottom” to trigger fetchNextPage?

A: IntersectionObserver on a sentinel element near the list’s end — modern, performant, no scroll-event throttling.

function Sentinel({ onVisible }: { onVisible: () => void }) {
  const ref = useRef<HTMLDivElement>(null);
  useEffect(() => {
    const el = ref.current;
    if (!el) return;
    const obs = new IntersectionObserver(([entry]) => {
      if (entry.isIntersecting) onVisible();
    }, { rootMargin: "200px" });   // trigger 200px before reaching it
    obs.observe(el);
    return () => obs.disconnect();
  }, [onVisible]);
  return <div ref={ref} />;
}

<List>{items.map(...)}<Sentinel onVisible={fetchNextPage} /></List>

For long lists pair this with virtualization (@tanstack/react-virtual, react-window) — see ../15_performance/.

Q: What happens to an infinite list when a new item is created via mutation?

A: Two strategies:

  • Invalidate + refetch all pages — simple, correct, expensive (every loaded page re-fetched). Use when freshness > cost.
  • Surgical setQueryData — splice the new item into the first page in the cache. Cheaper, but you have to handle position (sort order, tiebreakers).

Most real apps: invalidate the first page only after a mutation, optimistically prepend if the user just created the item.

Q: How do you let users “jump to” a moment in a cursor-paginated feed?

A: You can’t with a pure cursor — there’s no “page 50.” You add a timestamp/key-based deep link: ?at=<timestamp> resolves server-side to the appropriate cursor. The infinite scroll then loads the page containing that anchor.

Q: How do you handle the “I want a total count and page numbers” UX?

A: Either:

  • Use offset pagination and accept the trade-offs (this is fine for admin tables of stable data).
  • Use cursor for paging + a cheap COUNT query for “showing 1-20 of 4,231” — but never compute COUNT(*) on huge tables; cache it or show “showing 20+” instead.

Q: How does cursor pagination compose with filters / sorting?

A: The cursor encodes the sort order. Change the sort, get a new cursor — the previous cursor is invalid. Servers should reject mismatched cursors (400) instead of silently misinterpreting them.

Gotchas / edge cases

  • Cursor without a tiebreaker = duplicates or skips on equal sort values. Always (sortField, id).
  • Filter changes invalidate cursors. Either include the filter in the cursor, or version cursors so a stale one errors out.
  • fetchNextPage race — calling it twice quickly. Guard with isFetchingNextPage.
  • Page mutations after delete — deleting an item shifts cursor positions; on next fetchNextPage you may skip or duplicate one. Refetch the current page on delete to stay clean.
  • Offset pagination during a write spike — user sees an item twice as it gets pushed onto page 2 between requests, or misses an item that moves from page 2 to page 1.
  • Browser back-button + infinite scroll — page state isn’t in the URL; back button doesn’t restore scroll position. Pair infinite with URL-state for “last visible cursor.”

What a senior is expected to say

  • “Cursor is the default for live or large data — stable under writes, fast at depth, no OFFSET scans. Offset is fine for stable admin tables with jump-to-page UX.”
  • “Cursors are opaque to the client and encode (sortValue, id) — I never expose raw primary keys.”
  • “I use useInfiniteQuery with IntersectionObserver and pair it with virtualization for long lists.”
  • “After a mutation that creates an item, I invalidate the first page and prepend optimistically; full invalidation of all pages is wasteful.”

Cross-references

Further reading

Previous
Optimistic Updates and Rollback