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

User scrolls; query needs page 2, 3, etc.

Two paradigms; very different performance.

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: WHERE id > last_seen_id LIMIT 10. Constant time per page.

Implementation: store cursor; pass back to client.

Slightly more complex; necessary at scale.

API-design implications

Cursor APIs return next-cursor with each page.

Offset APIs return total-count + page numbers.

Each API style implies a backend pagination choice; cannot easily switch later.

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.