Pagination bounds the work and transfer of a collection read. Offset pagination is easy to express but may skip or repeat records when new rows arrive before the current page. A cursor based on a stable ordered pair, such as creation time and record ID, says where the next page begins. The server must fix a sort order, validate the cursor, cap the requested page size, and return a next cursor only when more results exist. A cursor is a position marker, not proof of permission; every page still applies the caller's visibility rule.
Make this comfortable
Cursor Pagination for Changing Collections
Working case
A reviewer scrolls an inspection feed sorted by newest creation time, then by ID for ties. While the reviewer reads page one, another inspection arrives. A keyset query for page two starts after the last item from page one, so it does not shift merely because the new row appeared at the front. The response includes 25 records at most and a cursor for the next page. The client should not decode the cursor as a case ID or use it to bypass filters; the server validates its shape and binds the same filters to each page.
Implementation
SELECT inspection_id, created_at, summary
FROM inspections
WHERE reviewer_id = :reviewer_id
AND (created_at, inspection_id) < (:cursor_time, :cursor_id)
ORDER BY created_at DESC, inspection_id DESC
LIMIT :page_limit;Cost and boundaries
An indexed keyset scan can seek near the cursor and read roughly O(P) rows for page size P, while a large offset may require scanning or discarding many earlier rows. The sort keys and filter must match an appropriate index. Encoding a cursor is small, but a tamper-resistant cursor may need signing when it carries filter or tenant context. A changing dataset can still alter membership; pagination promises a stable traversal rule, not a frozen snapshot unless the API explicitly provides one.
Common Mistakes
- Do not let the client request an unbounded page size.
- Do not sort only by a timestamp that can tie.
- Do not treat a cursor as authorization.
- Do not change filters between pages without restarting the traversal.
Connected lessons
Backend and API Systems; Idempotent Write Requests and Lost Responses; Background Jobs and the Outbox Boundary; Rate Limits and Request Budgets; HTTP requests: keep method, status, and body contracts separate; Form submission: validate on the server and return field errors; Routing: validate path parameters and return a stable error shape.
Failure trace
Page one ends at inspection 93. A newer inspection is inserted before the user requests page two; offset pagination shifts the boundary and repeats a row. A cursor built only from created_at also fails when two records share that timestamp. Order by the timestamp and a unique ID, carry both values in the cursor, and hold filters constant for the traversal.
Verification
- Insert a new newest row between page requests and check for repeated or missing IDs.
- Create two rows with the same timestamp and confirm the unique tie-breaker gives a total order.
- Try a malformed cursor and a changed reviewer filter; reject or restart the traversal explicitly.
Decision note
A cursor gives a position in a changing sequence, not a frozen snapshot. If a product needs a point-in-time export, define a snapshot or cutoff separately and test its retention cost.
Apply and check
Build Project: paginated inspection feed with safe writes; then check the boundary with Web Development: data and API contracts quiz.
Further connections
web-tech
web-development