Implementing Offset and Cursor Pagination in Harvester Query Interfaces

Desired Outcome

Implement a pagination mechanism that lets a Java‑based Harvester client retrieve all rows from a large table without exceeding memory limits or introducing duplicate/missing records.

Prerequisites

• A Harvester endpoint that accepts limit and offset (or a cursor filter) parameters.
• A column that is unique and indexed (e.g., record_id BIGINT) to serve as a cursor.
• A persistent store (file, database table, or distributed cache) to save the last processed offset or cursor between runs.
• Java 8+ with a HTTP client (e.g., HttpURLConnection or Apache HttpComponents) and JSON parsing library.

Strategy 1 – Offset‑Based Pagination

Procedure

1. Choose a page size that fits comfortably in memory (e.g., pageSize = 500).
2. Initialize offset = 0.
3. Build the request URL: ${HARVESTER_ENDPOINT}?limit=${pageSize}&offset=${offset}.
4. Execute the GET request with read‑only credentials.
5. Parse the JSON array; if the array length is zero, stop.
6. Process each record as needed by your application.
7. Update the offset: offset += pageSize and persist the new value.
8. Repeat from step 3.

Expected Checks

• After each page, confirm that the number of returned records ≤ pageSize.
• When the returned count is pageSize, continue; when it is less, you have reached the end.
• Optionally, keep a running total of records and compare it to a separate count query (SELECT COUNT(*) FROM table) after the harvest finishes.

Recovery

If the process crashes, read the persisted offset value and resume the loop from that offset. Because offset‑based pagination assumes a static dataset, verify that no inserts or deletes occurred in the already‑processed range; if the source table is volatile, prefer the cursor method.

Strategy 2 – Cursor‑Based Pagination

Procedure

1. Identify a monotonic, unique column (e.g., record_id) and ensure it is indexed.
2. Set an initial cursor value lower than any possible key (commonly 0 or the minimum value).
3. Build the request: ${HARVESTER_ENDPOINT}?limit=${pageSize}&cursor=${lastSeenId} where the Harvester interprets cursor as WHERE record_id > ${lastSeenId}.
4. Execute the request with read‑only access.
5. Parse the response; if empty, stop.
6. Process each record.
7. Extract the record_id of the last record in the batch and persist it as the new cursor.
8. Repeat from step 3.

Expected Checks

• Verify that the record_id of the first record in a page is strictly greater than the cursor used to request that page.
• Ensure no duplicate record_id appears across pages by keeping a Set of seen IDs (optional for small validation runs).
• When a page returns fewer than pageSize records, the harvest is complete.

Recovery

On failure, read the persisted cursor (lastSeenId) and restart the loop. Because the cursor always points to the last successfully processed key, inserts that occur with lower IDs will not be missed, and inserts with higher IDs will be picked up in subsequent pages, eliminating skip/duplicate risk.

Validation Example (offset‑based)

To confirm correct behavior on a test dataset of 2 350 rows:

1. Set pageSize = 500.
2. Run the offset loop and record the offset after each iteration: 0, 500, 1000, 1500, 2000, 2500.
3. Observe that the fifth page returns 350 rows (less than pageSize) and the sixth page returns zero.
4. Sum the rows returned per page (500+500+500+500+350) = 2 350, matching the known total.
5. No offset value is repeated, indicating no overlap.

For cursor‑based validation, persist the last record_id after each page and re‑run the query with that cursor; the result set should start with the next higher record_id and contain no overlap with the previous batch.