API Cursor Pagination: Stable Pages While Data Keeps Changing

OFFSET 100000 looks harmless, but the database may still have to find and discard a large number of records before returning a few dozen. While a user moves between pages, new data can also arrive at the front, causing an item to appear twice or disappear from the reading journey.

Cursor pagination, also called keyset pagination, does not ask how many rows to skip. It asks where to continue in a stable ordering. This model fits feeds, transaction histories, audit logs, order lists, and APIs whose data is large or changes frequently.

How do offset and cursor pagination differ?

Offset remains reasonable for small datasets, admin tables requiring numbered jumps, or static reports. Cursor pagination is not the default for every screen; it exchanges random page access for continuity and predictable work.

1. Start with a total order

A cursor is correct only when every record has one deterministic position. Sorting by created_at DESC is insufficient because multiple rows can share a timestamp. Add a unique tie-breaker, usually the primary key:

ORDER BY created_at DESC, id DESC

The pair (created_at, id) creates a total order when id is unique and both values remain stable throughout a record's lifetime. Avoid a mutable field such as updated_at when records moving during traversal would be undesirable.

SQL does not guarantee row order without ORDER BY. Results that appear to follow a primary key in development may change with a different query plan or production dataset.

2. Match the keyset predicate to sort direction

The first page fetches the newest orders:

SELECT id, created_at, status, total
FROM orders
WHERE tenant_id = :tenant_id
ORDER BY created_at DESC, id DESC
LIMIT :limit_plus_one;

If the last record has created_at = 2026-09-19T08:30:00Z and id = 9102, the next page compares the same key set:

