Paging through data without skipping records

A nightly job syncs contacts from a CRM, 100 at a time:

GET /contacts?offset=0&limit=100
GET /contacts?offset=100&limit=100
GET /contacts?offset=200&limit=100

It runs every night without errors, and yet a few contacts never arrive. The reason is that the data changes while you're paging through it.

#Why offset paging drifts

Say you've read page one, records 1 to 100. Before you request page two, someone deletes record 50. Everything after it shifts up by one, so the record that was 101 is now 100. Your request for "offset 100" starts at the old record 102, and the old record 101 is never read.

Insertions cause the opposite problem: a record shifts down onto the next page and you read it twice. Duplicates are annoying; silently missed records are worse, because nothing tells you they happened.

#Prefer cursors when the API offers them

Many APIs return a cursor, an opaque token that marks your position:

GET /contacts?limit=100
→ { "items": [...], "nextCursor": "eyJpZCI6..." }

GET /contacts?limit=100&cursor=eyJpZCI6...

The cursor encodes where you were, usually the sort key of the last item, so inserts and deletes elsewhere don't move you. If an API offers both offset and cursor paging, use the cursor.

#Sync by modification date, with overlap

For incremental syncs, page by "modified since" rather than reading everything:

• Store the latest modifiedAt you processed.
• Next run, request records modified at or after that time, minus a small overlap such as a few minutes, to cover clock differences and records that were mid-update.
• Make your writes idempotent, an upsert by external ID, so the overlap's duplicates are harmless.

#Building your own API: keyset paging

If you're the one exposing the data, give callers a stable way to page. Keyset paging filters on the last key seen instead of skipping rows:

public async Task<Page<OrderDto>> GetOrdersAsync(DateTimeOffset? afterCreated, Guid? afterId, int limit, CancellationToken ct)
{
    var query = db.Orders.AsNoTracking();

    if (afterCreated is { } created && afterId is { } id)
    {
        query = query.Where(o => o.CreatedAt > created || (o.CreatedAt == created && o.Id > id));
    }

    var items = await query
        .OrderBy(o => o.CreatedAt).ThenBy(o => o.Id)
        .Take(limit)
        .Select(o => new OrderDto(o.Id, o.CreatedAt, o.Total))
        .ToListAsync(ct);

    return new Page<OrderDto>(items, items.Count == limit ? EncodeCursor(items[^1]) : null);
}

Two details make it work:

• Order by a unique combination. CreatedAt alone can have ties; adding Id makes the order total, so no row is skipped or repeated at a page boundary.
• Index those columns. With an index on (CreatedAt, Id), each page is a quick seek, however deep into the data you are. Offset paging gets slower with every page because the database still reads every skipped row.

Encode the last item's values into an opaque cursor string, so you can change the scheme later without breaking clients.

#Takeaway

Don't page through changing data with offsets. Use cursors when an API provides them, sync incrementally by modification date with a small overlap and idempotent writes, and give your own APIs keyset paging on a unique, indexed sort order.