Pagination

The problem: a list too long for one reply

A feed, comments, a customer's orders: a list can be far longer than one reply should carry. Say a busy feed has 10,000 posts of about 500 bytes each. Sending them all is about 5 MB on every open, and most people read only the first 20. So the server sends pages: 20 posts, and a way to ask for the next 20.

The request itself must say where the next page starts, since the next one may reach a different server. There are two ways to say it: by position (an offset) or by the last item the reader got (a cursor).

Offset pagination

limit is the page size, and offset is how many items to skip:

GET /feed?offset=0&limit=20     → posts 1 to 20
GET /feed?offset=20&limit=20    → posts 21 to 40
GET /feed?page=3                 → the server turns it into offset 40

In SQL it's ORDER BY id DESC LIMIT 20 OFFSET 20. It's simple and can jump to any page, but has two problems.

The list moves under the reader. An offset counts positions, and positions change whenever items are added or removed above the reader. Take a feed of 8 posts, p8 the newest, in pages of 4. Ana reads page 1 (p8 to p5), and two new posts, p10 and p9, arrive on top before she scrolls:

position   when she read page 1   when she asks for page 2
1          p8                     p10
2          p7                     p9
3          p6                     p8
4          p5                     p7
5          p4                     p6
6          p3                     p5
7          p2                     p4
8          p1                     p3
9                                 p2
10                                p1

Page 2 is offset=4&limit=4: skip 4 positions, take the next 4. In the feed as it is now that's p6, p5, p4 and p3, so Ana sees p6 and p5 twice. A delete does the opposite: if p7 is deleted while she reads, p4 slides into position 4, which page 1 already sent, and page 2 starts at position 5 with p3. Ana never sees p4, and nothing tells her.

Deep pages are slow. For LIMIT 20 OFFSET 100000 (page 5,001 at 20 a page), the database can't jump to the 100,001st row: its index on id finds values, not positions, so it walks from the top and counts. It reads 100,020 rows to send 20, about 5,000 times the work of page 1, and every page is slower than the one before. Scripts, crawlers and exports go that deep.

Cursor (keyset) pagination

A cursor names the last item the reader got instead of a position. Page 1 ended at p5, whose id is 5 (newer posts get bigger ids), so page 2 is "the 4 posts older than p5":

SELECT * FROM posts
WHERE id < 5
ORDER BY id DESC
LIMIT 4;

New posts on top have bigger ids, so they can't change the answer: with p10 and p9 arrived, page 2 is still p4, p3, p2 and p1. A delete can't make it skip one either: with p7 gone, id < 5 still starts at p4. And the index finds id 5 like any key, in a few page reads, at any depth. This is also called keyset pagination. Don't guess the next ids instead (WHERE id BETWEEN 1 AND 4): deletes and other people's posts leave gaps.

Tiebreakers: when the sort column isn't unique

Many feeds sort by created_at, the time each post was made, and the cursor is the last post's time. But two posts can be made in the same second:

post   made at
p8     10:04:09
p7     10:04:08
p6     10:04:06
p5     10:04:05
p4     10:04:05
p3     10:04:03
p2     10:04:02
p1     10:04:00

Page 1 ends at p5, so the cursor is 10:04:05. Page 2 asks for created_at < '10:04:05' and starts at p3: p4 is skipped, because it isn't older, it's the same. Switching to <= repeats p5 instead. Milliseconds only make ties rarer: an app saving more than 1,000 posts a second must put two in the same millisecond. The fix is a tiebreaker, a unique column (usually the id) in both the sort and the cursor:

WHERE (created_at, id) < ('10:04:05', 5)
ORDER BY created_at DESC, id DESC
LIMIT 4;

-- the same, without comparing pairs:
WHERE created_at < '10:04:05'
   OR (created_at = '10:04:05' AND id < 5)

p4's pair, (10:04:05, 4), is below p5's, (10:04:05, 5), so page 2 starts at p4. The id belongs in the ORDER BY too: sorted by time alone, ties can come back in either order. An index on (created_at, id) keeps it a jump.

What goes in the reply

GET /feed?limit=4
→ 200 OK
  {"posts": [p8, p7, p6, p5],
   "next_cursor": "eyJ0IjoiMTA6MDQ6MDUiLCJpZCI6NX0"}

GET /feed?limit=4&cursor=eyJ0IjoiMTA6MDQ6MDUiLCJpZCI6NX0
→ 200 OK
  {"posts": [p4, p3, p2, p1],
   "next_cursor": null}

• next_cursor is sent back for the next page, and it's null on the last page. To know without an extra request, the server asks the database for one row more than the page (5 here): if only 4 come back, this is the last page.
• has_more: some APIs add this flag to every page. Stripe's list endpoints do, and take starting_after, the id of the last object seen, as their cursor.
• An opaque token: this cursor is {"t":"10:04:05","id":5} in base64. Clients just pass it back, so the server can change what's inside later without breaking them. Base64 is not encryption: if a cursor must not be read or edited, encrypt or sign it.

Offset or cursor: the trade-offs

• Offset jumps to any page and, with a count, shows "page 3 of 50": good for search results and admin tables, where people click page numbers, rarely go deep, and the list barely moves. Its costs: repeats and skips when the list changes, slow deep pages, and counting every match for "of 50".
• Cursor is stable and fast at any depth: good for feeds, infinite scroll, chat history and exports. Its costs: no jumping to page 37, no total, and it needs a unique sort key with an index.

Many systems use both: cursors in the API, page numbers on the search screen.

Where it shows up in design interviews

Any list endpoint in your API step (a timeline, comments, messages, search results) invites "how does it page?" Answer from what the caller sees down to the database:

1. "GET /feed returns 20 posts per call, newest first."
2. "It's cursor-paginated: each reply carries next_cursor, null on the last page."
3. "The cursor is an opaque token with the last post's time and id."
4. "The query reads the posts below that from an index on (created_at, id), so page 5,001 costs the same as page 1."

Saying what the cursor encodes shows you know why offsets fail: positions shift, a post's time and id don't.

Common interview questions on pagination

Why does offset pagination show duplicates?

An offset counts positions, and new items at the top push every position down. With two new posts, page 2 repeats the last two posts of page 1; a delete moves positions up and skips one.

Why is a large OFFSET slow?

The database reads and throws away every skipped row: OFFSET 100000 reads 100,020 rows to send 20. A cursor's WHERE id < … jumps through the index instead.

How do you paginate when the sort column isn't unique?

Add a unique tiebreaker, usually the id, to both the ORDER BY and the cursor. Without it, items sharing the boundary value are skipped with < or repeated with <=.

Can a cursor jump to page 37 or show the total?

No: it only knows where the last page ended. Where a screen needs page numbers, use offset there.

Summary

• List endpoints send pages: 20 items and a way to ask for the next.
• Offset counts positions: new items repeat posts, deletes skip them, and OFFSET 100000 reads 100,020 rows.
• A cursor names the last item seen (WHERE id < 5): nothing above shifts it, and the index jumps straight there.
• Add a tiebreaker when the sort column isn't unique: (created_at, id).
• Reply with next_cursor, an opaque token, null on the last page.
• Offset for page numbers and jumps; a cursor for feeds, infinite scroll and exports.