Database Pagination: Cursor vs Offset
Offset pagination is the textbook answer; cursor pagination is the production answer. The difference matters at any meaningful scale.
Why pagination matters
Pagination looks simple until you scale. The two paradigms have wildly different performance characteristics; the choice locks in for years.
- User-facing need. Users scroll; the query needs to return page 2, 3, 4 efficiently.
- Two paradigms. Offset vs cursor; same surface, very different performance at scale.
- Hard to switch. The choice shows up in the API contract; clients depend on it; migration is painful.
- Performance gap. Page 1000 with offset means scanning 10000 rows; cursor stays constant.
Offset pagination
- Offset:
OFFSET 100 LIMIT 10. Skips 100 rows; returns next 10. - Linear performance: page 1000 means scan 10000 rows.
- Easy to implement; bad at scale.
Cursor pagination
Cursor pagination uses a stable sort key as the boundary marker. Each page filters on the previous cursor; performance stays constant regardless of page depth.
- Query shape.
WHERE id > last_seen_id ORDER BY id LIMIT 10; constant time per page. - Cursor opaque. Server stores the cursor; client passes it back; format is implementation detail.
- Stable sort required. Sort key must be unique and monotonic; otherwise pages duplicate or skip rows.
- Slightly complex. More work upfront; necessary at any meaningful scale.
API-design implications
The pagination paradigm leaks into the API. Cursor and offset APIs look different on the wire; once shipped, the contract is hard to change.
- Cursor APIs. Return
next_cursorwith each page; no total count, just 'more available'. - Offset APIs. Return total count plus page numbers; lets clients show 'page 5 of 47'.
- Implies backend. Each API style implies a backend pagination choice; cannot easily switch later.
- Default rule. Cursor for everything new; offset only when small bounded result sets are guaranteed.
Antipatterns
- Offset pagination at scale. Slow page 100; broken page 10000.
- Cursor without stable sort. Pages duplicate or skip rows.
- Mixing both APIs. Confusing for clients.
What to do this week
Three moves. (1) Apply this pattern to your slowest production endpoint. (2) Measure p99 before/after. (3) Document the win and ship the runbook so the team can reproduce.