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.