Part 6 of 9 · 3 min read
Pagination
Returning a long list in pieces, and why a cursor beats an offset.
Think of a book. Nobody reads a thousand pages in one go. A reader turns to a page, and a bookmark marks the place.
A list endpoint must not return a million rows in one reply. That would be slow, would fill the memory of the server and the client, and would invite attacks. The book "Understanding Distributed Systems" says a list should return a limited number per call.
Pagination
Pagination is returning a long list in pieces called pages, with a way to ask for the next piece.
Example: GET /orders?limit=20 returns the first 20 orders and a pointer to the next 20.
The first way is the offset.
GET /orders?limit=20&offset=40
The server skips 40 rows and returns the next 20. It is easy, and it lets a user jump to page 3. It has two problems.
Problem 1: the cost grows with the offset. The database must walk past all the skipped rows. Suppose, made up for this example, that skipping one row costs 1 microsecond. Skipping 900,000 rows costs 900,000 × 1 microsecond = 0.9 seconds, for one request, to return 20 rows.
Problem 2: the list moves while the reader reads. A page size of 10. The reader loads page 1 and sees items 1 to 10. Then 3 new items arrive at the front of the list. Old item 1 is now at position 4, and old item 10 is at position 13. The reader asks for offset 10 and gets positions 11 to 20, which are old items 8 to 17. Items 8, 9 and 10 appear twice.
The second way is the cursor.
Cursor
A cursor is a marker that says where the last page ended, usually the sort key of the last row seen. The next request asks for rows after it.
Example: after reading order 900,020, the client asks for 20 orders with identifier greater than 900,020.
GET /orders?limit=20&after=900020
The server runs a query like "orders with id above 900,020, sorted by id, first 20". With an index (a sorted lookup structure on that column; Level 2 explains it), the database jumps straight to 900,020 and reads 20 rows. The cost does not depend on how deep the reader is. New rows at the front do not shift the place, so nothing repeats.
The reply carries the cursor for the next call.
{
"items": [ ... 20 orders ... ],
"next_cursor": "900040"
}
When next_cursor is missing, the list has ended.
| Offset | Cursor | |
|---|---|---|
| Jump to page 50 | Easy | Not possible directly |
| Cost on deep pages | Grows | Stays flat |
| Stable when rows are added | No | Yes |
| Typical use | Small lists, admin screens | Feeds, logs, big tables |
A cursor can be an opaque string, such as an encoded value. This lets the server change its details later without breaking clients.
If the sort order is not unique, for example "newest first" with several rows in the same second, the cursor must include a tie breaker such as the identifier. Otherwise rows with the same time may be skipped or repeated.
Check yourself:
- A table has 2 million rows. Which method is cheaper for the page near the end, and why?
- What goes wrong with an offset when new rows are added at the front?