Backend & Data
Cursor vs offset pagination
Every list that's too long to show at once needs paging, and almost everyone reaches for the same tool first: LIMIT 10 OFFSET 40 to get page five. It works — until the list is long, or it's changing. Then two nasty bugs show up: deep pages get slow, and rows start skipping or repeating. The fix is to page by a cursor instead of a count.
Offset — count and skip
Offset paging says "skip the first 40 rows, give me the next 10." The trouble is the database can't teleport to row 41 — to skip 40 rows it usually has to read them first and throw them away. Page 2 is instant; page 5,000 reads 50,000 rows to hand you 10. Worse, if someone inserts or deletes a row while a user pages, the offsets shift under them — so a row slides onto a page they already saw (a duplicate) or off one they haven't (a skip).
Cursor — bookmark and jump
Cursor paging (a.k.a. "keyset" paging) throws away the page number and remembers a bookmark instead — the value of the last row you saw. The next page is simply "give me the rows after this one": WHERE id > :last_id ORDER BY id LIMIT 10. With the right index, the database jumps straight to that spot — page 5,000 costs the same as page 2. And because you're anchored to a real row, not a count, inserts and deletes elsewhere don't make rows skip or repeat.
Offset says "skip 40, give me 10" — and the DB has to count past 40 every time. Cursor says "start after the last thing I saw" — and the DB jumps straight there. One drifts and slows; the other is stable and fast.
So why is offset everywhere?
- Offset lets you jump to any page ("go to page 37") and show a total page count — cursors only do next/previous, because there's no notion of an absolute page.
- Offset is fine for small, stable lists — a blog with a few dozen posts paged into a handful of pages will never feel it. (Yes, including this one.)
- Cursor wins for scale and freshness — infinite-scroll feeds, big tables, and any API where deep paging or shifting data is real.
Rule of thumb: if users need to hop to an arbitrary page number and the dataset is modest, offset is simplest. If the list is large, live, or scrolled endlessly, page by a cursor and both the slowness and the skip/duplicate bugs simply vanish.