Offset Pagination

The Model

 GET /trips?limit=20&offset=40      → "skip 40, return 20"

 SELECT ... ORDER BY created_at DESC
 LIMIT 20 OFFSET 40;

Familiar from every admin panel ever built: pages are numbered, users jump anywhere, implementation is one SQL clause.

The Instability Problem

Offsets address positions, but live data moves:

 user views page 3 (rows 41–60)
 meanwhile: 5 new trips inserted at the top
 
 next page request (offset=60) returns rows that SHIFTED:
 → trip #45 appears on both page 3 and page 4 (duplicate)
 → some trips never appear at all (skipped)

 reads during writes = duplicated and missing items
 harmless for static catalogs; embarrassing for activity feeds

The Cost-at-Depth Problem

Databases must walk past skipped rows to count the offset:

 LIMIT 20 OFFSET 1,000,000
 → index-scans 1,000,020 entries, discards a million, returns 20
 
 latency grows LINEARLY with depth:
   page 1     ~2 ms
   page 500   ~200 ms
   page 50,000 ~ seconds + angry DBA
 
 deep offsets also hold locks/cache pressure longer —
 they're a favorite accidental DoS vector on public APIs

Count queries hurt too: SELECT COUNT(*) for total-pages is another full scan per render.

Where Offset Is Still Correct

Scenario	Why offset wins
Admin dashboards	Data volume small; numbered pages essential
Search results UI	Users expect jump-to-page; result sets bounded
Static/rarely-changing data	Instability moot
Reporting exports with stable snapshots	Position semantics desired

Offset pagination over a bounded, slowly-changing dataset is perfectly sound engineering — the failures come from applying it to hot mutable feeds.

Mitigations When You Must Keep It

 - cap max offset (e.g., offset ≤ 10,000) beyond which cursor is required
 - keyset-hybrid for "next" while keeping numbers for jumps:
     page numbers for navigation, cursor for sequential next/prev
 - snapshot boundaries for consistency-sensitive exports:
     WHERE id <= :snapshot_max_id captured once, then paginate within
 - avoid COUNT(*): estimate totals or drop page-count display

The snapshot trick converts unstable positions into a frozen range — cheap stability when full cursors don’t fit the UX.

Interview Framing

Interviewers probe offset to test depth-awareness: “what happens at page 100,000?” Scored answer covers both wounds honestly — linear scan cost plus shift instability — then states the mitigation or migration path. Knowing when offset is fine matters as much as knowing when it isn’t; reflexive cursor-everything reads as inexperience too.