Pagination, filtering and sorting

Topics in this section
2 cards

Offset or cursor pagination

Lists must be paged: nobody should download a million orders in one response. Offset pagination uses ?limit=20&offset=40: simple, and lets users jump to page 37. Its problems show up on large or changing data. The database still has to walk past all skipped rows, so deep pages get slow. And if a new row is inserted while someone pages, everything shifts by one: an item is shown twice or skipped.

Cursor pagination returns an opaque next_cursor that encodes where the last page ended (for example, the last id and timestamp). The next request asks for 'rows after this point', which an index finds directly however deep you are, and inserts don't shift anything. The cost: no jumping to an arbitrary page number.

Offset paging showing a duplicate after an insert, beside cursor paging continuing after the last seen id
In real life

Infinite-scroll feeds (Twitter, Slack, Stripe's list endpoints) use cursors. Admin tables with page numbers often use offsets, where the data changes slowly and pages are shallow.

In code: HTTP
GET /orders?limit=20
→ {"data": [ …20 orders… ], "next_cursor": "b3JkXzEyMw"}

GET /orders?limit=20&cursor=b3JkXzEyMw
→ {"data": [ …next 20… ], "next_cursor": null}   # null: last page

-- behind it, roughly:
SELECT * FROM orders WHERE (created_at, id) < (:t, :id)
ORDER BY created_at DESC, id DESC LIMIT 20;
  • The cursor is opaque: clients pass it back unchanged and never build it themselves, so you can change its contents later.
  • Ordering includes the id as a tie-breaker, so rows with the same timestamp are never skipped.
Check yourself

A feed of 50 million posts gets new posts every second. Why is cursor pagination the better fit? (Choose two.)

Select 2, then check.

Filtering and sorting

Filters and sorting go in the query string of the collection: GET /orders?status=paid&created_after=2026-10-01&sort=-created_at. Keep the rules predictable: one parameter per field, a clear operator style (created_after / created_before, or a documented bracket syntax), and a sort parameter where a leading minus means descending.

Only allow filters and sorts the database can serve with an index; an unindexed sort over millions of rows can take the database down. Document a default sort and a maximum page size, and reject values outside it with 400 rather than silently returning something else.

In real life

GitHub: GET /repos/{owner}/{repo}/issues?state=open&sort=updated&direction=desc&per_page=50, with per_page capped at 100.

Check yourself

Which requests are well-designed for 'paid orders from October, newest first'? (Choose two.)

Select 2, then check.