Practice a Pagination API Interview With Concurrent Inserts

TL;DR

• In this fictional activity-feed exercise, concurrent inserts can make offset pagination repeat or skip rows.
• Use stable ordering and define how new or changed rows affect continuation.
• Treat a cursor as state rather than authorization, and test filter changes and concurrent sequences without claiming exactly-once processing.

Practice pagination while the list changes

Use this fictional prompt: a user scrolls through an activity feed ordered newest first. New rows arrive between page requests. An offset-based implementation sometimes repeats an item or skips one. Explain why and propose a pagination contract that fits the product.

Start by asking what the user expects. Is this a live feed, a stable export or a review queue where every item must be processed exactly once? Those products need different consistency guarantees. A cursor can improve navigation through a changing list, but it does not automatically create a frozen snapshot.

Trace the offset failure with a small list

Suppose the first page contains items numbered 10 through 6 in descending order. Before the next request, item 11 is inserted at the front. Skipping five rows now begins at item 6, which the user already saw. A deletion before the offset can shift the boundary in the other direction.

The example uses identifiers as a simple visible order, but real feeds often order by time. PostgreSQL's LIMIT and OFFSET documentation explains the importance of a predictable order and notes the work involved in skipped rows. The concurrent-change problem adds another reason to define the boundary explicitly.

Do not fix the example by assuming no one writes while the user scrolls unless the product can actually enforce that condition.

Use a stable ordering tuple

For a newest-first feed, one candidate order is (created_at DESC, id DESC), where the identifier breaks ties. The cursor records the last tuple returned. The next page selects rows strictly after that boundary in the descending traversal.

A simplified PostgreSQL-shaped predicate is:

WHERE tenant_id = :tenant
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size;

Assume the ordering values are non-null and the comparison types are appropriate. Explain the tie-breaker instead of using timestamp alone; several items can share the same time. The cursor also needs to correspond to the selected filters and sort order, not merely contain an arbitrary row identifier.

Define what new and changed rows do

With an immutable creation-time order, new rows at the front do not shift the next-page boundary. They may appear when the user refreshes from the beginning. A backdated insert below the boundary may still appear on a later page. State that behavior rather than claiming the user sees an exact snapshot.

If a row's ordering field changes, it can move across the boundary and be repeated or missed under a live traversal. Consider using an immutable ordering field or a snapshot mechanism when the product needs stronger guarantees. Deletions can make an item disappear; no pagination method can return a deleted record without some retained snapshot or history.

Our system design frameworks guide can help connect these choices with the actual product requirement. A feed refresh and a financial export should not inherit the same contract by accident.

Treat the cursor as continuation state, not authorization

An opaque cursor can encode the boundary and relevant version information. Validate it and ensure the request still applies the authenticated tenant and permission checks. A cursor from another user's list must not become a way to bypass those filters.

If you sign a cursor to detect modification, explain that signing does not make its contents secret. If the contents are sensitive, choose an appropriate design rather than relying on an unreadable-looking encoding. Avoid placing unnecessary personal data in the token.

Define what happens when filters change or the cursor format becomes unsupported. A clear restart response is preferable to silently applying an old boundary to a different query and returning confusing results.

Choose an explicit policy for page-size changes. The same boundary may remain valid when the user asks for a smaller page, but filters and sort direction must still match the continuation contract. If the cursor expires, explain how the client restarts and communicates that change. A pagination API should not silently reinterpret expired continuation state as a valid beginning or a different dataset.

Rehearse a sequence of concurrent changes

Use a small handwritten dataset and walk through page one, a new insert, a deletion and page two. Include two rows with the same timestamp. Verify the proposed predicate against the expected order. This catches mistakes that a broad explanation of cursor pagination can hide.

Then change the requirement to a stable export. Explain what additional snapshot or materialization strategy you would consider, how long it remains available and what resource cost it introduces. Do not present cursor pagination alone as exactly-once processing.

Use the mock interview strategy guide to repeat the exercise with oldest-first ordering. A strong answer defines a total order, makes concurrent behavior explicit and keeps continuation separate from authorization. The result is a product contract users can understand, not just a different way to calculate the next query.