Large-Scale Data Sync in Practice: Pagination Strategies and Resumable Transfer Design

The Real Constraints of Bulk Sync

Historical backfill (three years of orders, 100k+ SKUs) is a different beast from daily incremental sync: data volume is huge, rate limits are tight, and pages are capped (typically 50–200 records), so a full pull can take hours. During those hours anything can fail — network blips, throttling, platform maintenance windows. A sync job without resume capability restarts from zero on every failure.

Comparing Three Pagination Strategies

Offset/page-number paging is simple and universally supported, but records inserted mid-sync shift page boundaries and cause missed or duplicated rows. Cursor paging (continue from the last record's ID or a platform-issued next_cursor) is immune to inserts but depends on platform support. Time-window paging slices by modified_time (e.g. 15-minute windows) and is naturally re-runnable, but it depends on the accuracy of the platform's modification timestamp and needs boundary overlap with dedup.

The robust combination in practice: time windows as the outer strategy, cursor paging within each window — slice the total range into windows, cursor through each one, and commit progress per window.

Resumable Transfer: Progress Is State

The essence of resume is turning "how far we've synced" into persistent, queryable state: use the time window as the unit of progress and persist (task_id, window_start, window_end, status) per completed window; commit data writes and progress updates together (or write data first and tolerate re-runs, protected by idempotent writes); on restart, resume from the window after the last successful one; allow small boundary overlaps (1–2 minutes) deduped by idempotency keys; and make any window manually re-runnable for backfills.

The Incremental Watermark

After backfill, switch to watermark mode: record last_sync_time, pull records with modified_time > last_sync_time, and advance the watermark on success. Advance it to the max modification time seen in the batch, never the server clock, or in-flight records get lost. Rolling the watermark back (e.g. re-run all of yesterday) must be a first-class ops operation, not a database hack. Qeasy's sync tasks run exactly this model: windowed scheduling, idempotent writes, automatic resume, with backfill and incremental sharing one pipeline.