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. I put together an animated breakdown of exactly this on SeeItFlow if you'd rather watch the query costs play out than picture them.
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, at least for the insert case. 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.
One caveat: this holds for stable sort keys, not all of them
Everything above assumes the value you're paginating on doesn't move, which is true for something like id, but not for something like popularity. If the sort key itself can change while users are paging through it, a plain single-column cursor reopens a version of the same duplicate-and-skip problem, just triggered by an update instead of an insert: a product that climbs above the cursor after a user has scrolled past it gets skipped, and one that drops below shows up a second time.
The fix is a composite cursor rather than a single value. Order by (popularity DESC, id DESC), encode both values in the cursor, and query WHERE (popularity, id) < (:p, :id), with id acting as a tiebreaker so two rows tied on popularity at a page boundary don't get dropped between requests. Worth knowing if you're moving between database engines: PostgreSQL can serve that row comparison directly from a (popularity, id) index, while some other engines need it unrolled explicitly as popularity < :p OR (popularity = :p AND id < :id) to get the same index usage, so check your query plan rather than assuming it carries over.
For any endpoint sorted on a value that changes, it's worth saying so directly in your API docs, pages are a consistent snapshot only when the sort key is stable, and approximate otherwise.
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, keeping in mind the caveat above if the sort key itself is mutable. 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, and does the thing I'm sorting by actually stay still." Returning twenty rows is cheap no matter what. Finding them correctly is where the real cost and complexity live.
References
Want to see the query costs play out instead of just reading about them? Check out the animated walkthrough on SeeItFlow, it steps through offset scans, cursor seeks, and the production failure cases above one by one.