SELECT id, created_at, status, total
FROM orders
WHERE tenant_id = :tenant_id
  AND (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT :limit_plus_one;

PostgreSQL compares row constructors left to right. The equivalent expanded predicate is:

created_at < :cursor_created_at
OR (created_at = :cursor_created_at AND id < :cursor_id)

The operator must change with sort direction. Mixed ASC/DESC orders, NULL handling, and expression-based sorts need specific design and tests; do not apply one < rule mechanically.

3. Match the index to filters and ordering

The query is efficient when the database can walk a B-tree index instead of sorting or scanning most of the table:

CREATE INDEX orders_tenant_created_id_idx
ON orders (tenant_id, created_at DESC, id DESC);

tenant_id comes first because every query scopes by tenant; the remaining columns match the cursor order. If the API always filters by status, evaluate another index based on real selectivity and workload instead of adding every filter to one oversized index.

Use EXPLAIN (ANALYZE, BUFFERS) with production-like data. An index that looks correct may still be skipped when a query returns much of the table, statistics are stale, or parameter types do not match.

4. Keep cursors opaque and verifiable

Clients do not need to know that a cursor contains a timestamp and ID. The server can serialize a versioned payload and base64url-encode it:

{
  "v": 1,
  "created_at": "2026-09-19T08:30:00.000000Z",
  "id": 9102,
  "filter": "sha256:..."
}

Base64 is encoding, not security. If cursor manipulation could cross an access boundary or create abnormal queries, sign the payload with HMAC and verify it using constant-time comparison. Do not place sensitive data in a cursor; use authenticated encryption or server-side state when secrecy is required.

The v field allows future format changes. Decoders should cap token size, validate types and dates, and reject unsupported versions with a clear 400 error instead of allowing parser or database failures to become 500 responses.

5. Bind cursors to filters and scope

A cursor generated for status=paid should not be reused with status=pending. Likewise, a cursor from tenant A must never open tenant B's data.

Either include normalized filters and scope in the signed payload or store a filter hash and compare it with the current request. Tenant or user scope must still come from the authenticated principal and always appear in the query's WHERE. Cursor integrity does not replace authorization.

6. Design a client-friendly response

{
  "data": [ ... ],
  "page": {
    "next_cursor": "eyJ2IjoxLC4uLn0.signature",
    "previous_cursor": null,
    "has_more": true
  }
}

Fetch limit + 1 records to determine whether another page exists, then return only limit. This avoids a COUNT(*) on every request. Total counts may be expensive and quickly stale; provide them only when the product truly needs them, perhaps through a separate endpoint or approximation.

Enforce page-size limits on the server, for example 25 by default and 100 maximum. A huge client-provided limit can overload database reads, memory, and serialization.

7. Support previous pages without reversing the UI

To move backward, a cursor commonly carries the first record key of the current page. For a descending display, query records greater than that key in ascending order, limit the result, then reverse it before returning so the UI remains descending:

SELECT id, created_at, status, total
FROM orders
WHERE tenant_id = :tenant_id
  AND (created_at, id) > (:cursor_created_at, :cursor_id)
ORDER BY created_at ASC, id ASC
LIMIT :limit_plus_one;

Do not use one ambiguous token for both directions. Issue separate next_cursor and previous_cursor values or encode and strictly validate direction.

8. What happens when data changes?

Cursor pagination is more stable than offset pagination, but it does not automatically create a snapshot:

• New records at the front: a reader moving toward older data usually is not displaced; they simply have not seen the new items.
• Deleted records: traversal continues from key values without repairing an offset position.
• Changed sort keys: a record may reappear or be missed because it moved.
• Changed filter fields: a record can enter or leave the result set between requests.

Live semantics are often acceptable for feeds. Exports and workflows that must read one immutable set need a snapshot boundary such as created_at <= :as_of, an appropriate transaction snapshot, or a materialized export job. A cursor does not replace isolation.

9. Timestamps, IDs, and NULL values create traps

• Preserve timestamp precision during encoding and decoding; rounding microseconds can skip records.
• Do not assume random UUIDs represent time; use them as tie-breakers, not substitutes for a time key.
• For nullable sort columns, define NULLS FIRST/LAST and matching predicates or use a normalized non-null column.
• Text collation can change ordering across locales or versions; text cursors need a stable contract and careful migrations.
• Avoid floating-point cursor keys when values may be recomputed.

10. Error contracts and observability

An expired cursor, bad signature, filter mismatch, or unsupported version should return 400 with a machine-readable code. Do not expose stack traces or signature details. When an anchor row has been deleted, a valid keyset query can still continue because it uses key values rather than requiring the row to exist.

Monitor latency by estimated depth, rows read versus returned, invalid-cursor rates, page sizes, query plans, and requests hitting the limit. Decoded cursor payloads may contain identifiers; log version, direction, and a shortened hash rather than the entire token.

11. Required test scenarios

1. Records sharing a timestamp appear exactly once because of the tie-breaker.
2. Inserting a new record between requests does not repeat an item on the next page.
3. Deleting the previous page's last record does not break continuation.
4. A cursor changed by one byte is rejected.
5. A cursor from another filter or tenant is not accepted.
6. Moving next and then previous returns the same window and order.
7. Zero, negative, and excessive limits follow the documented policy.
8. Timestamp precision survives an encode/decode round trip.
9. A deep traversal still uses the expected index on a large dataset.

Production checklist

• The order has a unique tie-breaker and stable sort keys.
• The keyset predicate exactly matches order and direction.
• The composite index begins with important scope/filter columns, then sort keys.
• The cursor is opaque, versioned, size-limited, and signed when appropriate.
• Authorization is always enforced independently of the cursor.
• The response uses limit + 1 and caps page size.
• Insert, update, delete, and snapshot semantics are documented.
• Next/previous navigation, filter mismatch, and invalid cursors are tested.

Conclusion

Cursor pagination is not merely the last ID encoded in base64. Production design starts with a total order, a directionally correct predicate, and a matching index; only then come tokens, signatures, filters, bidirectional navigation, and mutation semantics. Done well, an API can traverse millions of records with steadier cost and fewer duplicates or gaps than offset pagination.

References

• PostgreSQL: LIMIT and OFFSET
• PostgreSQL: Indexes and ORDER BY
• PostgreSQL: Row Constructor Comparison