Engineering Practice
API Pagination on Changing Data: Reduce Missing and Duplicate Results
Compare offset and tuple-cursor pagination through completed insertion, deletion and mutable-ordering cases. A second client record shows where retry deduplication helps, why it cannot recover missing records, and how snapshot consistency differs from a continuation token.
For an API list that changes while you read it, choose the endpoint's supported pagination method, preserve a unique stable order, and define what completeness means for the traversal. A supported cursor can reduce repeats and skips caused by shifting positions between offset requests, provided its ordering and continuation contract fit the task. It does not automatically provide a frozen dataset, recover every update or make client processing exactly once.
This LandOffer.ai guide compares concrete insertion, deletion and ordering cases, then works through retry and deduplication boundaries. All records and sequences are original synthetic examples. The stated results came from isolated in-memory SQLite queries and local client simulations, not authenticated Zendesk, PostgreSQL or Elasticsearch execution. No customer data, production API or external account was accessed.

Define which records you expect to retrieve
Before asking a client to “fetch everything,” decide how it should treat new arrivals, deletions and records entering or leaving the filter. Decide whether you need records present at the start, the latest available state, an eventually reconciled collection or an ordered change stream. These are different contracts, and the API may not support all of them.
Zendesk's offset-pagination limitations explain why additions can repeat records and removals can skip them between page requests. Each request evaluates a changing set rather than necessarily reading a stored copy of the original collection. Zendesk recommends cursor pagination where supported and documents other endpoint-specific alternatives.
Check the particular resource before changing parameters. Zendesk's cursor guide describes page[size], returned continuation links or cursors, and meta.has_more; it also warns that some cursor values have resource-specific validity limits. Those names and rules belong to its APIs, not every cursor endpoint.
An endpoint without a suitable snapshot or incremental export may only support a best-effort live scan. Say that explicitly in the result. A client cannot manufacture a trustworthy historical snapshot merely by retaining whichever records happened to arrive across inconsistent pages.
Reproduce a changing list using a deterministic order
The example list has six records: A has sort key 60; B and C both have 50; D has 30; E has 20; F has 10. Sort descending by sort_key, then descending by unique id. The initial order is therefore A, C, B, D, E, F. Pages contain two records, so the first page returns A and C.
The tie is intentional. PostgreSQL 18's LIMIT/OFFSET documentation emphasizes a unique predictable order for selecting subsets. Sorting by a nonunique timestamp alone leaves tied records without the full ordering the client needs. Use the full order to resolve ties, then separately review what happens when rows or their sort keys change between requests.
In the local model, offset pagination asks for the next two positions after skipping two, then four, then six. The keyset model instead continues after the last returned (sort_key, id) pair. Its descending continuation predicate is:
WHERE sort_key < :last_key
OR (sort_key = :last_key AND id < :last_id)
ORDER BY sort_key DESC, id DESC
LIMIT 2
This is the tested model's explicit predicate, not a decoded vendor cursor or a drop-in query for an arbitrary API. For an opaque provider cursor, follow the documented continuation link or parameter instead of constructing its internal contents.
The following completed comparison uses independent runs: each resets the six-record dataset, reads A and C, applies the stated change, then retrieves the remaining pages. It does not apply all mutations sequentially in one run.
| Change after the first page | Offset model's full returned sequence | Tuple-keyset model's full returned sequence | What this establishes in the model |
|---|---|---|---|
| Insert N with key 70, ahead of A | A, C, C, B, D, E, F | A, C, B, D, E, F | Offset repeats C. Keyset returns the original six once, but does not include newly inserted N ahead of its boundary. |
| Delete already-returned A | A, C, D, E, F | A, C, B, D, E, F | Offset skips B even though B still exists. Keyset avoids that skip here, while its combined output still includes A, which has since been deleted. |
| Change unseen B from 50 to 80 and seen A from 60 to 15 | A, C, D, E, A, F | A, C, D, E, A, F | Both traversals omit B and repeat A when the sort field moves records across the boundary. |
The first two rows demonstrate a cursor advantage in these isolated mutations only; they are not measurements of any provider's live consistency guarantees. The third shows its limit when the ordering field itself is mutable. Even the deletion run's six returned IDs do not demonstrate one consistent final database state: A was read before it disappeared.
For tied keys, a timestamp-only predicate of sort_key < 50 would return D and E next, skipping B. Including the ID tie-breaker returns B and D. This result is why the cursor must represent the whole ordering boundary, not just whichever sort field is easiest to expose.
Cursor ordering and snapshot consistency solve different problems
An immutable creation key plus a unique ID often avoids the mutable-field movement demonstrated above, if the endpoint actually supports that order. It still does not preserve deleted records, prevent filter changes or guarantee discovery of new records ordered into the portion of the list already passed. Check the source's guarantees rather than naming every continuation token a snapshot.
Elasticsearch's pagination documentation makes the distinction concrete. Its search_after requests retain the same query and sort values, while refreshes can still change ordering between requests. It describes a point in time, or PIT, for preserving the index state, with appropriate tie-breaking and continuation details. That is a documented Elasticsearch facility, not a promise that another provider's cursor has the same semantics.
In a separate local demonstration, the six initial records are copied into a fixed list before the underlying SQLite table changes. Paging that captured list still yields A, C, B, D, E and F after B is deleted and N is inserted in the live table. This demonstrates fixed membership in a pre-captured local copy. It does not test Elasticsearch PIT, PostgreSQL transaction isolation or an API export.
A real snapshot or export needs a documented creation point, membership definition, retention limit and restart policy. Confirm whether it covers record contents as well as IDs, and what happens when the token expires. For a live sync, a documented incremental endpoint and its update or deletion semantics may fit better than a one-time list scan.
A cutoff such as created_at <= start_time can exclude newer creations if its field and API semantics support that use. It is not, by itself, a snapshot of later deletes, updates or filter membership. Preserve that limitation instead of labeling a cutoff-filtered scan “all records as of start.”

