Teams collecting orders, knowledge entries or equipment records often treat reaching the final page as proof of completion. That is insufficient when the data changes between requests. This article addresses a bounded bulk scan: which records it promises to include, how page boundaries remain meaningful and how interrupted work resumes. Continuous synchronization requires an additional change-log boundary.
Define the collection being delivered
An export can mean records and values eligible at its start, a best-effort traversal of changing current records, or every change within a version interval. These are different contracts. The first needs a stable view, the second permits observations from several times and the third normally needs a traceable change sequence.
Freeze tenant and authorization scope, filters, ordering, selected fields, semantic version and data boundary in the task manifest. Record a snapshot identifier or export version. If the interface has no consistency boundary, state that limitation; looping until an empty page does not establish a complete point-in-time snapshot.
Pagination concerns scanning; applying subsequent changes concerns merging state. The existing article on snapshot–incremental boundaries and reconciliation develops that second problem. Here the objective is defensible completeness of one scan. A cursor is neither a cross-system transaction nor a change log.
Unique ordering cannot prevent records from moving between pages
PostgreSQL 17 requires an ordering that uniquely determines rows for LIMIT pagination and notes that large offsets still require computing skipped rows.[1] Ordering only by created_at is usually insufficient because timestamps can tie. Add a stable unique tiebreaker such as id, and define null handling and direction.
Consider a constructed list, not a database measurement: IDs 10, 20, 30, 40, 50 and 60 in ascending order, with three rows per page. The first response contains 10, 20 and 30. Delete 20 before the next request. The current list is now 10, 30, 40, 50, 60; skipping three rows returns 50 and 60, omitting 40.
In a separate scenario, insert 15 after reading the first page. The current list becomes 10, 15, 20, 30, 40, 50, 60. OFFSET 3 returns 30, 40 and 50. ID 30 repeats, while 15 never entered the already-read first page. Deduplication removes the repeated 30 but does not recover 15 or establish which point in time the export represents.
A unique ordering removes arbitrary order within one view. Positions can still change when pages read different views. Adding ORDER BY is necessary in this design but is not a complete consistency strategy. Acceptance must insert, delete and update between page requests instead of merely traversing a static small table.
Keyset pagination replaces a position with a boundary
Keyset pagination continues from the final row's sort values instead of skipping a fixed number of rows. In the ascending-ID example, retain 30 and next read IDs greater than 30. Deleting the already-read 20 no longer moves 40 behind that boundary, so this particular omission disappears.
For ascending created_at followed by ascending id, retain both values. Continue where created_at is later, or where the timestamp is equal and id is greater. Retaining only time loses remaining records with the same timestamp. Mixed directions, null ordering and character collation must agree with the boundary comparison. Prefer non-null, stable sort fields.
Keyset pagination is not a snapshot. An unread record whose sort value moves from after the boundary to before it can disappear from the scan. A previously read record moving forward can appear again. Even immutable IDs do not freeze filter membership or output values: an order changing from unpaid to paid changes eligibility.
An opaque cursor's name likewise proves nothing about consistency. Establish whether it represents sorting position, snapshot position or temporary service state, and document expiry, retry and concurrent-write behavior. Do not decode and modify server cursors. Preserve them verbatim as recovery evidence bound to the original task parameters.
Keep the same data view throughout the scan
PostgreSQL 17 Read Committed gives each query its own view, so consecutive SELECTs can observe different committed states. Within one Repeatable Read transaction, subsequent queries retain the same snapshot.[2] Setting an isolation level while starting a fresh transaction for every page does not create a scan-wide view.
Options include completing the export in a controlled read-only transaction or materializing a versioned export and downloading its parts. The former requires limits on transaction lifetime and resource effects; the latter adds storage, generation, retention and cleanup work. Choose according to volume, maximum interruption and source-system capacity instead of indefinitely extending connection lifetime.
Elasticsearch 8.19 documents that refreshes between search_after requests can change ordering. A point in time, or PIT, preserves the index view while pagination retains the full sort values.[3] This separates continuation position from data view. A fixed search-index state does not prove the source business database has been fully synchronized to that same instant.
A maximum ID or time cutoff is not generally a snapshot either. It can bound new records without freezing changes, deletes or eligibility of existing ones. Lightweight boundaries can work for immutable data with demonstrable write, commit and boundary semantics. Make those prerequisites part of the contract; an auto-incrementing ID alone does not establish commit order.
Advance checkpoints after data commits, not after receipt
Use a separate staging version to retain page data alongside task identity, input cursor, next cursor, view identifier, page sequence, raw count, key-set digest and processing state. A digest of task parameters detects accidental filter or ordering changes. It is not authorization and cannot replace access checks.
Validate response completeness and required fields before committing data and progress. Where the destination supports transactions, write the page and advance its continuation cursor in one transaction. Advancing first can skip an entire page if the process stops before storing its data. Without a shared transaction across storage systems, use reconcilable page-commit records and idempotent replay.
If page data commits before progress is durable, recovery rereads the page. For a fixed view, a task-and-key uniqueness constraint or confirmed page-commit identity can prevent duplicate appends. Do not add replayed page values to aggregates again. Page digests also help detect unexpected changes for the same view and input cursor.
Keep filters and ordering unchanged and use renewed view tokens as required by the service. Elasticsearch, for example, requires using the latest returned PIT ID on subsequent requests.[3] Persist renewed tokens with their corresponding page position rather than permanently retaining the initial token or mixing cursors from different scans.
If the snapshot or cursor expires, silently opening a new snapshot and continuing from the old position does not preserve consistency. Discard an undelivered staging version and restart a complete scan, or use an explicitly supported recovery mechanism. Check delivery state before rebuilding. Keep completed versions separate from partial ones so consumers never see a blended dataset.
Verify membership and content, not just the last page
Follow the documented termination signal. Empty responses, short pages and request failures are not interchangeable with completion; filtering, shard behavior and limits can have different semantics. Retain the terminal response and inspect timeouts, partial results, shard failures and field truncation. Keep the task incomplete if these conditions undermine the contract.
Build a small enumerable reference set and compare all primary keys under a fixed view, rather than only total counts. One missing record and one duplicate can leave the count unchanged. Even with matching keys, compare important fields or record-content digests against the same view to detect mixed-time values.
Include timestamp ties, page size one, an exact multiple of page size, an empty collection, inserts on either side of the boundary, deletes of read and unread records, changing sort fields, changing eligibility, crashes around destination commits and token expiry. Declare expected snapshot or dynamic-view behavior in advance; a newly inserted record is not automatically an omission under every contract.
For large exports, partition counts, key-set digests and business totals can provide quick reconciliation before inspecting mismatching partitions in detail. Matching digests are supporting evidence, contingent on algorithm, scope and shared view. If the source offers no alignable boundary, explicitly state that strict snapshot completeness cannot be established.
Select a strategy and deliver an explainable completion record
A small static collection may use unique ordering with OFFSET. A changing interface needing forward traversal may use keysets with an explicit dynamic-view contract. Reproducible bulk exports should prefer controlled snapshots or materialized exports. Capturing every subsequent update requires a separate recoverable incremental mechanism.
Measure deep-page latency, complete scan duration, replay overhead and resources retained by the view. This article provides no universal page size or speedup. Larger pages can reduce requests while increasing memory, response size and retransmission cost. Smaller pages can make total duration and token-expiry management more demanding.
The completion record should include parameter version, view boundary, committed page count, raw and deduplicated record counts, termination evidence, reconciliation results and unresolved differences. Downloadability, a normally terminated loop, complete membership and usable business metrics are distinct states. Separating them makes collection failures diagnosable before they become untraceable errors in downstream AI analysis.