When should keyset pagination replace EF Core Skip/Take for large result sets?

Question

When a .NET service paginates a large table through EF Core, Skip() and Take() translate to an OFFSET-FETCH clause on SQL Server (LIMIT/OFFSET on other providers): the database scans and discards every row before the offset before returning the requested page. Take() alone maps to an efficient TOP clause, but the cost of Skip() grows with the page number, so deep pages get progressively more expensive regardless of EF Core version.

Keyset pagination avoids that scan by filtering on an indexed sort column in a WHERE clause, but it changes the API contract: clients can no longer jump to an arbitrary page number, and the sort key must be unique and stable so records are neither duplicated nor skipped between pages.

At roughly what offset depth does OFFSET-FETCH become a practical bottleneck on large tables, given that the threshold depends on indexes and row count?
Is keyset pagination the recommended pattern for deep paging in EF Core, and what ordering guarantees does it require?

Tasadduq Burney · Answer

Direct Answer
Keyset pagination should replace Skip()/Take() when your application needs to serve pages beyond approximately 10,000–50,000 rows into the result set, assuming a well-indexed sort column. Below this range, OFFSET-FETCH typically performs acceptably; above it, the cost of scanning and discarding rows grows linearly with the page number, creating a measurable bottleneck.

Why Offset-Based Pagination Slows Down
When EF Core translates Skip(n).Take(m), it generates OFFSET n ROWS FETCH NEXT m ROWS ONLY on SQL Server. The query engine must materialize and discard the first n rows before returning the requested page. This happens regardless of whether an index exists on the sort column—the index may be used, but the engine still walks the index to reach the offset. As n increases, so does CPU, I/O, and memory usage.

When to Switch to Keyset Pagination

Deep paging: Pages beyond ~10K–50K rows (varies by index efficiency and row width).
High query volume: Multiple clients paging through large datasets simultaneously.
Stable sort key: You have a column (or composite) that is indexed, unique, and immutable during the pagination window.

Requirements for Keyset Pagination
Keyset pagination filters using a WHERE clause instead of OFFSET. For example:
SELECT * FROM Items
WHERE (CreatedDate, Id) > (@lastDate, @lastId)
ORDER BY CreatedDate, Id
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
This requires:

Unique ordering: A composite key such as (SortColumn, PrimaryKey) ensures no duplicates.
Index support: A covering index on (SortColumn, PrimaryKey) keeps seeks fast.
Consistent sort: The same ORDER BY columns must be used for filtering and retrieval.

Trade-offs

Cannot jump to an arbitrary page number without tracking cursors.
Total row count becomes an additional query if needed.
Bi-directional paging requires separate logic for < vs > predicates.

Verification Steps

Enable EF Core SQL logging and confirm OFFSET-FETCH is generated.
Run EXPLAIN or execution plans for increasing offsets; watch for rising logical reads.
Test keyset queries under concurrent writes to ensure no gaps or duplicates.

Final Recommendation
If your use case involves deep pagination (pages > 10K rows) or high-frequency access to later pages, migrate to keyset pagination. The switch requires client-side cursor handling but eliminates the offset scan entirely. For random page access or accurate counts, consider a hybrid approach combining keyset with cached totals.

When should keyset pagination replace EF Core Skip/Take for large result sets?

1 answer

Direct Answer

Why Offset-Based Pagination Slows Down

When to Switch to Keyset Pagination

Requirements for Keyset Pagination

Trade-offs

Verification Steps

Final Recommendation

Your perspective belongs here.

0 question comments