Offset pagination vs. cursor/keyset pagination — what breaks with offset at scale?

#The query that looks completely reasonable

Almost every pagination tutorial starts here:

SELECT id, customer_id, total_cents, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 500000;

It works perfectly in a demo with two hundred rows. It becomes a production incident once orders has fifty million rows and someone's dashboard tries to render "page 25,000."

#What OFFSET actually costs the database

OFFSET is not a bookmark. The database does not remember where the last request stopped. Every single call re-executes the full ORDER BY, walks the resulting stream from the very beginning, and throws away the first OFFSET rows before it starts handing you the LIMIT you asked for. Even with a perfect index on created_at, that walk is still proportional to the offset — the engine has to traverse that many index entries (and often fetch the underlying heap rows to apply any additional filters) before it reaches your page.

That gives you a cost of roughly O(offset + limit) per request. Paginate through an entire n-row table one page at a time and the total work done across all requests is closer to O(n²), because each successive page re-walks everything before it, one more page's worth deeper than the last.

There's a correctness problem hiding here too, not just a performance one. OFFSET counts rows fresh on every query. If a new order is inserted while a user is sitting on page 3, everything after it shifts by one position — the next page fetch can silently duplicate a row the user already saw, or skip one entirely. Offset pagination is only ever "correct" against a perfectly static table.

#Keyset pagination asks a different question

Instead of "skip N rows and give me the next page," keyset (a.k.a. cursor) pagination asks "give me the rows that come after the last one I already saw":

Wrap long lines
-- first page: no cursor yet
SELECT id, customer_id, total_cents, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- every subsequent page: client echoes back the last row it received
SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE (created_at, id) < ($1, $2)   -- $1, $2 = created_at, id from the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

Two details matter here. First, id is included purely as a tiebreaker: created_at alone is not a unique, total order — two orders can share a timestamp, and without a tiebreaker, rows can still be skipped or duplicated exactly the way they are with OFFSET. Second, the row-value comparison (created_at, id) < ($1, $2) — supported directly by Postgres? and MySQL 8+ — is what makes this fast: with a composite index on (created_at DESC, id DESC), the planner turns that predicate into an index range scan that seeks straight to the boundary in roughly O(log n), then reads exactly limit rows forward. The cost of page 50,000 is identical to the cost of page 2.

#Keeping the cursor opaque

Public APIs generally don't hand clients the raw (created_at, id) tuple to build their own WHERE clause. They return an opaque token instead:

Wrap long lines
{
  "data": [ /* 20 orders */ ],
  "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wMS0xNVQxMjozMDowMFoiLCJpZCI6ImNhcnRfNDgxIn0="
}

The client just echoes next_cursor back on the next request; the server base64-decodes (and ideally signs) it internally. That indirection matters for more than tidiness — it lets you change the underlying sort key or indexing strategy later without breaking every client that memorized your row shape.

#Trade-offs, side by side

#Picking one

Offset earns its keep in small, low-write admin tables where someone genuinely wants to type a page number. Everywhere else — public APIs, infinite-scroll feeds, anything over a large or fast-growing table — keyset pagination is the default a senior engineer reaches for, which is exactly why Stripe, GitHub, and Slack all paginate their APIs by cursor rather than offset.