Offset Pagination: Simple, Jumpable, and Lying Under Writes
?page=3&limit=50 is the easiest pagination to build and consume, and it makes two quiet promises it cannot keep at scale: that deep pages are as cheap as shallow ones, and that page boundaries hold still while the collection changes.
Frame the contract
API design starts with a consumer, a design question and a guarantee — never with a URL.
What the database does with OFFSET 100000
Offset pagination inherits its semantics from SQL's LIMIT … OFFSET …, and its cost model too. OFFSET 100000 LIMIT 50 does not teleport to row 100,001 — there is no index on "being the 100,001st row", because that property changes with every insert. The database walks the ordering index from the start, discards 100,000 entries, then returns 50. Page 1 reads 50 entries; page 2,001 reads 100,050 and throws away 99.95% of the work. Cost is O(offset), and your latency curve says so: flat for the first pages, then climbing linearly into timeout territory.
This cost profile has a specific production signature: the endpoint is fast for every human user (humans live on pages 1–3) and is eventually discovered by a machine — a scraper, a sync script, a well-meaning nightly export — that walks all N pages. The deep pages each cost a near-full index scan, the last ones cost the most, and a single paginating client generates load equivalent to thousands of shallow requests. The contract invited this: it priced every page the same while the database priced them linearly.
The honest fixes are contractual, not clever SQL. Cap the depth (max_offset, or equivalently a maximum page number) and document it — this is what search engines do; nobody browses to result 100,000. Give traversal-shaped consumers a real alternative (a cursor mode, or an export endpoint) so the cap is a redirect, not a wall. And keep limits enforced server-side: ?limit=100000 is just the same problem rotated ninety degrees.
SELECT * FROM orders ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET :off;
page 1 OFFSET 0 → 50 index entries read
page 21 OFFSET 1,000 → 1,050 read, 1,000 discarded
page 2,001 OFFSET 100,000 → 100,050 read, 100,000 discarded
page 20,001 OFFSET 1,000,000 → 1,000,050 read — seconds, not ms
Same page size for the client. ~20,000x the work for the database.
Contract fix: max_offset 10,000 → 400 offset_out_of_range
+ "use cursor mode / the export API beyond this"Drift: page boundaries do not hold still
The second broken promise is correctness under concurrent writes. "Page 2" means "rows 51–100 *of the collection as it exists at query time*". If one new row is inserted at the top of the ordering between your page-1 and page-2 requests, everything shifts down by one: the row that was #50 (the last row of your page 1) is now #51 — the first row of your page 2. You see it twice. If a row on page 1's range is deleted instead, everything shifts up, and the row that would have led page 2 slides into page 1 *after you already fetched it*. You never see it at all.
For a human clicking through an admin table, a repeated row is a shrug. For anything that aggregates or syncs, drift is silent corruption: the nightly job that mirrors your API into a partner's warehouse double-imports some records and skips others, with no error anywhere — every individual request succeeded. On a busy collection (an events table inserting hundreds of rows per second at the head of a created_at DESC ordering), *every* page boundary drifts, and the skips concentrate exactly where the newest, most interesting data is.
Drift cannot be fixed inside the offset model — it is what "position as arithmetic" means. It can be *bounded*: order by something append-stable (created_at ASC drifts only at the tail, not through the whole traversal), keep pages large and traversals short, or snapshot the result server-side for the duration of a session. But each of these is a workaround wearing the costume of a fix; consumers that need every-record-exactly-once semantics need a cursor anchored to the ordering key, which is precisely the promise Cursor Pagination: An Opaque Bookmark, Not a Position exists to make.
1ORDER BY created_at DESC (new rows enter at the top)2 3T0 GET /orders?page=1&limit=3 → [O9, O8, O7]4T1 two orders created: O10, O115T2 GET /orders?page=2&limit=3 → [O8, O7, O6]6 ^^^^^^^ seen again7# and had rows been deleted instead,8# O6 would have slid into page 1 unseen — a gap1ORDER BY created_at DESC, id DESC2 3T0 GET /orders?limit=34 → [O9, O8, O7] next_cursor = after(O7)5T1 two orders created: O10, O116T2 GET /orders?cursor=after(O7)&limit=37 → [O6, O5, O4] # no repeats, no gaps:8 # new rows land before the9 # frontier, never inside itThe offset request asks for a *position*, which the writes just redefined. The cursor request asks for *rows after a known row*, which writes cannot redefine. Same data, same ordering — the difference is what the client's "where was I?" refers to.
Where offset is still the right call
None of this makes offset wrong — it makes it specific. Offset is the correct choice when its two real strengths are the actual requirement: random access (jump to page 40, parallel-fetch pages 1–10 for a report) and positional UI ("page 7 of 132", which needs both arithmetic positions and a total count). It is also fine, honestly, for small and slow-moving collections — a /team-members list with 60 rows will never hit the cost curve or meaningful drift, and the simpler client is a genuine benefit (see Design Principles Without Commandments on not over-engineering ties).
The design failure is not choosing offset; it is choosing it *by default* and letting its costs arrive as surprises. A deliberate offset contract states the depth cap, enforces the limit, documents that page contents can shift under writes (one sentence saves a partner a reconciliation project), and prices the total count honestly — COUNT(*) over 50 million rows can cost more than the page query, which is why large APIs return has_more instead of totals, or cache an approximate count. Each clause is cheap; their absence is how "the easy pagination" becomes the expensive one.
- Choose offset for: jump-to-page UIs, parallel range fetches, small or slow-moving collections.
- Cap depth explicitly (
max_offset) and point deep traversals at cursors or an export API. - Enforce
limitserver-side with a documented default and max. - Document drift in one sentence: "page contents may shift if the collection changes between requests."
- Price totals honestly:
has_moreor an approximate count whenCOUNT(*)is expensive.
Key points
- OFFSET is skip-and-discard: page cost is O(offset), so deep pages cost thousands of times more than shallow ones — flat for humans, linear into timeouts for crawlers.
- Page boundaries drift under concurrent writes: inserts cause repeats, deletes cause silent gaps — corruption for sync jobs, a shrug for humans.
- Drift is inherent to position-as-arithmetic; it can be bounded but only cursors remove it.
- Offset earns its place where random access or positional UI is the real requirement, and on small, slow-moving collections.
- A deliberate offset contract caps depth, enforces limits, documents drift, and prices totals honestly.
- Watch for the machine consumer: the deep-walking script is the load pattern offset invites and humans never produce.
Pagination Under Concurrent Writes
Change the contract and observe which guarantee moves.
—
Offset re-counts from the top every request, so each insert shifts the window back over rows the client already saw. The cursor names a position in the ordering, so new rows above it are simply ignored.
Follow the failure
How the contract fails or gets misused, hop by hop — and what it costs when it completes.
- 1Team → API: ships
?page=&limit=on every collection — it matched the SQL and the admin UI mockup. - 2Partner → API: builds a nightly full-sync by walking pages 1 through N; N is 400 and growing.
- 3Collection → scale: N hits 20,000; the last pages each scan a million index entries; the nightly sync now owns the database's worst hour.
- 4Concurrent writes → sync: boundary drift double-imports and skips records; the partner's warehouse quietly diverges from the source.
- 5Partner → provider: files "your API loses data"; the reconciliation meeting discovers the contract never said what a traversal observes.
- Database load concentrates in deep pages: one paginating machine consumer generates scan volume that dwarfs all human traffic.
- Completeness-dependent consumers silently corrupt: double-imported and skipped records surface in audits, weeks later, as trust damage.
- Latency SLOs break nonlinearly: p50 looks healthy (humans, shallow pages) while p99 is owned by deep offsets — the average hides the failure.
Design, observe, evolve
A contract decision is incomplete until you know how you would notice it failing and how it changes later.
- • Bound the mechanism: enforced default and max `limit`, a documented `max_offset`, and a stable total ordering with a unique tiebreaker (see [[sorting]]).
- • Provide the escape hatch beside the cap: cursor mode or an export endpoint for traversal-shaped consumers, named in the `offset_out_of_range` error itself.
- • Document drift semantics explicitly so completeness-sensitive consumers self-select out of offset before they build on it.
- • Return `has_more` by default; make exact totals a separate, deliberately-priced endpoint if the product truly needs "page 7 of 132".
- • Histogram requests by offset value: a bimodal shape (humans at 0–150, a machine at 100,000+) is the deep-walker signature — find that consumer before the database does.
- • Correlate endpoint p99 with offset depth; linear correlation is the cost curve, and it forecasts the timeout date as the collection grows.
- • Track repeated-item complaints and sync-diff reports from consumers — drift is invisible in your metrics and shows up first in theirs.
- • Add cursor fields (`next_cursor`) to the existing envelope and let offset and cursor coexist; migrate traversal consumers first — they are the ones being hurt.
- • Introduce `max_offset` as a deprecation with telemetry: announce, measure who exceeds it, contact those consumers with the cursor path, then enforce (see [[consumer-driven-evolution]]).
- • Tightening a default `limit` is a behavioral change old clients will feel as shorter pages — version it or grandfather existing keys.
- • Depth caps genuinely remove capability: a consumer that legitimately parallel-fetched deep ranges now needs the export path you must build and own.
- • Keeping offset alongside cursors doubles the pagination surface — two orderings to keep consistent, two sets of edge cases to test.
- • Approximate or absent totals cost product features ("page 7 of 132" disappears); the honest alternative is paying for count maintenance (a counter table or cached count with staleness rules).
Misconceptions
created_at DESC ordering drifts at the head constantly — which is precisely where feeds and sync jobs read. Traffic makes it frequent; the ordering makes it possible.COUNT(*) on a large filtered collection can cost more than the page itself, and you pay it on every page request. That is why big APIs return has_more, approximate counts, or a separate count endpoint.