Offset pagination feels obvious. LIMIT 20 OFFSET 100 — simple, readable, works in every ORM. I used it for years without thinking about it. Then I hit a feed with a few million rows and watched it fall apart.
The problem with OFFSET
The database still has to count and skip those rows. OFFSET 10000 doesn't jump to row 10,000 — it reads 10,000 rows and throws them away. At low volumes this is invisible. At high volume, page 500 of your feed takes ten times longer than page 1, and that ratio gets worse as the table grows.
There's a second problem: data drift. If a new row is inserted while a user is paginating, every subsequent page shifts by one. Users see duplicates. Items disappear. For a social feed or activity log this is constantly wrong, not just occasionally wrong.
How cursor pagination fixes both
Instead of "skip N rows", a cursor says "give me rows where id > last_seen_id". The query becomes:
SELECT * FROM events
WHERE id > $cursor
ORDER BY id ASC
LIMIT 20;
This is a range scan on an indexed column — O(log n) to find the cursor, then a sequential read of 20 rows. Performance is constant regardless of how deep into the feed you are.
Data drift is also eliminated. The cursor points to a specific row, not a position. New inserts don't shift anything.
The tradeoffs worth knowing
Cursor pagination has two real limitations, and pretending otherwise is how you get burned later.
You can't jump to arbitrary pages. There's no equivalent of "go to page 47". If your product needs "skip to page N" — a search results page, an admin table with page numbers — cursor pagination isn't the right tool. Use offset there, with awareness of its limits.
The cursor has to be on an indexed, monotonic column. Composite cursors (e.g. sorting by (score, id)) require encoding both values and add complexity. It's manageable but it's not free.
What I actually ship
For anything resembling a feed — notifications, activity logs, timelines, audit trails — I default to cursor pagination on a created_at / id composite. The ID breaks ties on rows with identical timestamps.
For admin tables and search where users expect page numbers, offset is fine — the data is usually bounded, the user is usually not on page 500, and the UX expectation of "page 3 of 12" is worth the tradeoff.
The rule I follow: if the data is high-volume, real-time, or user-facing — use cursors. If it's bounded, internal, or needs random access — offset is probably fine.
It's not a complicated decision once you've debugged a production feed where page 300 times out.
