Pagination Patterns (Offset vs Cursor)
Offset pagination asks for a page number and a page size. Cursor pagination asks for the items that come after a pointer. Offset is simpler and lets a reader jump to any page. Cursor is faster on large tables, and it stays correct when new rows arrive while someone is reading.
Use offset for small lists that rarely change. Use cursor for feeds, chat histories, logs, and any list that grows while people scroll it.
Offset vs cursor pagination at a glance
| Offset pagination | Cursor pagination | |
|---|---|---|
| Client sends | page and limit, or offset and limit | cursor and limit |
| Database work | Reads and discards every skipped row | Seeks straight to the position |
| Cost on page 5,000 | Grows with the page number | Stays flat |
| Jump to any page | Yes | No, only forward or backward one page |
| Correct while data changes | No, rows shift between pages | Yes, the anchor is a value |
| Total count available | Easy | Expensive, usually left out |
| Good for | Admin tables, reports, small catalogs | Feeds, timelines, messages, logs |
How offset pagination works
The client asks for a page number and a page size. The server turns that into a LIMIT and an OFFSET.
GET /posts?page=3&limit=20
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;
The database still has to walk the first 40 rows. It reads them, then throws them away. At page 3 that costs nothing. At page 5,000 the database reads 100,020 rows to return 20.
This is why offset pagination gets slower the deeper you go. The work grows with the offset, not with the page size.
How cursor pagination works
The client sends a pointer to the last item it saw. The server returns the items after it.
GET /posts?limit=20&cursor=eyJ0IjoiMjAyNi0wOC0wOVQxMjowMFoiLCJpZCI6OTkxfQ
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
A cursor is an opaque token. Opaque means the client should not read it or build one. It usually holds the sort key of the last row, encoded as base64.
The index does all the work here. The database seeks to one position and reads 20 rows. Page 5,000 costs the same as page 2. This approach is also called keyset pagination or seek pagination.
The problem cursors actually solve
Offset pagination breaks when the underlying list changes.
Say a feed is sorted newest first. You read page 1 and see items 1 to 20. Someone posts a new item. Every existing item shifts down by one. You request page 2, which is now offset 20. The item that used to be number 20 is now number 21, so you see it twice.
Deletions cause the opposite problem. An item slides up past the boundary and you never see it.
A cursor anchors on a value rather than a count. New rows appear above your cursor and do not disturb what comes after it.
Choosing a stable sort key
A cursor needs a sort key that is unique and ordered. A timestamp alone is not unique. Two rows can share the same millisecond, and one of them will be skipped or repeated.
Pair the sort column with the primary key. Sort by (created_at, id) and compare both in the WHERE clause. The primary key breaks the tie.
The sort key also has to be indexed. A cursor query without a matching index is just a slow scan with extra steps.
What the response should return
Cursor responses carry the next pointer, not a page number.
{ "data": [ { "id": 991, "title": "..." } ], "next_cursor": "eyJ0IjoiMjAyNi0wOC0wOVQxMjowMFoiLCJpZCI6OTcxfQ", "has_more": true }
Leave the total count out of a cursor response. Counting the whole table is the expensive part, and it is the reason many endpoints are slow.
Offset responses usually include total and total_pages, because the client needs them to draw page numbers.
Return has_more as its own field. Do not make the client guess from an empty array on the next request.
Common mistakes
- Using offset on a table with millions of rows. Deep pages get slow and time out.
- Sorting by a column that is not unique, with no tie breaker.
- Letting clients build or edit cursor tokens. Sign them or encode them so they stay opaque.
- Changing the sort order between requests. The old cursor no longer means anything.
- Returning a total count next to a cursor. It costs more than the page itself.
- Allowing an unbounded
limit. Cap it, and document the cap.
Frequently asked questions
What is the difference between offset and cursor pagination?
Offset pagination skips a fixed number of rows and returns the next batch. Cursor pagination starts from a pointer to the last row you saw. Offset supports jumping to any page. Cursor stays fast and stays correct while data changes.
Is cursor pagination faster than offset pagination?
On large tables, yes. Offset cost grows with the page number, because the database reads and discards skipped rows. Cursor cost stays flat, because the index seeks to one position. On the first few pages the two are about the same.
What are the two types of pagination?
Offset based and cursor based. Offset uses page numbers or a row count to skip. Cursor uses a token that points at the last item returned. Keyset pagination and seek pagination are other names for the cursor approach.
Can I jump to page 10 with cursor pagination?
No. A cursor only describes one position, so you can move forward or backward one page at a time. If the product needs page numbers, use offset, or offer offset for shallow pages and cursor for deep ones.
What should a cursor token contain?
The sort key values of the last row, including the tie breaker. Encode it as base64 so clients treat it as opaque. Sign it if you do not want people to craft their own.
Which one should I use in a system design interview?
Say what the list looks like first. For a feed or a message history, choose cursor and explain the shifting window problem. For an admin table with page numbers, choose offset. Naming the trade off matters more than the choice.
How to prepare
Learn the API side, not just the query. Pagination is a contract decision. It changes the request parameters, the response shape, and what clients can build. Grokking Modern API Design Interview has a lesson on pagination seen from the consumer side, and it applies the choice across 15 worked designs.
Practice under time pressure. Book Mock Interviews with ex-FAANG engineers to get feedback on how you present the trade off out loud.
Related reading
GET YOUR FREE
Coding Questions Catalog
$123
$197
$72
