Keeping API Pagination Consistent While Records Change

Pagination is a common feature in APIs that return large collections of records. Instead of sending thousands of database rows in a single response, an API divides the results into smaller pages that clients can request as needed.

For example, an order management API might return 50 orders per request. The client reads the first page, requests the next page, and continues until it has loaded the required records.

This approach works well when the underlying data remains unchanged. The problem starts when records are inserted, deleted, or updated while the client is moving through the pages.

A new record may shift the position of existing records. A deleted record may cause another record to move into an already processed position. An updated record may move from one page to another, resulting in duplicates or missing results.

Reliable pagination requires a stable ordering strategy and a clear definition of how changes should affect an ongoing read. In this article, we will explore why pagination becomes inconsistent, how offset and cursor-based pagination differ, and how to implement safer pagination in C# with ASP.NET Core and Entity Framework Core.

Why Pagination Becomes Inconsistent

Consider an API that returns orders sorted by their creation time, with the newest orders appearing first.

Initially, the database contains these records:

Position	Order ID	Created At
1	105	10:05
2	104	10:04
3	103	10:03
4	102	10:02
5	101	10:01
6	100	10:00

The client requests the first three records and receives orders 105, 104, and 103.

Before it requests the second page, a new order, 106, arrives. The database now returns:

Position	Order ID	Created At
1	106	10:06
2	105	10:05
3	104	10:04
4	103	10:03
5	102	10:02
6	101	10:01
7	100	10:00

If the client requests the second page using an offset of three, it receives orders 103, 102, and 101. Order 103 appears twice because the newly inserted record shifted the original results.

Deletion can cause the opposite problem. If a record from the first page is deleted before the second request, the remaining records shift toward the beginning of the result set. An offset-based request may then skip a record that the client has not yet received.

Updates can also cause problems when the field used for sorting changes. A record can move from a later page to an earlier page, or the other way around.

These issues are not necessarily database errors. Each individual query can return correct results for the data that exists when that query executes. The inconsistency comes from treating multiple independent queries as if they represented one unchanged result set.

Understand Offset-Based Pagination

Offset pagination is widely used because it is easy to understand and implement. The client specifies how many records to skip and how many to return.

For example, a request might use:

GET /api/orders?page=3&pageSize=50

The application calculates the offset as:

[
\text{Offset} = (\text{PageNumber} - 1) \times \text{PageSize}
]

The corresponding Entity Framework Core query looks like this:

public async Task<List<Order>> GetOrdersAsync(
    int page,
    int pageSize,
    CancellationToken cancellationToken)
{
    page = Math.Max(page, 1);
    pageSize = Math.Clamp(pageSize, 1, 100);

    return await _dbContext.Orders
        .AsNoTracking()
        .OrderByDescending(o => o.CreatedAt)
        .ThenByDescending(o => o.Id)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync(cancellationToken);
}

The ordering includes both CreatedAt and Id. This matters because multiple orders can share the same creation timestamp. Adding a unique tie-breaker makes the ordering deterministic for a particular database state.

However, deterministic ordering does not eliminate inconsistencies caused by changes between requests. The offset still refers to a position in the current result set, not to the last record the client actually received.

Offset pagination also becomes less efficient at large offsets because the database may need to process or skip many preceding rows. The exact cost depends on the database, query plan, indexes, and filters.

Use offset pagination when users need direct access to numbered pages and the dataset is reasonably stable. For large, frequently changing collections, cursor-based pagination is often a better choice.

Use Cursor-Based Pagination

Cursor-based pagination identifies where the next page should begin by using values from the last record received. Instead of asking the database to skip the first 100 records, the client asks for records that come after a particular ordering key.

This is sometimes called keyset pagination.

For example, if the last record on the current page has a creation time of 10:03 and an ID of 103, the next request asks for records older than that position in the descending order.

Newer records inserted ahead of the cursor do not shift the cursor's position. The next page continues from the same ordering boundary.

A cursor must represent the complete ordering key. If the query sorts by CreatedAt and Id, the cursor needs both values.

Implement Cursor Pagination in ASP.NET Core

