Offset and cursor pagination

Any collection that can grow needs pagination: the server returns a bounded page and tells the client how to get the next one. There are two common designs, and the difference between them is a favourite interview topic because one of them is quietly wrong on live data.

Offset pagination

GET /orders?limit=20&offset=40

"Skip 40, return 20." It is simple, the client can jump straight to page 7, and with a total count it can draw page numbers. It has two problems.

It drifts on changing data. The offset counts positions, not items. If a new order arrives at the top of a newest-first list between two requests, every item shifts down one place, and the first item of page 2 is the item the client already saw at the end of page 1. A deletion does the opposite and an item is skipped without anyone noticing.

It gets slower the deeper you go. In SQL databases, OFFSET 100000 still has to produce and throw away the first 100,000 rows before returning any.

Cursor (keyset) pagination

Instead of "skip N", the client says "continue after this item":

GET /orders?limit=20&after=WyIyMDI2LTAxLTAyVDEyOjAwOjAwWiIsNl0

The cursor encodes the sort key of the last item the client received. The server returns the items that come after that key in the sort order — in PostgreSQL or MySQL, a WHERE (created_at, id) < (?, ?) against an index on the same columns, which is about as fast on page 5,000 as on page 1. New items at the top do not shift anything, because the position is a value, not a count.

1// Newest first: a higher id is a newer order.2const newestFirst = (rows) => [...rows].sort((a, b) => b - a);3 4function offsetPage(rows, limit, offset) {5  return newestFirst(rows).slice(offset, offset + limit);6}7 8function cursorPage(rows, limit, afterId) {9  const sorted = newestFirst(rows);10  const rest = afterId === null ? sorted : sorted.filter((id) => id < afterId);11  return rest.slice(0, limit);12}13 14export function run() {15  const rows = [1, 2, 3, 4, 5, 6];16 17  const offset1 = offsetPage(rows, 3, 0);18  rows.push(7); // a new order arrives19  const offset2 = offsetPage(rows, 3, 3);20 21  const rows2 = [1, 2, 3, 4, 5, 6];22  const cursor1 = cursorPage(rows2, 3, null);23  rows2.push(7);24  const cursor2 = cursorPage(rows2, 3, cursor1[cursor1.length - 1]);25 26  return { offset: [offset1, offset2], cursor: [cursor1, cursor2] };27}

The tie-breaker

Sorting by createdAt alone is not enough: two orders created in the same second have no defined order between them, and a cursor that says "after 12:00:00" either skips or repeats the rest of that second. The sort must end in a unique column — usually the id — so every item has exactly one position: ORDER BY created_at DESC, id DESC, and the cursor carries both values.

Opaque cursors and the end of the list

Encode the cursor (base64 of a small JSON value is common) and document it as opaque: clients pass it back unchanged and never build one. That leaves you free to change what is inside.

To know whether there is a next page without a separate count query, fetch limit + 1 rows; if the extra one exists, return limit items and a nextCursor, otherwise return nextCursor: null. A client should stop when the cursor is null, not when a page is short.

When offset is still fine

Admin tables over small, slowly changing data, where "jump to page 12" and a total count matter more than perfect consistency. Cursors give up random access and totals; that trade is worth it for feeds, sync and exports, and for anything large or fast-moving.

Common mistakes

• Offset pagination on a feed that receives inserts: clients see duplicates and miss items, as the example shows.
• A cursor on a non-unique column, so items with the same timestamp are skipped or repeated at page boundaries.
• Returning a nextCursor whenever the page is full, which sends the client to an empty last page; fetch limit + 1 instead.
• Finding the cursor's item by id and continuing from its index. If that item was deleted, the position is lost; compare sort keys instead.