Pagination breaks a large export into manageable reads, but a sequence of successful page requests does not prove that the final file is complete. Records can move between pages as data changes, several records can share the same sort value, and a retry can append the same page twice. Treat pagination as part of the export contract. Define the order, the dataset being traversed, the meaning of the continuation token, and the recovery behavior before relying on a loop that simply requests the next page.
Define the exported record before its cursor
Begin with the logical unit of output. An order export might contain one row per order, while a shipment export might contain one row per shipment line. If the query joins orders to items, an order identifier is no longer necessarily unique in the result. The pagination key must identify the actual exported record, perhaps using the order identifier together with the item identifier. Otherwise a page boundary can split records that share the same parent and leave the continuation rule ambiguous.
Write down the fixed filters as well: organization, reporting interval, record status, requested columns, and any visibility scope. Every page must use the same interpretation of those inputs. A client that changes the status filter halfway through a traversal is starting a different query even if it retains the cursor. The data export API fundamentals guide explains why these dataset definitions belong in the public contract rather than remaining hidden inside the implementation.
Use an order that uniquely identifies position
The PostgreSQL documentation on LIMIT and OFFSET explains that retrieving subsets requires a predictable order and that the ordering should uniquely constrain the result. It also notes that large offsets can be inefficient because skipped rows still require work. These are useful foundations for export design. A sort on creation time alone is insufficient when several records have the same timestamp; add a stable unique tie-breaker that makes their relative positions unambiguous.
For example, suppose three records share the same creation time and have identifiers A, B, and C. If a page ends after B, the next page must still include C. A continuation rule that asks only for records with a later timestamp skips it. Use the full ordered position: later creation time, or the same creation time with a later identifier. The identifier's ordering must be consistent with the database query, including collation where text identifiers are involved.
Understand what offset pagination can change
Offset pagination names a position by counting how many rows to skip. It can work well for a small, fixed dataset or a materialized result that does not change during traversal. Consider a five-record result ordered A through E, fetched two records at a time. After reading A and B, deleting A shifts the remaining positions. Skipping two rows in the changed dataset begins at D, so C is missed. Inserting a new record before the current position can instead repeat a previously returned record.
This is not solved by making the page size larger. A larger page reduces the number of requests but leaves the underlying movement problem. If offset pagination is already part of an upstream API, investigate whether the provider offers a snapshot token, immutable result set, or export endpoint. When none exists, state the consistency limitation and design reconciliation around it. Do not label a best-effort traversal as a point-in-time snapshot merely because each individual page has a successful response status.
Use keyset pagination with an explicit contract
Keyset pagination resumes after the values of the last returned ordering key. In the creation-time example, the continuation token represents both the timestamp and identifier. The next query applies the same filters and ordering, then selects records strictly after that pair. This avoids relying on the number of earlier rows that still exist. Use an index appropriate to the actual filter and order, and inspect query behavior on representative data before assuming the approach will be efficient.
Handle nulls and mixed sort directions deliberately. If a sort field can be null, specify whether those records appear first or last and how the continuation condition crosses that boundary. Keep the cursor opaque to clients so the internal representation can evolve. Opaque does not automatically mean encrypted or secure: validate or authenticate token contents, bind them to the request scope, and recheck authorization on every page. A token must not grant access to another organization's query merely because its structure is difficult to guess.
Separate stable traversal from a stable dataset
Keyset pagination does not freeze data. If the ordering field changes after a record is read, that record can move ahead of the cursor and appear again. A previously unseen record can move behind the cursor and be missed. Prefer immutable ordering fields where practical, but also choose a consistency guarantee for the record values themselves. A reading may keep its identifier and timestamp while its corrected value changes between page requests. Traversal order and historical truth are separate requirements.
For a strict point-in-time export, use a source mechanism that actually preserves the selected view, such as a retained snapshot or a materialized export dataset. In PostgreSQL, separate statements under Read Committed can observe different committed data; a repeatable-read transaction provides a stable transaction view, but opening a new transaction for each HTTP request does not preserve the previous one. Long-running snapshots have operational consequences, so select the mechanism with the database team and define how expiration affects unfinished exports.
Treat time bounds as filters, not snapshots
A captured upper timestamp can keep newly created records beyond that boundary out of an export. It does not automatically protect against backdated inserts, corrections, deletions, or mutable timestamps. Suppose a reading is inserted late with yesterday's event time after the export has already passed that part of the ordering. The timestamp filter includes it logically, but a live forward traversal may never see it. Decide whether late arrivals belong in a correction export, a later processing window, or a genuinely frozen dataset.
Similarly, an identifier cutoff is only meaningful when the identifier's allocation and visibility semantics support it. Random identifiers do not provide a time sequence, and an allocated sequence value is not necessarily the order in which transactions become visible. Avoid deriving a snapshot guarantee from a field name. For incremental exports, distinguish event time, update time, and ingestion time, then choose the one that matches the receiving workflow. Deletions may require explicit change records because a current-state query cannot return a row that no longer exists.
Make retries safe at the output boundary
Save progress only after the corresponding output has reached a durable boundary. If a client writes a page and crashes before saving its next cursor, it may fetch that page again. A simple append then duplicates records. Use a documented recovery strategy: replace a page-specific chunk, reconcile stable record identifiers, or restart the export from its fixed input. Choose based on the output format and consistency model. Deduplication must use the complete exported-record key, not a parent identifier shared by legitimate rows.
Keep the cursor, snapshot identifier, schema version, and output checkpoint together. If a continuation token expires, do not resume against a different dataset while keeping the original file label. Either restart under a new export identity or provide an explicit reconciliation process. The asynchronous bulk export guide covers durable attempts and publication. Parallel extraction needs equally clear partition boundaries; independently fetching neighboring pages without coordinating their cursors can undermine both ordering and recovery.
Verify completion rather than guessing it
Follow the API's documented termination signal. A short page does not universally mean the traversal is finished, and an empty page can be meaningful in some continuation protocols. Check repeated cursors, unexpected scope changes, duplicate record keys, and missing required fields. Compare counts or totals against the same dataset snapshot when available. The export validation guide develops these checks. Use fixtures with tied timestamps, deletions, late inserts, and a failure immediately after a page is written.
The aim is an export whose traversal can be explained and whose completeness can be tested. Stable ordering locates records, an explicit consistency model defines the intended dataset, and durable output checkpoints govern recovery. ExportAPI.com's local samples help explore these mechanics; any production snapshot and storage guarantees must come from the systems you connect. Make each promise explicit, especially before describing an export as containing every row exactly once.