Suppose the Order entity has a unique numeric ID and a creation timestamp. The following endpoint uses those fields to retrieve the next page.

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

[ApiController]
[Route("api/orders")]
public sealed class OrdersController : ControllerBase
{
    private readonly AppDbContext _dbContext;

    public OrdersController(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    [HttpGet]
    public async Task<IActionResult> GetOrders(
        [FromQuery] DateTimeOffset? beforeCreatedAt,
        [FromQuery] long? beforeId,
        [FromQuery] int pageSize = 50,
        CancellationToken cancellationToken = default)
    {
        pageSize = Math.Clamp(pageSize, 1, 100);

        if (beforeCreatedAt.HasValue != beforeId.HasValue)
        {
            return BadRequest(
                "Both cursor values must be supplied together.");
        }

        IQueryable<Order> query = _dbContext.Orders
            .AsNoTracking();

        if (beforeCreatedAt.HasValue)
        {
            DateTimeOffset cursorTime =
                beforeCreatedAt.Value;

            long cursorId = beforeId!.Value;

            query = query.Where(o =>
                o.CreatedAt < cursorTime ||
                (o.CreatedAt == cursorTime &&
                 o.Id < cursorId));
        }

        var orders = await query
            .OrderByDescending(o => o.CreatedAt)
            .ThenByDescending(o => o.Id)
            .Take(pageSize + 1)
            .ToListAsync(cancellationToken);

        bool hasMore = orders.Count > pageSize;

        if (hasMore)
        {
            orders.RemoveAt(orders.Count - 1);
        }

        var lastOrder = orders.LastOrDefault();

        return Ok(new
        {
            Items = orders,
            HasMore = hasMore,
            NextCursor = lastOrder is null
                ? null
                : new
                {
                    CreatedAt = lastOrder.CreatedAt,
                    Id = lastOrder.Id
                }
        });
    }
}

The query requests one additional record beyond the page size. If the database returns 51 records for a page size of 50, the endpoint knows another page exists and removes the extra record before returning the response.

The NextCursor contains the timestamp and ID of the last returned order. The client supplies both values on its next request.

For example:

GET /api/orders?beforeCreatedAt=2026-10-14T10%3A03%3A00Z&beforeId=103&pageSize=50

The exact timestamp format depends on the API's serialization rules. In a real application, use the database's timestamp precision consistently. If the database stores timestamps at lower precision than the client supplies, cursor comparisons can produce unexpected results.

This example assumes CreatedAt is immutable and Id is unique. It also assumes that the timestamp comparison performed by the database matches the application's intended ordering.

The code returns an illustrative cursor object for clarity. For a public API, consider encoding the cursor as an opaque token so clients do not depend on its internal representation. If clients must not alter cursor values, protect the token with a signature or authenticated encryption. Even a protected cursor does not replace authorization or query filtering.

Why Cursor Pagination Is More Stable, but Not a Snapshot

Cursor pagination prevents a new record inserted ahead of the cursor from shifting the next page's starting position. It also avoids the growing skip cost associated with large offsets in many query plans.

However, cursor pagination does not automatically provide a consistent snapshot of the entire collection.

Suppose the API sorts orders by CreatedAt and Id, but an existing order's CreatedAt value changes while the client is paging. That order can move across the cursor boundary. Depending on the timing, the client might see it twice or miss it.

Similarly, a record inserted with a sort key older than the cursor can appear on a later page even though it did not exist when the first page was requested.

A useful distinction is:

Stable cursor: The next request continues from a specific ordering key.
Consistent snapshot: Every page reflects the same logical view of the dataset.

These are different guarantees. Cursor pagination is generally more resilient to concurrent inserts, but applications that require a complete, repeatable view need an explicit snapshot strategy.

Use a Snapshot When Every Page Must Represent the Same Data

Some workflows require stronger consistency than ordinary cursor pagination provides. Examples include financial exports, audit reports, compliance extracts, and bulk processing jobs that must represent a defined point in time.

One approach is to use database snapshot capabilities or an explicit version boundary. The correct implementation depends on the database and its data model.

For example, a versioned system may assign each change a monotonically increasing version. When the first page is requested, the application records a snapshot version. Later pages include that version in their filters so records created or modified after the snapshot are excluded from the result.

Another option is to materialize the IDs of the records included in an export. Subsequent processing uses that fixed list rather than repeatedly querying a changing collection.

Holding a database transaction open across multiple HTTP requests is usually not a good default. It can retain locks or row versions for too long, consume resources, and become difficult to recover if a client disconnects.

A snapshot strategy must also define how updates and deletions are handled. Filtering by creation time alone does not reconstruct an earlier state if existing records can change or disappear. True historical consistency may require temporal tables, versioned records, a database snapshot mechanism, or materialized results.

Add the Right Database Indexes

Cursor pagination performs best when the database can use an index that matches the filter and ordering columns.

For the example query, an index beginning with CreatedAt and followed by Id can help the database locate the next set of records without scanning and skipping a large number of preceding rows.

For SQL Server, an index could be defined as follows:

CREATE INDEX IX_Orders_CreatedAt_Id
ON dbo.Orders (CreatedAt DESC, Id DESC);

The ideal index depends on the query's filters. If the endpoint restricts orders by tenant or customer, those columns may also need to be included in the index, in an order that matches the query's access pattern.

Do not add indexes without reviewing the actual execution plan. Indexes improve some reads but add storage requirements and overhead to inserts, updates, and deletes.

Common Mistakes to Avoid

Mistake	Why it causes problems	Better approach
Ordering by a non-unique field	Records with equal values can appear in an unpredictable order	Add a unique tie-breaker
Assuming offsets remain stable	Inserts and deletes shift row positions	Use cursor-based pagination for changing datasets
Using an incomplete cursor	The next page may skip or repeat records with equal sort values	Include every field required by the ordering
Allowing sort keys to change	Records can move across cursor boundaries	Prefer immutable ordering keys or use a snapshot
Returning an unlimited page size	Large responses can increase memory usage and database load	Enforce a maximum page size
Trusting client-supplied cursor values	Clients can request unexpected positions or query ranges	Validate and optionally protect opaque cursors
Holding a transaction open across user requests	Long-lived transactions can retain resources and interfere with other work	Use a database-supported snapshot or materialized result strategy
Ignoring concurrent changes during exports	Different pages may represent different versions of the data	Define and implement an explicit snapshot boundary

Best Practices for Production APIs

Choose offset pagination for small, relatively stable datasets and cursor pagination for large or frequently changing collections.
Always define a deterministic sort order with a unique tie-breaker.
Use immutable sort keys when possible.
Keep cursor fields consistent with the database's data types and timestamp precision.
Apply the same authorization, tenant, and filtering rules on every page.
Cap page sizes and validate cursor input.
Create indexes that support the query's filters and ordering.
Use a snapshot or materialized result when the business requirement calls for a consistent export.
Test pagination while records are inserted, deleted, and updated.
Document whether the API guarantees only stable traversal or a consistent snapshot.

Advantages and Limitations

Advantages

Cursor pagination avoids many offset-related duplicates and omissions caused by inserts ahead of the cursor.
Keyset queries can perform better than large offsets when supported by suitable indexes.
Deterministic ordering makes pagination behavior easier to reason about.
Snapshot strategies can support repeatable exports and audit workloads.
Bounded page sizes help control response size and resource consumption.

Limitations

Cursor pagination is less convenient when users need to jump directly to an arbitrary page number.
Cursor tokens must preserve all required ordering values.
Updates to ordering fields can still move records across the cursor boundary.
Cursor pagination alone does not guarantee a consistent snapshot.
Snapshot implementations may require database-specific features, versioned data, or additional storage.

Summary

API pagination becomes difficult when the underlying records change between requests. Offset pagination relies on row positions, so new records and deletions can shift those positions and cause duplicates or missing records.

Cursor-based pagination provides a more stable way to continue from the last record received. It works best with a deterministic ordering that includes a unique tie-breaker, immutable sort keys, and suitable database indexes.

However, stable traversal is not the same as a consistent snapshot. When an export or report must represent one logical point in time, use a database-supported snapshot, version boundary, or materialized result set.

The key is to choose the pagination strategy based on the consistency your API actually promises, then test that behavior while records are changing.