Efficient Pagination in ASP.NET Core: Offset vs Keyset Pagination Explained

Pagination is essential for APIs that return large datasets. Without it, endpoints may attempt to load thousands of records into memory, resulting in slow responses, increased database load, excessive network usage, and poor user experience. As applications grow, efficient pagination becomes critical for maintaining performance and scalability.

The two most common pagination techniques are Offset Pagination and Keyset (Seek) Pagination. While Offset Pagination is simple to implement, it can become inefficient as datasets grow. Keyset Pagination provides significantly better performance for large tables by avoiding unnecessary row scans.

Rather than always using Skip() and Take(), this article explains how both pagination strategies work, their advantages and limitations, and when each approach is appropriate.

Note: Pagination is not just about limiting response size. A well-designed pagination strategy minimizes database work while providing a predictable experience for API consumers.

Why Pagination Matters

Without pagination, APIs can suffer from:

Slow response times
High memory consumption
Increased database load
Large network payloads
Poor user experience
Timeout exceptions

Efficient pagination ensures applications remain responsive even as data volume grows.

Common Pagination Strategies

The following approaches are commonly used.

Strategy	Best For
Offset Pagination	Admin dashboards
Keyset Pagination	Infinite scrolling
Cursor Pagination	Large distributed systems
Page Number Pagination	Traditional web applications

Each strategy offers different trade-offs between simplicity and performance.

Offset vs Keyset Pagination

Feature	Offset Pagination	Keyset Pagination
Easy to Implement	✅	Moderate
Performance on Large Tables	Lower	Excellent
Supports Random Page Access	✅	❌
Suitable for Infinite Scroll	Limited	✅
Uses Skip()	✅	❌

Offset pagination works well for small datasets, while keyset pagination is generally preferred for high-traffic APIs.

Understanding Offset Pagination

Offset pagination skips a specified number of rows before returning results.

var products =
    await context.Products
        .OrderBy(p => p.Id)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

For example, requesting page 5 with a page size of 20 causes the database to skip the first 80 rows before returning the next 20.

SQL Behind Offset Pagination

EF Core generates SQL similar to:

SELECT *
FROM Products
ORDER BY Id
OFFSET 80 ROWS
FETCH NEXT 20 ROWS ONLY;

As the offset grows, the database must scan and discard more rows, increasing query cost.

Understanding Keyset Pagination

Keyset pagination retrieves records after the last known value instead of skipping rows.

var products =
    await context.Products
        .Where(p => p.Id > lastId)
        .OrderBy(p => p.Id)
        .Take(pageSize)
        .ToListAsync();

Only the required rows are read, making the query much more efficient on large datasets.

Pagination Workflow

flowchart LR

A[Client Request]
B{Pagination Type}
C[Offset Query]
D[Keyset Query]
E[(Database)]
F[Paginated Results]

A --> B
B --> C
B --> D
C --> E
D --> E
E --> F

Both approaches return a limited dataset, but they differ significantly in how the database locates those records.

Returning Pagination Metadata

Include useful metadata in API responses.

Example response:

{
  "page": 3,
  "pageSize": 20,
  "totalRecords": 250,
  "totalPages": 13,
  "items": []
}

Providing pagination metadata simplifies client-side navigation.

Sorting Before Pagination

Always apply sorting before pagination.

var products =
    await context.Products
        .OrderBy(p => p.CreatedDate)
        .Take(20)
        .ToListAsync();

Without a deterministic sort order, clients may receive duplicate or missing records between requests.

Choosing the Right Strategy

Scenario	Recommended Strategy
Product catalog	Keyset Pagination
Activity feed	Keyset Pagination
Admin dashboard	Offset Pagination
Reporting system	Offset Pagination
Mobile infinite scrolling	Keyset Pagination

Select the strategy based on user experience requirements and expected data volume.

Common Production Mistakes

Problem	Root Cause
Slow page navigation	Large Skip() values
Duplicate records	Missing sort order
Missing records	Unstable ordering column
High database CPU	Offset pagination on massive tables
Inconsistent results	Data modified between requests
Large response payloads	Excessive page sizes

Many pagination performance problems originate from inefficient database queries rather than the pagination logic itself.

Best Practices

Prefer keyset pagination for large datasets.
Always sort results before paginating.
Limit maximum page size.
Index columns used for sorting.
Return pagination metadata.
Validate page number and page size.
Benchmark pagination performance on production-sized data.

Common Anti-Patterns

Avoid these common mistakes:

Using Skip() with millions of rows.
Returning entire tables in a single request.
Allowing unlimited page sizes.
Paginating without an OrderBy() clause.
Sorting on non-indexed columns.
Ignoring pagination performance during testing.

FAQ

Which pagination strategy is faster?

Keyset pagination is generally faster for large datasets because it avoids scanning and discarding rows before returning results.

Can keyset pagination jump directly to page 100?

No. Keyset pagination is designed for sequential navigation using the last retrieved value rather than page numbers.

Is offset pagination still useful?

Yes. Offset pagination works well for administrative interfaces, reporting tools, and scenarios where users need to jump directly to a specific page.

Should pagination always include total record counts?

Not necessarily. Calculating total counts on very large tables can be expensive. For infinite scrolling APIs, returning a continuation token or indicating whether more data exists may be sufficient.

Conclusion

Efficient pagination is a key component of building scalable ASP.NET Core APIs. While Offset Pagination offers a familiar and straightforward implementation, its performance degrades as datasets grow. Keyset Pagination, by contrast, provides significantly better scalability for high-volume applications by leveraging indexed lookups instead of row offsets.

By choosing the appropriate pagination strategy, applying consistent sorting, and returning useful pagination metadata, you can build APIs that remain fast, predictable, and responsive regardless of database size.