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

Lê Thanh Trúc — Tue, 07 Jul 2026 01:38:16 +0000

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, on NuGet.

// Program.cs
builder.Services.AddSeekKit(options =>
{
    options.Strategy        = DatabaseStrategy.ForSqlServer();
    options.DefaultPageSize = 20;
});

// anywhere you have an IQueryable
var page = await seek.SeekAsync(
    db.Products.Where(p => p.IsActive),
    new SeekRequest { Token = token, PageSize = 20 },
    b => b.OrderByDescending(p => p.CreatedAt)
          .OrderBy(p => p.Id));   // unique tie-breaker last

The result carries Items, NextToken, PreviousToken, HasNext,
HasPrevious. Tokens are opaque Base64url strings — clients pass them back
to navigate, without ever seeing your key values.

A few design decisions I want to highlight, because they're where the actual
work went:

Each database gets different SQL. PostgreSQL supports row-value
comparison — WHERE (a, b) > (@a, @b) — which the planner turns into a
clean index seek, so SeekKit uses it there. SQL Server doesn't support
row-values in comparisons, so it gets a UNION ALL + TOP N pattern
instead, which optimizes better than nested ORs. MySQL, Oracle, and SQLite
have their own strategies. Everything is built as LINQ expression trees, so
your EF Core provider does the final translation — no raw SQL anywhere.

Mixed sort directions just work. Status ASC, Priority DESC, Id ASC is
fine. When a fast strategy can't handle the shape (e.g. tuple comparison
requires uniform direction), it falls back automatically.

Tokens can be signed. By default tokens are opaque but not
tamper-proof. One config line turns on HMAC-SHA256 signing, and forged or
modified tokens are rejected:

builder.Services.AddSeekKit(
    options => options.Strategy = DatabaseStrategy.ForSqlServer(),
    config  => config.UseHmacSigning(builder.Configuration["SeekKit:TokenKey"]!));

It targets .NET 8/9/10 (EF Core 8/9/10) and .NET Standard 2.1 (EF Core 5).

Try the benchmark yourself

The repo has a runnable example: a .NET 10 minimal API with two endpoints —
/products (keyset) and /products/offset (offset) — both returning
elapsedMs, plus a docker compose file for SQL Server and a resumable seed
script that can generate millions (or billions, if your disk dares) of rows:

👉 https://github.com/lttruc1402/SeekKit.EntityFramework

Seed a few million rows, hit a deep offset page, then walk the same distance
with tokens. The difference sells itself.