Pagination

Pagination

An unbounded collection endpoint is a production incident waiting for your first successful week. Cap it from the first commit: Math.min(Number(req.query.limit) || 20, 100) is the whole rule — the || 20 absorbs ?limit=abc, the Math.min keeps ?limit=999999 from becoming a full table scan. Offset pagination (?page=2&limit=3) is what everyone writes first: stateless, jumps to page 40, gives a total for a "1 of 14" widget. It is also wrong under concurrent writes, because the offset counts rows, and rows move.

Offset pagination repeats a row when the set shifts; a cursor does not

Cursor pagination trades random access for correctness. The server returns an opaque token encoding the last row it emitted — Buffer.from(lastId).toString('base64url') here — and the next request says ?after= that token, so the query becomes WHERE id > :cursor ORDER BY id LIMIT :n. An index answers that in constant time however deep the client is, while OFFSET 100000 makes the database count and discard a hundred thousand rows. The offset response can afford counts and the cursor response cannot; both carry a Link header (RFC 8288):

Output of 60

> GET /api/v1/books?page=2&limit=3&sort=-year,title
< 200 OK
< Link: <https://api.example.com/api/v1/books?page=3&limit=3&sort=-year%2Ctitle>; rel="next",
        <https://api.example.com/api/v1/books?page=1&limit=3&sort=-year%2Ctitle>; rel="prev"
{"data":[{"id":"bk_018","title":"Piranesi",...},{"id":"bk_033",...},{"id":"bk_041",...}],
 "page":{"number":2,"size":3,"total":42,"pages":14},
 "links":{"self":"...page=2...","first":"...page=1...","last":"...page=14...",
          "next":"...page=3...","prev":"...page=1..."}}
> GET /api/v1/books/cursor?limit=3&after=YmtfMDAz
< 200 OK
{"data":[{"id":"bk_004","title":"The Dispossessed"},{"id":"bk_005","title":"A Wizard of
  Earthsea"},
         {"id":"bk_006","title":"The Left Hand of Darkness"}],
 "page":{"size":3,"nextCursor":"YmtfMDA2"},
 "links":{"next":".../books/cursor?limit=3&after=YmtfMDA2"}}

Use offset pagination for an admin table that needs page numbers; use cursors for feeds, infinite scroll and exports. Two details make cursors safe. The sort key must be unique — pair a non-unique one with the id (ORDER BY year DESC, id DESC) or two books from 2020 straddle the boundary and one vanishes. And the cursor must encode the sort it was issued under. Sign it too, since Buffer.from('YmtfMDAz', 'base64url').toString() is bk_003.

MENU