If you have added pagination to a .NET 8 API recently, you almost certainly reached for Skip and Take. They are familiar, they work, and for a table with a few thousand rows no one notices the cost. Add a few hundred thousand rows, point a load test at page 500, and watch what happens to your query times and CPU on the PostgreSQL host.
Offset pagination and keyset pagination both deliver pages of data, but they do it in fundamentally different ways with very different performance curves and API surface areas. This post compares them with real EF Core code against PostgreSQL so you can choose deliberately rather than by default.
You will walk away with working implementations of both approaches, an understanding of where each one breaks down, and a decision framework you can use immediately.
Offset pagination is as old as SQL itself. The OFFSET n ROWS FETCH NEXT m ROWS ONLY clause (or PostgreSQL's LIMIT m OFFSET n) instructs the database to scan through the first n rows and discard them before returning the next m. It is the natural fit for numbered pages and random access ("jump to page 47").
Keyset pagination, sometimes called cursor-based or seek-method pagination, avoids OFFSET entirely. Instead of telling the database how many rows to skip, the query filters to rows whose sort key is greater than (or less than) the last value seen on the previous page. The database never scans rows you do not need.
Both patterns sit on top of the same PostgreSQL B-tree index structures. The difference is how much of that index each query is forced to walk.
Both snippets retrieve a page of products ordered by Id. The offset version accepts a page number; the keyset version accepts the last Id seen on the previous page.
Offset pagination approach
// Request model
public record OffsetPageRequest(int Page = 1, int PageSize = 20);
// Response model
public record PagedResult<T>(List<T> Items, int TotalCount, int Page, int PageSize);
// EF Core query
public async Task<PagedResult<ProductSummary>> GetPageOffsetAsync(
OffsetPageRequest request, CancellationToken ct)
{
var query = _db.Products.OrderBy(p => p.Id);
var totalCount = await query.CountAsync(ct);
var items = await query
.Skip((request.Page - 1) * request.PageSize)
.Take(request.PageSize)
.Select(p => new ProductSummary(p.Id, p.Name, p.Price))
.ToListAsync(ct);
return new PagedResult<ProductSummary>(items, totalCount, request.Page, request.PageSize);
}Keyset pagination approach
// Request model — cursor replaces page number
public record KeysetPageRequest(int? AfterCursor, int PageSize = 20);
// Response model — includes next cursor
public record CursorPageResult<T>(List<T> Items, int? NextCursor, bool HasMore);
// EF Core query
public async Task<CursorPageResult<ProductSummary>> GetPageKeysetAsync(
KeysetPageRequest request, CancellationToken ct)
{
// Fetch one extra row to determine whether a next page exists
var items = await _db.Products
.Where(p => request.AfterCursor == null || p.Id > request.AfterCursor)
.OrderBy(p => p.Id)
.Take(request.PageSize + 1)
.Select(p => new ProductSummary(p.Id, p.Name, p.Price))
.ToListAsync(ct);
var hasMore = items.Count > request.PageSize;
if (hasMore) items.RemoveAt(items.Count - 1);
var nextCursor = hasMore ? items[^1].Id : (int?)null;
return new CursorPageResult<ProductSummary>(items, nextCursor, hasMore);
}Notice that the keyset version fetches PageSize + 1 rows to detect the presence of a next page without a separate COUNT query. It also never needs to know how many total rows exist. These two differences are where most of the performance gap comes from.
1. Performance at scale
PostgreSQL must scan and discard the first n rows for every OFFSET n query. On a table with 500,000 rows and a page size of 20, page 500 requires PostgreSQL to read and discard 9,980 rows before returning the 20 you need. The work grows linearly with page depth. For an indexed sort key, the overhead is index scans rather than heap scans, but the work still accumulates.
Keyset pagination changes the access pattern entirely. The query is always WHERE id > $cursor ORDER BY id LIMIT 20. PostgreSQL uses an index range scan starting at $cursor and reads exactly 20 rows. Page depth becomes irrelevant because the cursor always puts the database at the right starting point in the index.
Does this matter to you? On tables under roughly 50,000 rows with well-cached indexes, the difference is rarely measurable in practice. On tables over 500,000 rows with deep page access, the difference can be one to two orders of magnitude. Measure against your actual data distribution before optimizing.
2. Consistency across pages
Offset pagination has a classic consistency problem: if a row is inserted or deleted between page 1 and page 2, page 2 will skip or duplicate a row. PostgreSQL offers no snapshot isolation at the query level for paginated reads without explicit transaction management.
Keyset pagination does not suffer from this problem in the typical case. Because it filters on a sort key value, insertions before the cursor position do not affect the next page, and deletions before the cursor position are simply absent. Deletions at or after the cursor can still cause a row to be skipped, but this is a much narrower window.
3. API surface complexity and client expectations
Offset pagination maps cleanly to what most API consumers expect: a page number, a page size, and a total count. Admin UIs, data export tools, and report generators need random access ("export page 47") that keyset pagination cannot satisfy without traversing all prior pages.
Keyset pagination trades random access for performance and consistency. Clients must store the cursor and send it back. "Jump to page 47" is not possible. This is an acceptable trade-off for feeds, infinite-scroll UIs, and high-volume API consumers, but it changes the client contract in a way that requires explicit buy-in.
Match the pagination strategy to the access pattern your clients actually need, not to what is easiest to implement.
Choose offset pagination when:
- Clients need random page access or user-controlled page numbers (admin dashboards, report generators)
- The table is small or page depth rarely exceeds 20 to 30 pages in practice
- Clients expect a total row count in the response for rendering pagination controls
- The data is largely static and consistency skips between pages are not a concern
Choose keyset pagination when:
- The table has more than 100,000 rows and clients may page deep into the result set
- The use case is a feed, infinite scroll, or a sequential data sync
- You need consistent pages despite concurrent inserts and deletes
- You want to eliminate the cost of a separate
COUNT(*)query on every page request
Both strategies can coexist in the same API. A product catalog endpoint used by an admin UI is a natural fit for offset pagination. An order history feed consumed by a mobile app is a natural fit for keyset. The controller implementation for each is independent.
// Composing both strategies on separate endpoints in the same controller
[HttpGet("admin")]
public Task<PagedResult<ProductSummary>> GetAdminPageAsync(
[FromQuery] OffsetPageRequest req, CancellationToken ct) =>
_paginationService.GetPageOffsetAsync(req, ct);
[HttpGet("feed")]
public Task<CursorPageResult<ProductSummary>> GetFeedAsync(
[FromQuery] KeysetPageRequest req, CancellationToken ct) =>
_paginationService.GetPageKeysetAsync(req, ct);| Concern | Offset pagination | Keyset pagination |
|---|---|---|
| PostgreSQL query cost at page 1 | Low | Low |
| PostgreSQL query cost at page 500 | High (scans and discards 9,980 rows) | Low (index seek at cursor position) |
| Random page access | Supported | Not supported |
| Total count in response | Requires a COUNT query | Not available without a COUNT query |
| Consistency under concurrent writes | Rows can be skipped or duplicated | Consistent for inserts before cursor |
| Client contract complexity | Simple: page number + page size | Moderate: cursor must be stored and sent |
| Best fit | Admin UIs, reports, static data | Feeds, infinite scroll, large tables |
Offset pagination is the right default for most admin and reporting endpoints where tables are small, page depth is shallow, and clients expect page numbers. It is not the right default for any endpoint where a large table will be paged through sequentially or at high volume.
Reach for keyset pagination whenever the access pattern is sequential (feeds, syncs, infinite scroll) or whenever you measure offset queries degrading beyond acceptable latency thresholds under realistic data volumes. The cursor-based API contract is a minor client-side inconvenience that pays for itself quickly at scale.
- Add sorting flexibility to keyset: Multi-column keyset pagination (sorting by
name, idrather than justid) requires a compound cursor. It is more complex but follows the same seek-method logic. - Benchmark on your data: Run both implementations against a table seeded with 500,000 rows and compare query plans with
EXPLAIN ANALYZE. The index seek vs. index scan difference becomes visible immediately. - Explore EF Core compiled queries: For high-frequency pagination endpoints, wrapping keyset queries in EF Core compiled queries eliminates the per-request LINQ expression tree compilation overhead.
No comments: