LIMIT/OFFSET pagination silently duplicates and skips rows the moment writes happen concurrently, and gets slower the deeper a client pages. Here is why, and how keyset (cursor) pagination avoids both problems.
A user scrolls an activity feed, hits "load more," and sees three items they already saw. Another user exports a report page by page and ends up missing rows that definitely exist in the database. Both bugs trace back to the same root cause: LIMIT 20 OFFSET 100000 pagination, which quietly assumes the underlying data holds still between page loads. It doesn't, and at any real write volume it won't.
This isn't a hypothetical edge case. It's the default behavior of offset pagination under concurrent writes, and it's why almost every API that has scaled past a demo — Stripe, GitHub, Slack — moved to cursor-based pagination instead. The mechanics of why are worth understanding in detail, because the fix isn't "use a library," it's a different way of thinking about what a page even is.
OFFSET doesn't mean "jump to row N." It means "run the full query, then throw away the first N rows and return the next M." Even with a perfect index on the sort column, the database still has to walk past every skipped row — an index scan skips over OFFSET entries one at a time, it doesn't seek to a position. That gives offset pagination a cost that grows linearly with the offset itself, not with the page size.
Concretely: paging through a 10-million-row orders table sorted by created_at DESC, OFFSET 100 is cheap — the database walks 120 index entries and returns 20. OFFSET 500000 for page 25,001 means walking half a million index entries to throw all of them away before it can return the 20 you asked for. On the same hardware, that query can go from single-digit milliseconds to hundreds of milliseconds or worse, and the cost keeps climbing the deeper a client pages in. Deep pagination isn't a rare code path either — bots, scrapers, and "export everything" jobs page to the end far more than real users browsing page 2.
The second failure mode is more insidious because it corrupts results instead of just being slow: page drift. Say a feed is sorted created_at DESC, page size 20, and a user is looking at page 3 (rows 41–60 by rank). While they're reading page 2, five new rows get inserted at the top of the sort order. Every existing row's rank shifts down by five. When the client requests page 3 with OFFSET 40, it now gets what used to be rows 36–55 — five rows repeated from page 2, and five rows that belonged on page 3 silently skipped. Nothing errors. The API returns a perfectly well-formed page of 20 rows; it's just the wrong 20 rows. This is exactly the "why do I see duplicates in my feed" and "why is my paginated export missing records" class of bug reports that are maddening to reproduce because they depend on write timing.
The fix is to stop asking "give me rows 41 through 60" and instead ask "give me the rows that come after this specific row I already saw." That's keyset pagination, sometimes called seek pagination:
-- First page
SELECT id, created_at, title
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Next page: anchor on the last row of the previous page
SELECT id, created_at, title
FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Two things make this work. First, the WHERE clause is a direct index seek — the database jumps straight to the anchor position in the B-tree index and reads forward, so the query costs roughly the same whether it's page 2 or page 20,000. Second, because the boundary is defined by data (a specific created_at/id pair) rather than a position (the 500,000th row), inserting or deleting rows elsewhere in the table doesn't shift anything. New rows above the cursor just show up the next time the client refreshes from the top; they don't cause the current page to duplicate or skip rows.
The tiebreaker column matters more than it looks. created_at alone isn't guaranteed unique — two orders can be inserted in the same millisecond, and MySQL/Postgres timestamp precision or bulk imports make ties common in practice. Without id as a secondary sort key, rows with identical timestamps can be skipped entirely or returned twice, reintroducing the exact bug keyset pagination is supposed to fix. Always sort and filter on a compound key that's guaranteed unique: (created_at, id), (score, id), whatever the primary sort is, plus the primary key.
"Cursor" and "keyset" get used interchangeably, but it's worth separating the concept from the implementation. A cursor is just an opaque pointer the API hands back to the client — "here's where you left off, send this back to get the next page." Keyset pagination is the most common way to implement a cursor, but the client should never see or construct the raw (created_at, id) tuple directly. Instead, encode it:
cursor = base64url(JSON.stringify({ created_at: "2026-09-18T09:11:21Z", id: 48213 }))
Treating the cursor as opaque buys three things: you can change the underlying sort key later without breaking the API contract, you can reject cursors that don't decode cleanly instead of trying to interpret malformed input as a query filter, and — if the endpoint is scoped to a tenant or user — you can HMAC-sign the cursor so a client can't hand-edit it to page into someone else's data by guessing at internal IDs.
Keyset pagination isn't a strict upgrade; it trades away one thing offset does well: jumping to an arbitrary page number. "Go to page 50" is a single OFFSET 980 query with offset pagination and has no clean keyset equivalent, because a keyset cursor only knows how to move forward or backward one page at a time from a known anchor. That makes offset pagination the right choice for admin tables with numbered page links and a visible total count, and keyset pagination the right choice for infinite scroll, "load more" feeds, and any public API where clients page sequentially — which is most of them. Total counts are also cheap with OFFSET's companion COUNT(*) query pattern but require a separate (often approximate, via EXPLAIN estimates or a maintained counter) strategy under keyset pagination, since "how many total rows" doesn't fall naturally out of a seek query.
| Offset pagination | Keyset (seek) pagination | |
|---|---|---|
| How it locates a page | Skips N rows, returns the next M | Seeks to a known row, returns the next M |
| Cost at high page numbers | Grows with offset (scans and discards) | Roughly constant (direct index seek) |
| Stable under concurrent inserts/deletes | No — page drift causes duplicates/skips | Yes — anchored on data, not position |
| Jump to arbitrary page number | Yes | No (sequential only) |
| Exact total count | Cheap (COUNT(*)) | Expensive/approximate |
| Best fit | Admin tables, numbered pagination UI | Feeds, infinite scroll, public APIs |
If an API or feed only ever gets paged a few pages deep by a human clicking "next," offset pagination's flaws rarely surface — which is exactly why it ships broken and stays broken until a bot, an export job, or a busy feed exposes it. Default to keyset pagination for anything sorted and paged sequentially, keep the cursor opaque and signed, and reserve OFFSET for genuinely random-access UIs where jumping to page 50 matters more than correctness under writes. If you're also returning pagination links in HTTP headers rather than the response body, Utilix's HTTP Link Header Builder will assemble the rel=next/rel=prev Link header from your cursor URLs correctly formatted per RFC 8288, without you hand-rolling the comma-and-semicolon syntax yourself.