Offset vs Cursor Pagination
System designsystem design / API design
Two ways to page through a big list. At page 1 they look identical. At page 50,000 one of them quietly falls over. Knowing which — and why — is a classic intermediate line.
Offset pagination
GET /items?limit=20&offset=40000
SQL: ... ORDER BY created_at DESC LIMIT 20 OFFSET 40000
The catch: OFFSET doesn't skip rows for free. The DB still scans and discards all 40,000 rows before handing you 20. Page 1 is instant; deep pages get linearly slower. On a 10M-row table, "jump to page 9000" can take seconds and hammer your DB.
Worse — it's unstable. If someone inserts or deletes a row while the user is paging, every later row shifts by one. Result: the user sees a duplicate item, or skips one entirely, without any error.
Cursor (keyset) pagination
Instead of a numeric offset, you pass a pointer to the LAST row you saw:
GET /items?limit=20&after=2026-06-20T10:00:00Z_1843
SQL: ... WHERE (created_at, id) < ('2026-06-20T10:00:00Z', 1843)
ORDER BY created_at DESC, id DESC LIMIT 20The index seeks straight to that position. No scan-and-throw-away → page 50,000 is as fast as page 1. And because the cursor anchors to a real row's key (not a count), inserts/deletes elsewhere don't shift your window. Stable + fast.
Side by side
- Deep-page speed: Offset = slow (O(offset)). Cursor = constant.
- Stability under writes: Offset = drifts. Cursor = stable.
- "Jump to page 500": Offset = easy. Cursor = can't (only next/prev).
- Total count / page numbers: Offset = natural. Cursor = awkward.
- Implementation: Offset = trivial. Cursor = needs care.
The gotcha everyone hits
Cursor sorting MUST be on a unique, immutable key — or a composite that ends in one. Sort by created_at alone and two rows share a timestamp? Your < comparison straddles them and you silently drop or repeat rows at the page boundary. Fix: always tie-break with a unique column, e.g. ORDER BY created_at DESC, id DESC, and put both in the cursor. That's why the cursor above carries BOTH the timestamp and the id.
Rule of thumb: user-facing infinite scroll / feeds / APIs at scale → cursor. Small admin tables where humans click "page 7" → offset is fine.
Exercise
You have ORDER BY created_at DESC with no tie-breaker, and 5 rows share the exact same created_at right at a page boundary. Walk through what the user sees on page 2. Then rewrite the WHERE clause and the cursor token to fix it. Bonus: how would you encode the cursor so clients can't tamper with it (hint: base64 + opaque)?