Cursor vs offset pagination: an interactive feed simulator
Offset pagination asks for “20 rows, skipping the first 2,000”. Cursor pagination, also called keyset pagination, asks for “20 rows after the last one I saw”. On a list that changes while people scroll, offsets show some items twice and skip others; and deep pages get slower, because the database still reads every row it skips. Cursors have neither problem, which is why feeds and public APIs use them.
The playground loads five pages of a newest-first feed both ways while posts arrive at the top and are deleted between page loads. Red posts were already shown on an earlier page.
Between page loads, 3 new posts arrive at the top. Offsets count positions, and positions moved: each new post pushes everything down a place, so the next page by offset starts with posts already shown: 12 repeats. The cursor remembers the last post shown, not its position, so it carries on from exactly there: no repeats, nothing skipped.
LIMIT 10 OFFSET 10 × pageWHERE id < :last LIMIT 10Why offsets show duplicates and skip items
An offset is a position, and positions move. If three new posts arrive while the user reads page one, every post shifts down three places, and page two by offset starts with the last three posts of page one: 12 repeats over five pages in the playground. If posts the user already read are deleted, everything shifts up, and the next page jumps over posts that moved into the gap: 8 never shown. A cursor names the last post itself, so new posts above it and deleted posts behind it do not matter.
Why deep offsets are slow
OFFSET 2000 does not jump to row 2,001. The database walks the index or table through the first 2,000 matching rows, then throws them away. Page 1,000 of a 20-per-page list reads 20,000 rows to return 20, and every page further reads 20 more. A cursor turns the same request into an index seek to the last seen key followed by 20 rows, the same cost on page 1 and page 1,000. Deep offsets are also an easy way for a crawler or a curious user to load the database heavily.
How cursor pagination works
- Sort by a unique key, or add a tie-breaker:
created_atalone can repeat, so sort by(created_at, id). - Return a cursor with each page: the sort key of its last row, usually encoded as an opaque string so clients do not build their own.
- Continue after it: the next request filters with
WHERE (created_at, id) < (:c, :i), using an index on the same columns. - Stop when a page comes back short or with no next cursor.
-- offset: simple, but reads and discards every earlier row, and positions shift
SELECT id, created_at, body FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 2000; -- page 101
-- cursor (keyset): continue after the last row the client saw
SELECT id, created_at, body FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- the index that makes it a seek
CREATE INDEX posts_feed ON posts (created_at DESC, id DESC);What cursors cannot do
- Jump to page 37: there is no page number, only next (and, with a second query, previous). That suits infinite scroll and APIs, less so a search results page with numbered links.
- Show a total count cheaply: counting matching rows is a separate, often expensive query; many products show “more” instead.
- Sort by anything: every sort order needs its own key and index, and sorting by a column that changes, like a score, can still move items between pages.
For numbered pages over data that rarely changes, such as an admin table, offsets are fine and simpler. For feeds, timelines, chat history, exports and public APIs, use cursors.
A cursor in an API
GET /posts?limit=20
200 OK
{ "items": [ ... ], "next_cursor": "eyJjIjoiMjAyNi0xMC0yM1QwOToxMjowMCIsImkiOjgzMTF9" }
GET /posts?limit=20&cursor=eyJjIjoiMjAyNi0xMC0yM1QwOToxMjowMCIsImkiOjgzMTF9In a system design interview
When you design a list endpoint, say how it pages: “GET /feed returns 20 posts and an opaque cursor encoding the last post’s timestamp and id. The next request continues after it with an index seek on (created_at, id), so page 50 is as fast as page 1, and posts arriving at the top do not shift what the user sees.” Expect the follow-ups: how new posts are shown (a separate “newer than the first post” query), going backwards, and paging over a sharded or ranked feed.
Read the guide to pagination or API design, see composite indexes for the index a cursor relies on, or watch a strong candidate design a news feed.
Questions people ask
What is the difference between cursor and offset pagination?
Offset pagination requests rows by position, LIMIT 20 OFFSET 40, so the database skips a number of rows. Cursor pagination, or keyset pagination, requests rows after a given key, WHERE id < :last_id LIMIT 20, so it continues from the last item the client saw. Cursors stay correct when the list changes and cost the same on every page.
Why does offset pagination show duplicate items?
Because an offset counts positions, and inserting items above the current page shifts every item down. If three new posts arrive while a user reads page one, page two by offset begins with the last three posts of page one. Deleting items does the opposite: everything shifts up and the next page skips items.
Why is OFFSET slow for deep pages?
The database cannot jump to a position: to answer OFFSET 20000 it reads the first 20,000 matching rows from the index or table and discards them before returning the page. Cost grows linearly with the page number. A cursor query seeks directly to the last seen key in an index and reads only the rows it returns.
How do you implement cursor pagination?
Sort by a unique key, or a sort column plus a unique tie-breaker such as (created_at, id), with an index on the same columns. Return the last row’s key as an opaque, encoded cursor with each page, and filter the next request with WHERE (created_at, id) < (:created_at, :id) ORDER BY created_at DESC, id DESC LIMIT n.
What are the downsides of cursor pagination?
You cannot jump to an arbitrary page number, only move forward or backward from a cursor; total counts need a separate query; and each sort order needs its own key and index. For small, stable admin tables with numbered pages, offset pagination is simpler and good enough.
What is keyset pagination?
Another name for cursor pagination: each page is defined by the key values of the last row on the previous page rather than by a row count to skip. The cursor sent to clients is usually just those key values, encoded so that clients treat it as opaque.
How do infinite scroll feeds handle new posts at the top?
They page downward with a cursor based on the last post shown, so new posts never disturb what the user is scrolling through. New posts are fetched separately with a query for items newer than the first post shown, and surfaced as a “new posts” prompt instead of shifting the list.