Cursor Pagination: Build Stable Lists for Growing APIs

Cursor pagination returns the next group of records after a known position in a stable ordering. It is useful for growing feeds, activity lists, and APIs where large offsets become expensive. A cursor is usually an opaque token representing the last returned ordering values. The token is not automatically an authorization mechanism. The server must still validate it, enforce filters and access rules, and use the same ordering when it loads the next page.

cursor pagination: a practical workflow

The sequence is Stable ordering → Last position → Next query → Next cursor. The sections below explain what each decision changes, which failure cases deserve attention, and how to verify the result in a small project. Use the example as a starting point, then adapt the interfaces and error policy to your own application.

Compare position-based and offset-based navigation

Offset pagination is simple and convenient for small administrative tables with numbered pages. However, reaching a large offset can require scanning or discarding many earlier rows, and concurrent inserts can shift page boundaries. Cursor pagination seeks from a known position and often works better for next-page navigation. It does not naturally provide arbitrary page jumps or an exact total count. Choose the approach based on the user interface and query workload rather than treating one method as universally better.

Make ordering deterministic with a tie-breaker

Ordering by created_at alone is not enough when multiple rows share the same timestamp. Add a unique tie-breaker such as id and include both values in the cursor. For descending order, the next query should select rows whose ordering tuple is less than the previous last tuple. Use an index that supports your filter and ordering. PostgreSQL supports row-value comparisons for compatible non-null values, but null ordering and mixed directions need deliberate handling rather than a copied comparison formula.

Bind the cursor to the query contract

A client should not reuse a cursor from one filter with a different sort order or tenant. Include relevant query context in the token or verify it separately. Base64 encoding makes data opaque to casual inspection but provides no integrity or secrecy. Sign the token if you need to detect tampering, and never trust the token to grant access. Clamp page size on the server. A next-page indicator can be derived by requesting one extra row beyond the page limit.

Document consistency under concurrent changes

A cursor usually improves boundary stability, but it does not create a snapshot across multiple HTTP requests. Deleted records can disappear, and edits to ordering fields can move records across the boundary. For an audit export requiring a consistent snapshot, use a design explicitly supporting that requirement. For a feed, document how new records appear and provide a refresh action. Choose immutable ordering fields where possible and test changes that happen while a user is browsing.

Example and implementation notes

SELECT id, created_at, title
FROM articles
WHERE published = true
  AND (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 21;
-- Return 20 rows; the extra row indicates another page.
-- Build the next cursor from the last returned row, not the extra row.

This example illustrates the core contract rather than a complete production application. Connect it to your real inputs, authentication rules, and failure-handling paths before using it. Check the expected output first, then change one input at a time to verify the assumptions. Keep those checks alongside the implementation so future changes do not silently weaken the behavior.

Verification checklist

• Create several records with identical timestamps.
• Insert and delete records between page requests.
• Reject malformed cursors and cap page size.
• Verify filters and authorization apply on every page.

Practice exercise

Build a list of fifty records with duplicate creation timestamps. Fetch pages of ten using both offset and cursor strategies. Insert a new record after the first request and compare the second page. Then change an existing ordering value and observe the limitation of cursors without a snapshot. Add tests that every original record appears exactly once when the dataset remains unchanged. Measure a late-page query with an index supporting your filter and order.

Review the result

After completing the exercise, describe the successful path and one failure path in plain language. Record the input, expected result, actual result, and the evidence you used to compare them. A passing demonstration should remain repeatable after a dependency update or a refactor. If you cannot reproduce the result, reduce the example until the source of uncertainty is visible. Keep the smallest useful regression check rather than depending on memory.

References and related reading

Consult the official reference for cursor pagination for exact platform behavior. Continue with a practical debugging workflow and unit testing basics to turn these decisions into repeatable checks.