Completed client retry and deduplication record
The second worked asset concerns what the client does with pages, separate from what the server includes. In a local fixed-list simulation, the first page A, C is processed into an ID-keyed output collection. Before its continuation checkpoint advances, a simulated interruption causes the same page to be processed again. The remaining pages then continue normally.
For this separate fixed-list retry case, the received sequence is A, C, A, C, B, D, E, F: eight deliveries, including a replay of the first page. The ID-keyed collection contains six entries, A, C, B, D, E and F, and the local position reaches six. These are in-memory client observations; the simulation does not establish crash durability, a database transaction or exactly-once external side effects.
| Boundary case | Completed observation | Client decision and remaining limit |
|---|---|---|
| Replay after output handling but before checkpoint advance | The fixed first page arrives twice; eight deliveries produce six ID-keyed entries. | Tolerate repeat delivery for this chosen sink operation. Decide how real output persistence and checkpointing coordinate; a dictionary is not a durability design. |
| Deduplicate the mutable-sort traversal | A, C, D, E, A, F becomes A, C, D, E, F. | A duplicate disappears, but missing B is not recovered. Deduplication cannot prove completeness. |
| Same ID can carry a changed representation | The mutation case changes A's sort key before A is seen again. | Define version or update handling for a sync; blindly discarding later occurrences can discard newer information. The example asserts no general merge policy. |
| Repeated continuation token | A guard encounters tokens t1, t2, t2 and stops. | Report an incomplete traversal with the repeated-cursor reason, rather than treating the loop guard as successful completion. |
| Stable query and full ordering boundary | The tuple continuation keeps tied B in the local query. | Preserve the endpoint's filter, sort and cursor context. A different query is a new traversal, not an interchangeable continuation. |
Define the identity used for deduplication as part of the contract. A short ID may be unique only within one resource or tenant, so a combined import may need a key that includes that context. Also state whether the output represents the first version observed, the latest version received or a versioned history. Choosing one silently can produce a clean-looking ID list with the wrong record contents. The six-entry dictionary above demonstrates only one local replacement behavior, not a generally correct update policy.
For an actual client, make the sink behavior explicit. Replacing a stored row by ID is different from charging a card, sending a notification or appending an irreversible event. Pagination deduplication alone does not make such operations idempotent. This example performs none of those external actions.
Likewise, advancing a checkpoint before output is safely handled can lose work after an interruption. Handling output first can replay work if the checkpoint is not saved. Choose an appropriate persistence and retry design for the application, and test its real boundaries separately instead of claiming that a continuation token solves them.
Stop according to the endpoint contract and report gaps
Use the API's documented end signal, not a universal “short page means finished” assumption. In Zendesk's cursor guide, traversal follows has_more and the provided continuation mechanism; it also documents an empty final response after a previous has_more value was true. Preserve the endpoint-specific interpretation, including which cursor may be retained for later use.
Keep request failures, token expiry, repeated cursors and exhausted retry budgets distinct from a normal end. A stopped client should record its last successfully handled boundary and why completeness remains uncertain. Redacting secrets from operational records should not remove the query, ordering and status information needed to diagnose the traversal.
The missing-record conclusions in the tables are possible because the authored example has a known six-ID starting set. A real client usually does not possess that complete reference set before it starts. Logging six distinct IDs is therefore not equivalent to checking the result against a reliable completeness reference. Identify what independent source, server export or documented consistency boundary can support that check, and report when it is unavailable.
For a one-time export, compare the result with the server's supported snapshot or export contract where available. For a continuing sync, plan reconciliation and the documented handling of updates and deletes. Check identities and the documented consistency boundary rather than relying on a matching count; one missing ID and one duplicate can leave the total unchanged.
Before marking a traversal complete, confirm the endpoint's supported order and continuation method, choose the required snapshot or incremental contract, and record the client's replay policy and incomplete-stop reasons. LandOffer.ai has related workplace guides; use the mutation and client-boundary cases here to review your own endpoint contract before declaring a traversal complete.
Sources and Further Reading
- Zendesk: Understanding offset-pagination limitations — changing collections can repeat or skip records; supported alternatives depend on the endpoint.
- Zendesk: Cursor pagination — continuation parameters, end signals, empty final responses and resource-specific cursor validity.
- PostgreSQL 18: LIMIT and OFFSET — unique predictable ordering is necessary when selecting subsets; this article's execution used isolated SQLite, not PostgreSQL.
- Elasticsearch: Paginate search results —
search_after, tie-breakers and point-in-time consistency; no Elasticsearch account or cluster test was performed.


