The Paginated API That Lost Records Between Pages

The ticket said "data sync is missing records." No error logs. No failed requests. The sync job ran every night at 2 AM, pulled customer records from the internal API, and pushed them into a reporting warehouse. It had been running for months. Then someone ran a reconciliation query and found 1,400 records in the source database that didn't exist in the warehouse.

Not old records. Not archived ones. Active customers with recent orders, scattered across every letter of the alphabet. No pattern anyone could see.

The sync looked fine

I started where the data moved: the sync job itself. It was a Python script that called a paginated REST endpoint, looped through every page, and inserted records into the warehouse. Straightforward stuff.

offset = 0
page_size = 500
 
while True:
    response = requests.get(
        f"{API_URL}/customers?offset={offset}&limit={page_size}&sort=created_at"
    )
    records = response.json()["data"]
    if not records:
        break
    warehouse.bulk_insert(records)
    offset += page_size

The API behind it was equally simple. A PostgreSQL query with OFFSET and LIMIT, sorted by created_at. The kind of pagination you see in every tutorial.

SELECT * FROM customers
ORDER BY created_at ASC
LIMIT 500 OFFSET :offset;

I ran the sync manually against a test copy of the database. Every record showed up. I ran it three more times. Perfect every time.

So I did what I should have done first: I ran it against production.

The off-by-some error

The production database had about 340,000 customer records. The sync took around twelve minutes to page through all of them. During those twelve minutes, the application was still running. Sales reps were creating new customers. The CRM integration was updating existing ones. Records were being inserted, modified, and occasionally soft-deleted.

Here's what happens with offset pagination when the underlying data changes.

Say you're on page 3 (offset 1000, limit 500). You've already synced records 1 through 1500, sorted by created_at. Now, before you fetch page 4, a new customer gets created with a created_at timestamp of yesterday — maybe it was imported from a CSV, maybe the sales team backdated it. That record slots into position 800 in the sort order.

Every record after position 800 shifts forward by one. Record 1501, which should have been the first record on your next page, is now record 1502. But record 1500, which you already synced, is now at position 1501. You'll fetch it again. And the record that was at position 1501 — the one you needed — just got pushed to position 1502 on the next page.

You didn't lose it entirely. But if this keeps happening, and in a database with active writes it will, records slip through the cracks between pages. Some get fetched twice. Some get fetched zero times.

How many records you lose depends on how busy the database is

I wrote a script to simulate concurrent writes during a full pagination run. With the production write rate — roughly 40 inserts and 200 updates per minute during business hours — the simulation dropped between 0.3% and 0.8% of records per run. On 340,000 records, that's 1,000 to 2,700 missing rows. The nightly sync ran at 2 AM when write volume was lower, which is why the actual loss was on the lower end. But it was still losing records every single night.

The previous reconciliation hadn't caught it because it ran quarterly, and somebody had been manually re-syncing the missing records as "one-off fixes" without asking why they were missing in the first place.

Cursor-based pagination fixes this

The fix was switching to cursor-based pagination. Instead of saying "give me rows 1000 through 1500," you say "give me the next 500 rows after the last one I saw."

cursor = None
page_size = 500
 
while True:
    params = {"limit": page_size, "sort": "id"}
    if cursor:
        params["after"] = cursor
    response = requests.get(f"{API_URL}/customers", params=params)
    records = response.json()["data"]
    if not records:
        break
    warehouse.bulk_insert(records)
    cursor = records[-1]["id"]

The API query uses a WHERE clause instead of OFFSET:

SELECT * FROM customers
WHERE id > :cursor
ORDER BY id ASC
LIMIT 500;

Because the cursor is the primary key of the last record you saw, inserts and updates elsewhere in the table don't shift your position. You always pick up exactly where you left off. The database also doesn't have to count and skip rows, so deep pages get faster instead of slower — offset 300,000 means PostgreSQL has to scan and discard 300,000 rows before returning your 500.

We deployed the cursor-based version on a Thursday. The Friday morning reconciliation came back clean for the first time in months.

Offset pagination isn't wrong, but it has a shelf life

For a UI where a human is clicking through 20 results at a time, offset pagination is fine. The data might shift between page views, but nobody's going to notice or care that they saw the same listing twice.

For machine-driven processes that need to reliably walk through an entire dataset — sync jobs, data exports, migrations, ETL pipelines — offset pagination is a trap. It works perfectly in development, passes every test, and quietly breaks in production under real write load.

The frustrating part is that this was a known problem. The backend team had even discussed cursor pagination during the original API design. They went with offset because it was simpler to implement and "we can always switch later." Later turned out to be after 1,400 records went missing and a quarterly audit flagged the discrepancy.

If your API is the source of truth for any downstream system, check how it paginates. You might be losing data right now and not know it yet.