TL;DR —
LIMIT/OFFSETpagination assumes the list holds still. Real lists don’t: rows arrive while people read. So offset duplicates and skips rows — and it gets slow at depth. Cursor (keyset) pagination — “give me the rows after this one” — fixes both (the drift and the slow deep pages). Separately, once the rendered list gets long, virtualization keeps the DOM small. Two different windows: one over the data you fetch, one over the rows you paint. Play with both →
The feed I was building always put the newest items on top — a live betting feed, fresh bets streaming in at the head of the list as people placed them. Nothing exotic. But that one property — rows arrive while you’re reading — quietly broke pagination in a way that had nothing to do with a bug in my code.
Load a page. A few bets come in. Load the next page — and you’re handed a row you already saw. No error, no crash; the list just quietly lied about its own order.
My first instinct was that we’d broken something in the frontend — merged two pages, or gotten our React keys wrong so the UI recycled a row into the wrong slot. I checked. Everything looked correct. Then it clicked: the duplicate wasn’t coming from the frontend at all. Between loading page two and page three, the feed itself had changed. We weren’t paging through a snapshot — we were paging through a stream.
That’s the whole bug, and it’s baked into the most obvious pagination code you can write. Here’s why offset breaks, the one-line idea that fixes it, and a second, unrelated problem that broke the same feed — but only on phones.
Offset pagination points at a position
The default way everyone paginates is offset:
-- "give me 20 rows starting at position 40"
SELECT * FROM items ORDER BY id DESC LIMIT 20 OFFSET 40;OFFSET 40 is a position — the 40th row from the top, right now. That’s fine if the list never changes. But on a live feed, new rows arrive at the top, and every position below them shifts down by one.
Watch what that does. Page size 4, newest on top:
FIRST TWO PAGES THEN ONE ROW ARRIVES ON TOP
data you fetched data OFFSET 8 (page 3) now grabs…
───── ────────── ───── ──────────────────────────
#12 ┐ #13 ← new
#11 │ page 1 (OFFSET 0) #12
#10 │ #11 (everything shifted down one)
#9 ┘ #10
#8 ┐ #9
#7 │ page 2 (OFFSET 4) #8
#6 │ #7
#5 ┘ ← last row you saw #6
#4 #5 ┐ ← OFFSET 8 lands HERE now
#3 #4 │ page 3 = #5, #4, #3, #2
#2 #3 │ …but you already saw #5.
#1 #2 ┘ #5 is a DUPLICATE.The offset didn’t move — the data moved underneath it. Page 3 re-serves #5, a row that was the tail of page 2. Insert instead of prepend, or delete a row, and you get the mirror image: a skipped row nobody ever sees.
And there’s a second, unrelated problem with offset, worth knowing even on static data: it’s slow at depth. To answer OFFSET 1000000, the database reads and throws away a million rows before it returns yours. Offset cost grows with how deep you page.
So offset has two independent failure modes: it drifts on live data, and it degrades at depth.
Cursor pagination points at a row
The fix is to stop asking for a position and start asking for the rows after a specific one:
-- "give me the 20 rows after the last one I saw"
SELECT * FROM items WHERE id < :lastSeenId ORDER BY id DESC LIMIT 20;This is cursor (a.k.a. keyset) pagination. The cursor is the last key you saw — here id. New rows arriving on top don’t move id = 5, so “the rows after #5” is the same set no matter what got inserted above it. Immune to inserts and deletes shifting the page boundaries.
The whole difference in one picture:
OFFSET → a POSITION CURSOR → an IDENTITY
"row #40" "the rows after id = 123"
│ │
│ a row arrives on top │ a row arrives on top
▼ ▼
#40 is now a DIFFERENT row id = 123 is still id = 123
→ duplicates & skips → stableIt fixes the other problem too: with an index on the key, the database seeks straight to that row and reads the next 20 — no scanning-and-discarding — so deep pages stay fast regardless of table size.
Two things to get right about the key. It must be unique — a bare timestamp isn’t, and at a tie you’ll drop or repeat rows, so order by a composite like (timestamp, id). And it must be stable: id works because it never changes, but if you order by something mutable — a score, an updated_at — a row can move between pages while you’re paging, and cursor won’t save you. The invariant behind “immune to drift” is simply that the ordering key doesn’t move.
One thing cursor pagination doesn’t give you is a frozen snapshot. It keeps rows from moving between pages, but if a row you already scrolled past changes its contents, you won’t see that — that’s real-time updating, a different job.
The trade you’re making: cursor pagination can’t jump to an arbitrary page (“go to page 47”), total counts and “page 12 of 340” get awkward, and paging backward means flipping the comparison and the sort. But an infinite feed almost never needs page-jumping — which is exactly why cursor pagination and feeds are made for each other.
Where this really bit me. I hit the worst version of this on a live feed backed by a GraphQL subgraph (The Graph). There, offset isn’t just slow —
skiphas a hard ceiling of 5,000; page past it and the API flatly refuses withThe skip argument must be between 0 and 5000. And the docs don’t mince words: “avoid usingskip… it’s best to page through entities based on an attribute,” with the canonicalwhere: { id_gt: $lastID }. So switching to a cursor wasn’t a preference — it was the platform’s recommended pattern and the only way to reach old history at all. The lesson is universal, though: this is the sameOFFSETin Postgres,skipin Mongo,?offset=in a REST API. The subgraph just turned a soft footgun into a wall.
# offset — drifts on live data, and caps out at skip: 5000
{ items(first: 20, skip: 40, orderBy: id, orderDirection: desc) { id } }
# keyset — the recommended pattern, stable and uncapped
{ items(first: 20, where: { id_lt: $lastSeenId }, orderBy: id, orderDirection: desc) { id } }(skip isn’t a GraphQL quirk, by the way — The Graph is just exposing the same offset semantics as SQL’s OFFSET. Same footgun, different syntax.)
Correct isn’t the same as fast
Paging was correct now, and on my laptop the feed was smooth — I scrolled through hundreds of bets without a hitch. Then I opened it on my phone.
It stuttered. Scrolling lurched, taps lagged behind my thumb. Same code, same data — the only thing that changed was the device. The feed had grown to thousands of rows, and every one of them was sitting in the DOM. My laptop could brute-force that; a mid-range phone couldn’t.
This is a different problem from pagination. It has nothing to do with what you fetch — it’s about what you render.
The fix is virtualization (windowing): render only the rows in — or just outside — the viewport, and recycle the DOM nodes as they scroll off. Whether the list is 200 rows or 200,000, the number of mounted nodes stays a small, approximately constant “visible + overscan” set (it flexes a little with viewport size, overscan, and variable row heights). Libraries like react-window do this; content-visibility: auto in CSS covers some cases too.
The mental trap to avoid: virtualization does not “load only the visible data.” It doesn’t decide what you fetch, only what you mount — and it works the same whether you loaded 200 rows up front or you’re streaming more in as the user scrolls (infinite loading and virtualization pair up constantly). It bounds the DOM, not the network.
You can feel the difference in the playground: load 100,000 rows with virtualization off and you get 100,000 real DOM nodes and a frame time that tanks; flip it on and the node count collapses to a couple dozen and scrolling goes smooth. (The memory readout there is performance.memory — a non-standard, Chromium-only, deliberately-bucketed number. Great as a rough “look, it dropped” signal; not a benchmark.)
One idea: two windows
Both fixes are the same shape — a window — just over two different things.
- Pagination is a window over your data. Offset windows by position, and breaks when the data shifts beneath it. Cursor windows by a stable key, and doesn’t.
- Virtualization is a window over your DOM. Render the slice you can see; recycle the rest.
A big, live list usually needs both: cursors so the data stays correct as it grows, virtualization so the rendering stays fast.
And underneath the offset bug is a mistake worth recognizing everywhere: treating something that changes as if it were a snapshot. Anchor to identity, not position — and window what you paint — and your list stops lying.
Drive it yourself
The playground makes it concrete, no setup:
- In Offset mode, load two pages, click Insert a bet (watch the list shift down one row), then load the next page — a row you already saw comes back, flagged as a duplicate.
- Switch to Cursor and repeat — the same insert changes nothing.
- In Act 2, crank the list to 100k and toggle virtualization — watch the DOM node count and frame time collapse.