Somewhere around month four of a product's life, someone on the team notices the products page has gotten sluggish. Not broken, just slow, and getting slower. Nobody touched that endpoint. No deploy caused it. The query is the same SELECT * FROM products LIMIT 20 OFFSET 40 it's always been.
Except it isn't the same query anymore, not in any way that matters. The table had ten thousand rows when that endpoint was written. It has a million now. And OFFSET doesn't skip rows for free, it has the database walk through every single one before it, count them, and discard them, just to hand you the twenty you actually asked for. At offset 40 that's nothing. At offset 1,000,000 the database is doing real work for every page your users click through, and that cost keeps climbing for as long as the table keeps growing.
This is one of the more common ways a perfectly reasonable-looking API decision turns into a production incident eighteen months later, so it's worth understanding what's actually happening under LIMIT and OFFSET, and what the alternatives trade away to avoid it.
Pagination isn't about limiting rows, it's about controlling database work
Say you've got an ecommerce catalog with a million products and someone hits GET /products with no limit at all. The database tries to hand back all million rows, and now you're dealing with a payload the browser has to download and render, memory pressure on your API server holding all that in memory at once, and a database that just did a full table scan for one request. Add ?limit=20 and none of that happens, you get twenty rows, a small payload, and a fast response.
That part every developer already knows. The part that surprises people later is that not all ways of getting "the next twenty rows" cost the same, and the cheap-looking one gets expensive as your data grows.
Offset pagination: simple, until it isn't
This is almost certainly what your first pagination implementation looked like:
SELECT * FROM products
LIMIT 20 OFFSET 40;
Skip forty rows, return the next twenty. It's trivial to write, it maps directly onto page numbers in a UI, and it's what everyone reaches for first. For a while, that's completely fine.
The trouble is what "skip forty rows" turns into once forty becomes a million. LIMIT 20 OFFSET 1000000 doesn't jump to row 1,000,001. Conceptually, the database walks past row 1, then row 2, then every row after that, discarding each one, until it's counted a million rows it's not going to return, and only then does it start collecting the twenty you asked for. You get back a 20-row response, but the database did a million rows of work to produce it. People sometimes call this the offset tax, and it's a genuinely apt name, because it's a cost you keep paying, and it keeps going up, for as long as the table grows and users keep paging deep into it.
There's a second problem that has nothing to do with speed. Offset pagination identifies a row by its position, row 41 through 60, say, not by what that row actually is. If someone inserts a new product while a user is browsing page 2, every row downstream shifts by one position, and the user can see the same product twice, or skip one entirely, without anything actually going wrong on your end. The pagination did exactly what it was told to do. It just wasn't told to do the right thing for a dataset that changes while people are reading it.
None of this makes offset pagination bad. It's still the right call for an admin dashboard, a report, or any dataset that's small or rarely paged deep into, anywhere users genuinely want to type "page 47" and land there directly. It just doesn't scale to a feed, a large catalog, or anything that grows without bound.
Cursor pagination: stop counting, start pointing
Cursor pagination asks a different question. Instead of "give me the rows starting at position 1,000,000," it asks "give me the rows that come after this specific one I already have":
GET /products?after=cursor123
The cursor identifies an actual row, not a position, usually something derived from its ID or sort key. The database doesn't count anything to get there. It performs roughly the equivalent of:
WHERE id > 1000000
LIMIT 20;
which, with the right index, is an index seek straight to the right spot, followed by reading the next twenty rows. It doesn't matter whether that WHERE id > lands you at row 1,000 or row 100,000,000, the cost is the same either way, because the database isn't walking past anything to get there.
This also quietly fixes the duplicate-and-skip problem. Since the cursor points at a specific item rather than a position, an insert somewhere else in the table doesn't shift what "after this cursor" means. That's exactly why Instagram and Twitter/X use cursor pagination for feeds that are being written to constantly while people are scrolling through them, and why Stripe's list APIs expose starting_after and ending_before instead of page numbers.
The trade-off is real, though: you lose the ability to jump to an arbitrary page. There's no "go to page 500" with a cursor, only "give me the next batch after where I am." For a social feed or an infinite-scroll UI, nobody wants page numbers anyway. For a search results page where users expect to type in a page number, that's a genuine loss of functionality, not just an implementation detail.
Keyset pagination: cursor pagination's stricter sibling
Keyset pagination is usually described as a specific implementation of the cursor idea, one built directly on a sort key rather than an opaque token:
SELECT * FROM products
WHERE id > 500
LIMIT 20;
Same mechanism as cursor pagination in spirit, same index-seek performance profile, but it's explicit about what it's ordering by. That makes it a good fit for append-heavy workloads: event streams, time-series data, transaction logs, anything where "give me everything after this point in an ever-growing, rarely-reordered sequence" is exactly the access pattern you have. The cost is that it wants a genuinely stable sort key, and it gets noticeably more awkward once you need to sort by something like popularity or relevance that changes independently of insertion order.
Where it actually goes wrong in production
A few specific mistakes show up over and over, and they're worth knowing before you hit them rather than after.
Deep offsets left unbounded. If nothing stops a client from requesting ?offset=5000000, someone eventually will, whether by accident, a scraper, or just a very persistent user clicking "next page" a lot. Capping the maximum offset you'll honor, or switching the endpoint to cursor pagination once a dataset crosses some size, heads this off before it becomes an incident.
Sorting without a matching index. ORDER BY popularity DESC without an index on popularity means a full table scan on every single request, no matter which pagination strategy you're using. Cursor pagination isn't magic here, it still needs INDEX(popularity, id) or equivalent to get the index-seek behavior that makes it fast. Without the index, you've just built a cursor-shaped API on top of offset-pagination performance.
Cursors a client can edit. A cursor that's just {"id": 4231} in plain JSON is an invitation for someone to hand-edit it and see what happens. Treat cursors as opaque, base64-encode them at minimum, sign or encrypt them if what's inside would leak anything you don't want exposed.
A COUNT(*) on every request. Returning "total": 10000000 in every paginated response looks harmless until you notice that computing it means the database runs a full count on every single call, which on a large, growing table can end up costing more than the actual query that fetches your twenty rows. Unless your UI genuinely needs a total, "has_more": true gets you the same practical outcome, "should I show a next button," without the query.
What it looks like in practice
The difference shows up clearly once you compare a deep page fetched both ways. Offset, twenty rows in from a hundred thousand:
GET /products?limit=20&offset=100000
might genuinely cost something like 300ms and a hundred thousand rows scanned to return twenty. The same logical request with a cursor:
GET /products?limit=20&after=eyJpZCI6OTk5OX0=
does an index seek and reads twenty rows, typically a couple of milliseconds, and that number barely moves whether the table has ten thousand rows or ten million.
Picking one
None of these three is a universal right answer, they're suited to different shapes of problem. Offset pagination earns its keep when users genuinely expect page numbers and the dataset is small or rarely paged very deep, think admin panels and reports. Cursor pagination is the right default for anything that scales and changes while people are reading it, feeds, timelines, any API where "infinite scroll" describes the UX. Keyset pagination is cursor pagination's more rigorous form, best suited to append-only, ever-growing data like event streams and transaction logs where the sort key is naturally stable.
If there's one habit worth taking away from all of this, it's to stop thinking of pagination as "how do I limit the rows I return" and start thinking of it as "how much work am I asking the database to do to find them." Returning twenty rows is cheap no matter what. Finding them is where the real cost lives, and that's the part offset pagination quietly gets worse at over time while cursor and keyset pagination don't.
Explore It Visually
I built an interactive walkthrough that animates the difference directly, watching an offset query scan through rows it'll throw away versus a cursor query jumping straight to its target, along with the production failure cases above played out step by step: Pagination on SeeItFlow.
Has your team hit the offset tax in production, or made the jump to cursor pagination before it became a problem? I'd like to hear how that migration went for you.
Top comments (0)