Backend · core

Pagination

Offset versus cursor pagination, stable ordering, and why deep pages get expensive.

backendapi-design

Mental model

OFFSET makes the database produce and discard every skipped row, so page 10,000 costs proportionally more than page 1 — and because the underlying rows shift between requests, an insert can make a client see the same item twice or miss one entirely. Cursor (keyset) pagination replaces 'skip N' with 'resume after this key', turning every page into an index seek at constant cost and making the sequence stable under concurrent writes. The price is that you can no longer jump to an arbitrary page number.

How to study Pagination

Begin by restating the mental model in your own words, then connect it to a concrete system you have built or operated. Name the mechanism, the constraint it addresses, and the trade-off it introduces. Use the linked roadmap and primary implementation references to check details, but close the source before writing your explanation. Retrieval is the learning step; rereading is only preparation.

Next, compare Pagination with Secondary Indexes, Rate Limiting. Ask what changes in correctness, latency, resource use, operability, and failure recovery. Complete Cursor pagination that survives inserts and preserve the command, input, output, and one failed attempt as evidence. Finish by explaining the idea without jargon to someone who has not studied the track.

Proof of understanding

Explain the mechanism from first principles and identify the state it reads or changes.
Give one situation where the concept is the right choice and one where it is not.
Predict a realistic failure mode before running the drill, then compare the prediction with evidence.
Connect the result to a roadmap or build artifact instead of treating the concept as isolated trivia.

Where it matters

Every list endpoint, and the usual cause of an API that is fine in testing and times out on real data.

Common mistakes

OFFSET on a large table, where deep pages degrade linearly
Paginating without a total ordering — ties on the sort key make results non-deterministic across pages
Exposing a raw primary key as the cursor, leaking row counts and creation order
Returning a total count on every page, which often costs more than the page itself

Learn from primary sources

Use the linked roadmap context and practice prompt.

Practice and explain it back

Cursor pagination that survives inserts

Implement page(rows, cursor, limit) for keyset pagination. Rows are {id, createdAt} sorted by (createdAt, id) ascending. cursor is null for the first page or {createdAt, id} for 'resume strictly after this'. Return { items, nextCursor } where nextCursor is the last item's key, or null when the page is not full.

Expected evidence: Two sequential pages return disjoint items even when a row is inserted between the calls.

Open the interactive drill →

Review prompts

An endpoint using OFFSET is fine on page 1 and times out on page 5,000, and users occasionally report a duplicated row. Explain both symptoms and the single change that fixes them.