Pagination: offset, cursor or keyset? How to choose the right strategy

Have you ever tried jumping to page 5,000 of a listing and watched the server freeze? Or noticed items disappearing or duplicating as you flip between pages? If so, you've already felt the pain of using the wrong pagination strategy.

Pagination looks trivial: split the data into pages and call it a day. But the choice between offset, cursor, and keyset is the difference between an API that responds in 2ms and one that times out under load.

Mental model Offset is telling the librarian: "skip 99,980 books to find page 5,000." She'll count every single one to get there. Cursor is saying: "give me the books right after this exact bookmark." She walks straight to the shelf and pulls the next batch. Same books, same library. Radically different trip.

The OFFSET problem nobody tells you about

Most devs start with offset/limit because it's intuitive:

It works beautifully on page 1. But on page 5,000, the database has to scan 100,000 rows before returning 20 results. On tables with millions of records, that turns into seconds of wall time.

Beyond performance, there's a consistency problem: if someone inserts a post while you're paging, items can duplicate or vanish between requests.

War story: how Slack and Stripe killed the "ghost message" bug

Slack's engineering team has written publicly about rebuilding their message history APIs around cursor pagination. With offset, a new message arriving mid-scroll would shift every row by one, so users would either see the same message twice or skip one entirely as they scrolled back through a channel. Stripe took the same stance years earlier and baked cursors into their public API philosophy: every list endpoint exposes starting_after and ending_before, never a page number. When your API is consumed by thousands of integrations, "page 5" is a lie waiting to happen.

Cursor-based: constant O(log n)

The idea is simple: instead of "skip N rows", you say "give me everything after this point":

With the right index, performance is constant regardless of the "page":

The numbers speak for themselves on a table with 10 million rows:

• Page 1: Offset ~2ms vs Cursor ~2ms (tied)
• Page 10,000: Offset ~3,200ms vs Cursor ~2ms (1,600x faster)
• Page 500,000: Offset times out vs Cursor ~2ms

Implementing cursor on the backend

The trick is to encode the cursor in base64url so it's opaque to the client:

Treat the cursor as opaque on the wire. Don't let clients construct or mutate it, that's how you end up coupling the API to a specific schema forever.

Keyset pagination with multiple columns

Keyset is cursor's overachieving sibling: same tuple-comparison trick, tuned for non-trivial sort orders. Say you want posts sorted by score DESC, then created_at DESC, then id DESC as a tiebreaker. The where-clause becomes a lexicographic comparison:

The id at the end is the tiebreaker that guarantees a total ordering: no ties, no skipped rows. Back it with a matching composite index:

Without that trailing unique column, two posts with identical scores and timestamps could both sit on the page boundary and one would silently vanish. Always anchor your keyset on something unique.

GraphQL and the Relay cursor spec

If you work with GraphQL, you've probably seen the Relay Cursor Connections spec. It standardizes cursor pagination across the ecosystem with a predictable shape:

edges wrap each node with its own cursor, and pageInfo tells the client whether to keep walking forward or backward. The spec also defines first/after for forward paging and last/before for backward paging, the same primitives Stripe exposes on REST. You don't need GraphQL to borrow the shape, it's a solid blueprint for any cursor API.

On the frontend: Infinite Scroll with React Query

TanStack Query v5 (the current line in 2026) renamed React Query's hooks slightly and made initialPageParam plus getNextPageParam mandatory on useInfiniteQuery. Here's the modern shape:

A small but real gotcha: include fetchNextPage in the effect's dependency array. React Query memoizes it, so you won't trigger an infinite loop, and you'll silence the exhaustive-deps lint rule that newer ESLint configs ship on by default.

When to use each strategy?

Use Offset/Page Number when:

• Small dataset (< 100K rows)
• Users need to jump to a specific page
• Admin panels with filters that already prune the dataset
• SEO (each page has an indexable URL)

Use Cursor-based when:

• Large or growing dataset (feeds, timelines)
• Infinite scroll
• Data changes frequently
• Public API (cursors are opaque)

Use Keyset when:

• You need to sort by multiple columns (popularity + date)
• You want cursor performance with composite sorting

FAQ

Can I go to the "previous page" with a cursor? Yes, but you need a bidirectional cursor. Relay handles this with before/after plus hasPreviousPage. Under the hood you flip the comparison operator and reverse the sort, then re-reverse the results client-side.

Is jump-to-page (e.g. "go to page 47") possible with cursors? Not really, that's cursor's Achilles heel. Cursors only know "next" and "previous", they don't track ordinal position. If jump-to-page is a hard requirement, stay on offset or hybridize: cursor for hot paths, offset for the rare jump.

Is a DB index mandatory for cursor pagination? Practically, yes. Without a composite index matching your ORDER BY, the database falls back to a sort and your "O(log n)" promise becomes a full scan. The index is what makes cursor fast, not the cursor itself.

How do I show a total count with cursors? Separately. Run a SELECT COUNT(*) (cached, or approximated via pg_class.reltuples on Postgres) independent of the paginated query. Many APIs just drop total counts entirely, Twitter and Slack don't show "page 12 of 847" for a reason.

Can I ORDER BY random() with cursor pagination? No, and honestly you shouldn't want to. Cursors need a stable, deterministic ordering to work. For randomized feeds, use a seeded shuffle (store the seed in the cursor) or pre-compute a randomized rank column you can paginate over.

Key Takeaways

Offset is the SELECT * of pagination: everyone starts there, but the sooner you migrate to cursor-based, the better. The implementation is slightly more involved, but the performance gap at scale is brutal. If you're building a new API, start with cursor from day zero. Your future self will thank you.