Returning a Large Result a Page at a Time

Pagination means an API returns a large list in pieces, called pages, and the client asks for the next page when it needs it. A shop with a million orders does not send all of them when an admin opens the orders screen: it sends the first 25, and a way to ask for the 25 after those.

Why not send everything. A million rows is a response of hundreds of megabytes. The database must read them all, the server must hold them in memory to build the response, the network must carry it, and the client must parse it, only for a person to look at the first screen. Every step costs more than the reader will ever use.

What a page request carries. Two things: how many items to return, usually called limit, and where to start. The response carries the items, and something that tells the client how to ask for the next page, or that there is none.

Two ways to say where to start. The first counts: skip this many rows, then return the next ones. That is offset pagination, and it lets a client jump to any page number. The second points: return the rows that come after this one. That is cursor pagination, and the pointer, the cursor, is usually the sort key of the last row the client saw.

An analogy. An offset is like "turn to page 212"; a cursor is like a bookmark. The analogy breaks when the book changes: a printed book's pages do not shift when a chapter is added, but a table's rows do, and that is exactly where offsets go wrong.

What must be fixed first. A stable order. Pages only make sense if the list is sorted the same way on every request, by something unique such as the id, or by a date with the id as a tie-breaker. Without one, the database may return rows in a different order each time, and pages overlap.

So every list an API returns needs a limit, a stable order, and a way to ask for the next page; the choice that remains is whether that way is an offset or a cursor.