Pagination Design
also called Cursor Pagination, Keyset Pagination
Returning large result sets in pages, where offset-based paging is both slow at depth and incorrect under concurrent writes.
Offset pagination is the obvious approach and has two defects that both worsen with scale.
Performance: retrieving page 5,000 requires the database to scan and discard 100,000 rows before returning 20. Deep pagination degrades linearly and is a common cause of slow queries that look harmless in the code.
Correctness: if a row is inserted or deleted between requests, the offset shifts, so an item can be skipped or returned twice. A client paging through a list to process every item will silently miss records, which is the kind of defect discovered during a reconciliation months later.
Cursor or keyset pagination fixes both. The client sends an opaque cursor encoding the position — typically the sort key of the last item seen — and the query becomes a range scan on an indexed column, which is fast at any depth and stable against insertions.
The constraints it imposes: the sort must be on a unique, stable, indexed key, or ties break the boundary — commonly resolved by a composite of timestamp and identifier. Arbitrary jumps to page N are no longer possible, which is usually acceptable since deep random access is rarely a real requirement. And the cursor should be opaque and validated, since an exposed cursor invites clients to construct their own.
The general rule for API design: default to cursor pagination, enforce a maximum page size, and never offer an unbounded list endpoint.