Our ERP’s API returned different results for the same query. Here’s how we dealt with it.
This article was originally published on LinkedIn and is archived here on texlytics.com.
Run the query at 9:00 AM. You get 4,812 rows.
Run the exact same query at 9:01 AM. You get 4,797.
Run it a third time and you get 4,812 again — the exact count you started with. Relief. Except that run is the most dangerous of the three, and I’ll come back to why.
No data changed. No filter changed. Same endpoint, same parameters, same everything. Fifteen rows just evaporated, and they were different fifteen rows than the ones that went missing the time before.
That should not be possible. Pagination is supposed to be the most boring thing in the entire integration. It turned out to be one of the most interesting bugs I chased this year.
What pagination is supposed to do
If you have never had to think about this, here is the plain-language version.
When an API has a lot of records to hand you, it does not dump all of them in one response. It gives them to you a page at a time. You ask for records 1 through 100, then 101 through 200, then 201 through 300, and you staple the pages together to rebuild the full set.
It is like reading a book by tearing it into 100-page chunks. The whole thing only works if the page numbers are fixed — if page 101 always starts exactly where page 100 left off. You trust the seams.
Our ERP’s REST API was re-shuffling the book between requests. Page 101 did not start where page 100 ended. Sometimes a record showed up on two pages. Sometimes it fell straight through the crack between them and never appeared at all. And because the shuffle was different every time, the cracks landed in different places on every run.
So our nightly extract was quietly, intermittently incomplete. Nothing errored. Nothing logged a failure. The row counts just wobbled.
Why a paginated query goes non-deterministic
Here is the technically honest reason, and it is worth understanding because it is not unique to one vendor.
Offset-based pagination — OFFSET n LIMIT m — only returns a stable result if there is a total, stable ordering of the rows underneath it. The moment the sort key is not unique, or there is no enforced sort at all, the database is free to break ties however it wants on each execution. Different query plan, different buffer cache state, a parallel scan instead of a serial one — any of these can change the order rows come back in.
Think of it like this. Picture a crowd told to line up by height, where dozens of people happen to be exactly the same height. Nothing decides the order among the equally tall ones, so every time you ask the crowd to line up again, those people settle into a different order. Now ask for the people standing 201st through 300th. You get a different group each time — not because anyone grew or shrank, but because the ties landed differently. The records are the crowd; offset 200 is just where you start counting.
When the order changes, the page boundaries cut the dataset in different places. Offset 200 is no longer a stable window into a stable list. It is a window into a freshly-shuffled list. That is the entire bug in one sentence.
The textbook fix is keyset pagination (also called the seek method): instead of “give me rows 201 to 300,” you say “give me the next 100 rows where the ID is greater than the last one I saw,” ordered by a unique, monotonic key.
In plain terms, page numbers assume the book never gets re-paginated between readings. A bookmark makes no such assumption — it just says start from the last line I actually read and keep going. Keyset pagination is the bookmark; offset pagination is the page number.
— fragile: offset into an unstable ordering
SELECT * FROM records ORDER BY updated_at OFFSET 200 LIMIT 100;
— stable: seek past the last key you actually received
SELECT * FROM records WHERE id > :last_seen_id ORDER BY id LIMIT 100;
Keyset pagination is the right answer. There is just one problem: it requires the server to expose a stable key and to honor your ordering and filter parameters. Ours did not. The filter parameters we passed were not respected, and there was no cursor to seek on. We did not control the server, and the server was the thing that was broken.
That is the real lesson buried in this whole story: when you cannot fix the server, you have to engineer around its pathology instead of through it.
When the count is right and the data is wrong
Missing rows are the polite version of this bug. The counts wobble, something looks off, and eventually you go digging.
The mean version is the one where the count comes back identical and you stop looking.
Go back to the book analogy. When the page boundaries shift, two things happen at the same time. A record that sat at the tail of one page can reappear at the head of the next — a duplicate. And a different record gets skipped in the reshuffle and never lands on any page at all — a drop. One row double-counted, one row dropped. The net count does not move.
In everyday terms: a teacher counts thirty heads boarding the bus and relaxes. The count is right. But two kids got counted twice, and two are still back in the gift shop. Thirty on the clipboard, twenty-eight on the bus. A headcount that matches is not the same thing as the right kids being on board.
So you can run the query twice, get 4,812 rows both times, and feel safe — except 500 of those rows are duplicates of records you already have, and 500 distinct records you actually needed never showed up. The total matched perfectly. The contents were quietly corrupt.
This is the dangerous one, because the cheapest sanity check anyone ever writes — “did the row count come back the same as last time?” — passes with flying colors. Monitoring is green. The dashboard is wrong. The only check that catches it is counting distinct business keys, not rows.
That split is also the shape of the fix, and it comes in two halves: something has to strip the duplicates back out, and something has to go find the records that fell through the cracks. Hold that thought.
First fix: stop trusting one pass
If a single sweep through the pages randomly misses a different slice every time, then no single sweep is ever going to be complete. So we stopped pretending one sweep would be.
Instead, we run the full pagination sweep multiple times and take the union of everything we got, deduplicated on a stable business key. That one sentence pulls double duty against the two-part corruption from a moment ago: the dedupe collapses the spurious duplicates, and the union across passes recovers the distinct rows any single pass dropped.
The reasoning is probabilistic. Call the true, complete set of matching records “T.” Any single sweep returns some subset of T — a different subset each time. But if every record has a decent chance of showing up in any given sweep, then running three independent sweeps and unioning them drives your coverage way up. A record only goes missing from the final result if it managed to dodge all three sweeps, and that is a small number times a small number times a small number.
Picture it this way: it is like searching a dark room for coins you dropped. Any single sweep of your hand misses a few, and a different few each time. Sweep the floor three times, combine everything you found, and you have almost certainly got them all.
def fetch_all(endpoint, params, dedupe_keys, max_passes=3, page_size=100):
seen = {} # business_key -> record (first one wins, dedupe is implicit)
for pass_no in range(1, max_passes + 1):
# IMPORTANT: open a fresh session per pass (more on why below)
session = new_session()
added_this_pass = 0
offset = 0
while True:
page = api_get(session, endpoint, params, offset=offset, limit=page_size)
if not page:
break
for record in page:
key = make_key(record, dedupe_keys)
if key not in seen:
seen[key] = record
added_this_pass += 1
offset += page_size
log(“pass {}: total_unique={} new_this_pass={}”.format(
pass_no, len(seen), added_this_pass))
if added_this_pass == 0:
break # union has stabilized — another pass would add nothing
return list(seen.values())
Two details in that snippet are doing real work:
- Dedupe on a business key, never on row position or object identity. We use a configured set of key columns as the single source of truth for what makes a record unique. Position is meaningless here — the whole problem is that position is unstable.
- The stop condition. If a pass adds zero new keys, the union has converged and you can quit early. Log new-versus-total on every pass so you can actually see convergence happening instead of cargo-culting “always run three times.”
This worked. Completeness went from “wobbly” to “rock solid.” We shipped it and moved on.
Except the why was wrong, and the wrong why is the interesting part.
The twist: there was a meter we couldn’t see
We thought the multi-pass union was beating randomness. It was not. Or rather, it was, but that was a side effect, not the mechanism.
When we captured the raw HTTP responses and diffed them pass to pass, the missing rows were genuinely different each time — that part checked out. But something else was suspicious: no matter how many records actually matched the query, a single uninterrupted pagination sweep capped out at almost exactly the same total volume every time before the pages started coming back short and then empty.
For example: one endpoint had roughly 12,000 matching records, another had roughly 40,000. Both sweeps died in almost exactly the same place — somewhere around 8,000 rows in — even though one had more than three times the data. And the pages did not stop cleanly: a few full pages of 100, then a page of 60, then a page of 20, then empty. The ceiling was tracking total volume pulled over the connection, not the number of records that actually matched.
The cap was not “100 rows per page.” It was a cumulative ceiling on how much the API would hand you over a single connection before it quietly throttled the well to a trickle.
The limit was not in the API documentation. It was not in the application at all. It was being enforced one layer up, at the nginx reverse proxy sitting in front of the API. There was an undocumented cumulative budget per connection, and once you spent it, the upstream stopped feeding you — with no error, no header, no warning. Just short pages and then nothing.
Plain-language version: imagine a buffet where the rule is not “one plate at a time.” The real rule is a hidden total — after you have been handed a certain amount of food across the whole meal, the kitchen quietly starts sending out empty plates and nobody tells you the kitchen closed.
That reframed our fix completely. The three passes were not just rolling the dice three times against a shuffle. Each new pass opened a fresh connection, which reset the proxy’s cumulative budget. We were not out-running randomness. We were resetting an invisible meter. That is why a brand-new session per pass — not just a new loop — is load-bearing in the code above. Reuse the connection and you keep spending the same depleted budget; you get three identical truncated results and the union buys you nothing.
What I’d hand to the next person who hits this
- Offset/limit pagination without a guaranteed total ordering is a latent bug, not a working feature. It will look fine in testing and rot quietly in production. If you own the server, use keyset pagination.
- When you do not own the server, treat completeness as probabilistic. Multi-pass union, dedup on a stable business key, and an explicit stop condition is a legitimate, defensible pattern — not a hack.
- Gateways lie by omission. The documented API contract is not the whole contract. Proxies, rate limiters, and load balancers impose limits nobody wrote down. Instrument cumulative volume across a sweep, not just per-page row counts, or you will never see the ceiling.
- Connection state matters more than you think. A “retry” that reuses an exhausted connection is not a retry. If a server-side budget is per-connection, your reset has to be per-connection too.
- Log new-versus-total per pass. It turns “I hope this is complete” into “I can prove this converged.”
- A matching row count is not a completeness check. Duplicates can mask drops one for one, so two runs can agree on the total and still disagree on the contents. Count distinct business keys, not rows.
Most integration tutorials assume the API on the other end is deterministic and honest about its limits. Plenty of real-world enterprise APIs are neither. The fix is rarely elegant. But “run it until the union stops growing, and open a fresh connection each time” has quietly become one of the most reliable patterns in our entire extraction layer.
A postscript: the vendor shipped the fix
There is a happy ending. As of May 30, the vendor shipped a documented fix for the exact root cause this whole article is about: a first-class orderBy=default parameter that pins pagination to the default sort order defined in the underlying database view — the stable, total ordering the endpoints were missing all along. It slots in alongside finders, the q filter, field selection, and ordinary limit/offset paging, and it is effectively the server-side version of the keyset discipline I wish I could have applied from day one.
Two honest caveats. It fixes the ordering instability, not a gateway-level volume ceiling — if a proxy is still metering cumulative responses, the fresh-connection-per-pass habit keeps earning its keep. And the rollout is incremental; not every view supports it yet. So the multi-pass union pattern is not obsolete — but where orderBy=default is available, it is the better tool, and I am retiring workarounds endpoint by endpoint as coverage expands.
The broader lesson holds: keep the escalation channel open. The thing you have to engineer around today is sometimes the thing the vendor ships weeks later. When they do, go back and delete your scar tissue.
Reference: Best Practice | New orderBy parameter for API sorting
Has your “boring” pagination ever turned out to be non-deterministic? I am curious what the root cause turned out to be on the other end — unstable sort, concurrent writes, or a gateway nobody told you about.