Why Skip/Take gets slower on every page (and how keyset pagination fixes it)

A while back I was debugging an API where page 1 returned in 5ms and page

50,000 took several seconds. Same query, same table, same indexes. The only

difference was one number in the URL.

This post is about why that happens, and how keyset pagination fixes it.

The problem with OFFSET

Here's what most of us write on day one:

var products = await db.Products

    .OrderByDescending(p => p.CreatedAt)

    .Skip((page - 1) * pageSize)

    .Take(pageSize)

    .ToListAsync();

EF Core translates this to OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY

(or LIMIT/OFFSET on Postgres and MySQL). Looks harmless. The problem is in

how the database executes it: it cannot jump to row 1,000,000. It walks

the index from the beginning, reads every row before your offset, and throws

them away.

So the cost is O(offset + pageSize). Page 1 reads 20 rows. Page 50,000 reads

a million rows to return the same 20. That's the whole bug — pagination

depth becomes a hidden table scan.

There's a second, sneakier problem: offset pagination is unstable under

writes. If someone inserts a row while your user is between page 3 and

page 4, every row shifts by one. The user sees the last item of page 3 again

at the top of page 4 — or worse, misses a row entirely. For feeds and

exports this means duplicated and lost data.

Keyset pagination (a.k.a. the seek method)

Instead of telling the database "skip N rows", you tell it where the last

page ended:

SELECT TOP 20 * FROM Products

WHERE CreatedAt < @lastCreatedAt

   OR (CreatedAt = @lastCreatedAt AND Id < @lastId)

ORDER BY CreatedAt DESC, Id ASC;

With an index on (CreatedAt DESC, Id ASC), this is a single index seek.

Cost: O(pageSize). Page 1 and page 1,000,000 are the same query with

different parameters. And because the cursor points at an exact row (that's

why you need a unique tie-breaker column like Id), concurrent inserts

can't shift your pages.

The trade-off: you lose random access. There's no "jump to page 57" —

only next and previous. For infinite scroll, feeds, exports, and sync APIs,

that's exactly the access pattern anyway.

So why doesn't everyone do this?

Because writing those WHERE clauses by hand hurts. The two-column example

above is the easy case. Add a third sort column, mix ascending and

descending directions, and the predicate explodes into nested ORs that are

easy to get subtly wrong — and a subtly wrong keyset predicate doesn't

throw, it just silently skips rows.

You also need to serialize the cursor position to the client somehow,

ideally without leaking your key values, and handle paging backwards.

After writing this by hand a few times, I built a library so I'd never have

to again.

SeekKit.EntityFramework

MIT licensed,

Seed a few million rows, hit a deep offset page, then walk the same distance

with tokens. The difference sells itself.

This is my first open-source library, and I'd love feedback — on the API,

the per-database strategy approach, or edge cases I haven't thought of.

And I'm curious: how do you handle pagination on big tables? Comments open 👇