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.
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 👇
Top comments (2)
Great explanation. Pagination is one of those areas where an implementation can look perfectly fine at small scale but become a serious performance problem as data grows.
OFFSET/LIMIT is convenient because it matches how humans think about pages, but the database still has to walk past the skipped rows. As the offset increases, the work increases — even when the user only needs a small result set.
Keyset pagination is usually the better choice for large datasets because it turns pagination into an indexed range query:
Avoids scanning discarded rows
Provides more consistent latency
Works better with high-volume tables
Handles real-time data changes more predictably
One additional challenge in production is designing the right cursor. A simple timestamp is often not enough because of duplicate values. Using a stable ordering combination (for example created_at + id) helps avoid missing or duplicated records.
For APIs, this also changes the UX model slightly — instead of thinking in “page numbers,” clients work with continuation tokens/cursors.
A good reminder that database performance is often about choosing the right data access pattern, not just adding more infrastructure.
Great write-up. 👏
Thanks Luis!
The timestamp cursor thing is so real. Works fine in dev, then prod has
two rows with the same created_at and rows just quietly disappear between
pages. No exception, no log, nothing. Took me a while to catch that one
the first time.
That's why the lib is opinionated about it — last OrderBy column has to
be unique (usually just Id), and it goes into every predicate. I'd rather
force the convention than debug that again.
Agree on the token UX too. Frontends doing infinite scroll never actually
needed page numbers, they just want "give me the next chunk". The only
thing you really lose is jump-to-page-N, and honestly nobody's jumping to
page 57 of anything.