Frontend / APIs & data fetching / 04_pagination_and_infinite_queries.md

Pagination — Offset vs Cursor, and Infinite Queries

Updated 5 min read source
On this page6
  1. TL;DR
  2. In depth
  3. Gotchas / edge cases
  4. What a senior is expected to say
  5. Cross-references
  6. Further reading

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.”

In depth

Offset vs cursor — what actually differs?

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.

Show me a cursor-paginated endpoint contract.

http 
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:

json 
{
  "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.

Why does an offset query get slow at page 1000?

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.

sql 
-- 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.

TanStack Query useInfiniteQuery — show the shape.

tsx 
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.

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

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

tsx 
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();
    // trigger 200px before reaching it
    }, { rootMargin: "200px" });
    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 Frontend Performance — Senior Interview Prep.

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

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.

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

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.

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

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.

How does cursor pagination compose with filters / sorting?

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 4

  • “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