# Pagination > Pagination splits a large result set into bounded responses. In system design interviews, the important choice is how clients continue reading without expensive scans, unstable ordering, or confusing duplicates as data changes. Source: https://sysround.com/concepts/pagination Updated: 2026-11-05 ## How pagination works Offset pagination identifies a position by count: `ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 1000`. It supports numbered pages, but the database generally must walk past the skipped entries before returning results. Inserts or deletes before the offset can shift page boundaries, causing duplicates or skipped items between requests. Cursor pagination returns a continuation token with each page. For keyset pagination, that token contains the last item's ordering values. With descending order, the next query uses `WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 51`. Return 50 items and use the extra item to determine whether another page exists; build the next cursor from the last returned item. Use a deterministic total order, usually a sort field plus a unique tie-breaker. For a user's chronological feed, an index on `(user_id, created_at DESC, id DESC)` lets the database seek to the boundary and read the next entries. Without a matching index, a cursor does not prevent expensive filtering or sorting. Treat the cursor as an opaque API token, not something clients construct. It can include ordering values, a query fingerprint, a version, and an expiration time. Validate its scope and integrity, cap page size, and perform authorization on every request; base64 encoding alone provides neither secrecy nor tamper protection. A keyset boundary avoids offset shifts, but it does not freeze the dataset. Newer inserts stay above a descending cursor, while backfilled records, deletions, and changing sort values can still affect traversal. If the contract requires a fixed result set, use a supported snapshot or retained result session with an expiration policy. Cursor pagination is broader than SQL keyset pagination. Ranked search may use a search-after token tied to a point-in-time view; a personalized feed may retain an ordered candidate list for a session. Across shards, the service must merge results in a common order and preserve enough continuation state to resume correctly. ## Worked example: Paginating a changing photo feed A photo feed returns 50 posts at a time, ordered by immutable `published_at` and unique `post_id`, both descending. The feed table has an index on `(viewer_id, published_at DESC, post_id DESC)`. The service requests 51 rows, returns the first 50, and encodes the 50th row's ordering values in a signed cursor. The next request seeks strictly below that boundary. If 12 newer posts arrive, they do not shift the next page; a refresh retrieves them. Deleting the boundary post does not break continuation because its values are in the token. Late backfills can still appear, so the API promises live chronological traversal, not a frozen snapshot. ## Trade-offs - **Deep-page efficiency.** Offset work typically grows with the skipped prefix, while indexed keyset pagination can seek directly to a boundary, though residual filtering and shard fan-out still add cost. - **Numbered pages versus sequential navigation.** Offsets make arbitrary page jumps straightforward, while cursors naturally support next and previous navigation rather than jumping directly to page 200. - **Freshness versus stable traversal.** Live queries expose changes between pages, while snapshots or retained result lists improve stability at the cost of storage, retention, and freshness. - **Flexible sorting versus index cost.** Each supported filter-and-sort combination may need a suitable index or search backend, increasing storage and write overhead. - **Stateless tokens versus server-side sessions.** Self-contained cursors avoid session lookups, while server-side continuation state can represent complex rankings but requires expiration and recovery behavior. - **Exact counts versus bounded work.** An exact total count may require substantial additional work, so a fast paginated endpoint often returns only whether more results are available. ## When to use it in a system design interview - The design includes feeds, message history, review lists, photo libraries, or search results that cannot fit in one response. - Users scroll deeply through a large dataset, making offset scans increasingly expensive. - Writes continue while users browse, so you need to explain duplicates, omissions, and refresh behavior. - Results come from changing rankings or multiple shards, requiring an explicit continuation contract. - An administrative interface needs numbered pages over a small or relatively stable dataset, where offsets may be the simpler choice. ## How to bring it up in the interview - Start with the product contract: infinite scroll or numbered pages, live results or a stable session, and whether exact counts or backward navigation are required. - Show the ordering, continuation predicate, and matching index together; interviewers often probe timestamp ties, deleted boundary rows, and query-plan efficiency. - State the guarantee precisely: keyset pagination avoids offset shifts but does not provide snapshot isolation or exactly-once traversal of a changing result set. - For ranked or sharded results, explain how ranking stays stable and how continuation resumes the merge; expect questions about session expiration and shard failures. - Define the API behavior for invalid or expired cursors, changed filters, capped page sizes, and permission changes rather than treating the token as authorization. ## Common mistakes - Using a timestamp alone as the cursor even though multiple records can share it, causing records at the boundary to be skipped. - Claiming cursor pagination is always fast without specifying an index that supports the filters and ordering. - Promising no duplicates or omissions while sort values, ranking, or result membership can change between requests. - Assuming an encoded offset gains the performance and stability benefits of keyset pagination. - Reusing a cursor with different filters or trusting it to bypass current access checks. ## Interview questions where it comes up - [Design Instagram](https://sysround.com/questions/design-instagram): Stable feed pagination must account for asynchronous inserts, deletions, and follow changes while a user scrolls. - [Design TikTok](https://sysround.com/questions/design-tiktok): Swipe-feed continuation must balance fresh recommendations with stable page delivery across retries and session boundaries. - [Design Facebook post search](https://sysround.com/questions/design-facebook-post-search): Distributed ranked retrieval needs deterministic continuation and a policy for index changes between pages. - [Design Facebook Marketplace](https://sysround.com/questions/design-facebook-marketplace): Nearby discovery requires stable pagination even as listing availability, location filters, and search results change. - [Design a restaurant review app like Yelp](https://sysround.com/questions/design-yelp): Ranked nearby discovery and long review histories need bounded retrieval with explicit ordering and continuation semantics. - [Design a chat app like WhatsApp](https://sysround.com/questions/design-whatsapp): Loading older conversation history requires efficient boundaries and stable message ordering while new messages arrive. - [Design a news feed like Facebook](https://sysround.com/questions/design-news-feed): Feed delivery needs a continuation strategy that works with cached entries, fan-out updates, and potentially changing rankings. ## FAQ ### What is the difference between offset and cursor pagination? Offset pagination asks for results after skipping a specified number of rows. Cursor pagination resumes from a continuation token, often containing the last returned item's sort values. Offsets suit numbered pages; indexed keyset cursors usually suit deep scrolling through large, changing datasets. ### Does cursor pagination prevent duplicates and missing records? Not by itself. A stable, unique ordering prevents boundary ambiguity, and keyset pagination avoids shifts caused by inserts or deletes before an offset. Changing sort values, backfills, or changing rankings can still affect traversal; a fixed result set requires a snapshot or retained session. ### Can cursor pagination support previous pages and page numbers? Previous-page navigation can use the first item's boundary, reverse the comparison and query order, then reverse the returned rows. Arbitrary page-number jumps are not naturally supported because a cursor identifies a boundary rather than a row count. If direct jumps are essential, consider offsets, stored checkpoints, or a materialized result list. ## Related concepts - [Database indexing](https://sysround.com/concepts/database-indexing) - [REST API design](https://sysround.com/concepts/rest-api-design) - [MVCC](https://sysround.com/concepts/mvcc) - [Full-text search](https://sysround.com/concepts/full-text-search) --- SysRound (https://sysround.com) is an AI practice tool for system design interviews: a voice or typed AI interviewer, a shared whiteboard, then feedback on every skill and a model answer. Its feedback is AI-generated and can be wrong.