Stable Pagination in .NET APIs: Offset Drift, Keyset Cursors, and Tie Breakers

Learning outcome

By the end of this lesson, you should be able to explain why offset pagination becomes unstable as data changes, design a cursor-based alternative that survives inserts and deletes, and describe how tie breakers make ordering deterministic in a .NET backend API. You should also be able to tell the difference between a pagination cursor, a historical snapshot, and exactly-once delivery.

Intuition

Imagine a small timestamped order feed for a commerce system. The feed is sorted by CreatedAt descending, then by OrderId descending to break ties. A client asks for page 1, gets 20 items, and later asks for page 2.

With offset pagination, page 2 means “skip the first 20 rows currently visible.” That sounds simple, but it is brittle. If a new order is inserted between requests, or if an older order is deleted, the meaning of “skip 20” changes. The client can see duplicates, miss items, or both.

A stable cursor instead says, “continue after the last row I already saw, using the same sort order.” That is why keyset pagination is also called seek pagination. It does not count rows; it resumes from a concrete position in the ordering.

For an order feed, a practical sort key is a composite key:

• CreatedAt for recency
• OrderId as a unique tie breaker

Without a unique tie breaker, two orders created in the same timestamp bucket may swap positions between requests. That creates non-deterministic page boundaries even if the underlying rows never change. Deterministic ordering is the foundation of stable pagination.

Deep dive

A robust .NET API usually exposes one of two pagination strategies:

1. Offset pagination: pageNumber and pageSize
2. Keyset pagination: a cursor containing the last seen sort key values

Offset pagination is easy for clients to understand, and it works well for admin dashboards or small data sets. But under concurrency it has what interviewers often call offset drift.

Here is the core problem in a feed sorted newest-first:

• Client requests page 1 and gets items 1–20
• A new order arrives at the top
• Client requests page 2 with skip=20
• With just that one insertion and no other changes, the first 20 rows are now the new order plus old items 1-19. Page 2 therefore starts with old item 20, duplicating the last item from the original page 1.

Deletes cause a different drift: if an item from page 1 is removed, then skip=20 may skip one too many rows and the client misses an item that was previously on page 2.

Keyset pagination avoids this positional drift by using a predicate based on the last seen key. For predictable forward traversal, prefer immutable ordering keys. If an existing row's sort values change across the cursor boundary, it can still be skipped or encountered again; a unique tie breaker alone does not solve that. For a descending feed ordered by (CreatedAt, OrderId) the next page uses a condition like:

WHERE (CreatedAt < @lastCreatedAt)
   OR (CreatedAt = @lastCreatedAt AND OrderId < @lastOrderId)
ORDER BY CreatedAt DESC, OrderId DESC

That predicate is the important idea, not the exact SQL flavor. It says: keep scanning from the point after the last item you saw, in the same total order.

In a .NET API, the cursor often serializes the last item’s sort key values into an opaque token. Clients should treat the cursor as an implementation detail, not as a business identifier. A good cursor is typically:

• opaque
• compact
• tamper-resistant if needed
• tied to a specific sort order

A key interview nuance: cursor pagination is not snapshot isolation. A cursor can help you move forward deterministically through a changing list, but it does not automatically freeze the data at a point in time. If the database changes between requests, later pages may include newly inserted rows or omit rows deleted after the first request. That is normal unless you explicitly design a snapshot-backed feed.

This distinction matters:

• Cursor pagination answers: “Where do I continue?”
• Snapshot isolation answers: “Am I reading the same historical view?”
• Exactly-once delivery answers: “Will I process each item exactly once?”

Those are different guarantees. A cursor alone does not provide exactly-once semantics. If a client retries a request, it may receive overlapping rows unless the API or consumer layer deduplicates by stable IDs. Likewise, if you need the user to see a frozen export or an audit-complete report, you need storage support for a versioned snapshot, transaction timestamp, or a materialized export table.

For a timestamped order feed, a practical API design might be:

• GET /orders?limit=20&after=...
• The server returns items sorted by CreatedAt DESC, OrderId DESC
• The response includes nextCursor
• The cursor encodes the last item’s (CreatedAt, OrderId) pair

Direction matters too. If the API supports both newest-first and oldest-first browsing, the cursor must encode the direction or be invalid outside the original ordering. You cannot safely reuse a cursor produced for descending order in ascending order, because the seek predicate changes.

Filtering also matters. If the client filters by status, customer, or tenant, the cursor must belong to that same filtered result set. A cursor built over status = shipped should not be replayed against status = pending. In practice, the filter criteria are often included in the signed cursor payload or validated separately on the server.

Tradeoffs to mention in interviews:

• Offset pagination is simpler and supports random access by page number, but it is unstable under concurrent writes.
• Keyset pagination is stable and scalable for deep paging, but it does not jump directly to page 57 without walking the cursor chain.
• A composite keyset cursor is only correct if the sort order is deterministic and total.
• If you need a historical snapshot, design for snapshot storage; do not assume cursors can reconstruct the past.

A clean mental model is: offset pagination counts rows; keyset pagination follows order.

Failure modes

Common mistakes include:

• Using offset pagination on a rapidly changing feed and assuming pages are repeatable
• Sorting only by timestamp when many rows share the same timestamp
• Encoding a cursor without the tie breaker, which causes missing or duplicated rows near page boundaries
• Reusing a cursor after changing sort direction
• Reusing a cursor after changing filters or tenant scope
• Claiming that cursor pagination guarantees exactly-once processing
• Claiming that a cursor gives a historical snapshot without dedicated storage support

Another subtle failure mode is non-deterministic ordering from the database itself. If your ORDER BY does not fully determine a unique order, different query plans can produce different relative row orderings among ties. That becomes visible as page jitter even when the underlying data is unchanged.

Interview drill

If asked to explain stable pagination in an interview, you can answer in three steps:

1. State that offset pagination drifts under concurrent inserts and deletes.
2. Describe keyset pagination using a composite ordering key, such as (CreatedAt DESC, OrderId DESC).
3. Clarify that cursors are continuation tokens, not snapshots, and that exactly-once delivery requires separate deduplication or storage guarantees.

A strong follow-up answer is to explain how you would encode and validate a cursor. Mention that the cursor should capture the last seen sort values, the direction, and the active filter context, ideally in a tamper-resistant opaque form.

Revision checklist

• Can I explain why offset pagination drifts when rows are inserted or deleted?
• Can I describe a composite keyset cursor for a timestamped order feed?
• Can I explain why a unique tie breaker is required?
• Can I distinguish cursor pagination from snapshot isolation?
• Can I distinguish cursor pagination from exactly-once delivery?
• Can I explain why the cursor must match sort direction and filters?
• Can I state when offset pagination is acceptable and when keyset pagination is preferred?