Cursor Pagination in PostgreSQL: A REST API Guide

Pagination becomes a correctness problem when a collection changes between requests. A customer browsing invoices should not see the same invoice twice simply because another invoice was inserted. Large offsets also force databases to walk past increasing amounts of data before returning a small page. Cursor pagination can address both problems when its ordering is designed carefully.

Define the collection before defining the cursor

Start with a concrete endpoint such as an account's invoices, ordered newest first. Decide which filters are supported, whether records can change position, and whether consumers need random page access. Cursor pagination is excellent for sequential traversal, but it does not naturally provide a reliable jump to page 400.

Sketch a response and inspect it in the JSON Formatter. Include an item array and a next cursor, and use null when there is no following page. Avoid exposing database-specific implementation details as fields that consumers will inevitably depend on.

Write down the consistency promise. An ordinary cursor does not create a frozen snapshot across requests. New records may appear before the current position; changed sort values can move records across the boundary. If the feature requires a legally exact export, use a separate snapshot or export workflow rather than implying that browsing semantics provide one.

Choose a stable, unique ordering

Ordering only by creation time is insufficient because multiple rows can share a timestamp. Add a unique tiebreaker, such as an immutable invoice identifier. For descending traversal, both comparisons must follow the same descending order. This produces a total order: every eligible row has a deterministic position.

SELECT id, created_at, total_cents
FROM invoices
WHERE account_id = $1
AND (created_at, id) < ($2::timestamptz, $3::bigint)
ORDER BY created_at DESC, id DESC
LIMIT $4;

The first request omits the boundary predicate. Subsequent requests use the last returned row's timestamp and identifier. PostgreSQL supports row comparisons, but check null handling before applying this pattern to nullable sort columns. Prefer non-null pagination keys or define explicit null ordering and matching predicates.

Use an index beginning with the equality filter and followed by the ordering columns, such as account identifier, creation time, and identifier. Verify the actual plan rather than assuming any index containing those columns is equally useful. Additional filters may need different indexes or intentionally restricted combinations.

Design opaque cursors and page boundaries

A cursor can contain a version, the last ordering tuple, and a fingerprint of relevant filters. Encoding JSON as Base64url makes transport convenient; it does not encrypt the contents or prevent tampering. Do not embed secrets, and never treat a decoded account identifier as authorization.

If tamper resistance matters, authenticate the token using a maintained signing implementation. Validate token length before decoding, then validate the decoded schema and version. Bind the cursor to filters and tenant context so accidentally reusing it on another collection produces a clear input error rather than confusing results.

Fetch one extra row beyond the requested page size. If the caller requests 25 records, query for 26, return the first 25, and derive the next cursor from record 25 only when an extra record exists. Building the cursor from record 26 would silently skip that row.

Clamp page size to a documented maximum and reject invalid values consistently. A missing next cursor means no further results at that moment; it is not a permanent promise that the collection will never grow.

Handle mutations and reverse navigation explicitly

An immutable creation timestamp usually behaves better than an editable title or activity score. If a row's ordering key changes during traversal, it can move behind or ahead of the cursor. Explain this limitation for activity feeds and ranking systems rather than promising perfect deduplication from an ordinary keyset query.

For backward navigation, reverse both the comparison and query ordering, fetch the bounded result, then reverse the returned rows into the public presentation order. Test equal timestamps and page boundaries carefully. Supporting before and after together increases contract complexity; reject combinations you do not intend to support.

Deletion of the cursor's source row should not break traversal when the token stores the ordering values themselves. An implementation that looks up the last row by identifier can fail unnecessarily after deletion. Store enough boundary information to continue without that lookup.

Do not attach a costly exact count to every page by habit. Counts may be stale immediately and can dominate query time. Offer approximate counts, separately fetched counts, or no count depending on the product's actual needs. PostgreSQL's index documentation is a useful starting point for understanding the trade-offs.

Verify performance and authorization together

Create fixtures with repeated timestamps, empty results, a final partial page, and records belonging to different accounts. Walk every page and assert that the expected identifiers appear exactly once under a stable dataset. Then insert and delete records between requests to confirm the documented behavior.

Every page still needs tenant authorization. A signed cursor is not a substitute for checking the current caller's permissions. If access changes halfway through traversal, subsequent requests must use current policy, not the permissions implied by the first page.

Inspect plans with EXPLAIN using realistic account sizes. Compare early and late pages, since keyset pagination's advantage is avoiding growing offsets. Measure endpoint timing with the API Latency Tester, while remembering that a browser timing includes network conditions and cannot identify the database bottleneck alone.

Document cursor expiration, invalid-token responses, and page-size limits in your Backend & APIs reference. Treat cursor format as private, but maintain supported old versions long enough for normal browsing sessions and integrations to finish safely.

Key takeaways

• Use an immutable composite ordering with a unique tiebreaker and a matching index.
• Fetch one extra row, but create the next cursor from the last row actually returned.
• Treat opaque cursors as untrusted input and recheck authorization on every request.
• Distinguish convenient browsing from snapshot-consistent exports and exact counting.