Backend Interview: How Would You Design Consistent Cursor Pagination Under Concurrent Writes?

Prompt and context

This question tests whether a backend engineer treats pagination as a stable read protocol. Orders, comments, and logs can be inserted, deleted, or updated between requests. Plain OFFSET depends on shifting positions and scans away many rows at deep pages. Cover ordering, cursor encoding, filter binding, consistency boundaries, indexes, and forward/backward semantics.

What the interviewer tests

Strong answers clarify whether the product needs jumps, counts, or real-time results, then choose keyset/cursor or offset. A cursor binds ordering and filters; its sort key is unique, stable, and indexed. The server signs and expires it. The answer explains why new rows do not duplicate the next page, how deletes affect results, and how next_cursor and end-of-list state are returned.

Questions to clarify

• What is the sort order? Can the sort field change, and what is the unique tie-breaker?
• Do we need previous-page navigation, arbitrary page jumps, exact counts, or forward infinite scroll only?
• Should reads use a frozen snapshot or allow an eventually consistent live list?
• Must filters, tenant scope, permissions, and sort order be bound into the cursor? How long is it valid?
• How are deletes, soft deletes, permission changes, and cross-shard reads handled?

30-second answer framework

“I would use a stable composite key such as (createdat, id) for descending keyset queries. The cursor is a signed opaque token containing the last sort values, filter hash, direction, and version. The server validates it and runs WHERE (createdat,id) < (:time,:id) against the matching composite index. New rows appear on refresh rather than being inserted into an already-read window; deletes may shorten a page but do not create duplicates. If absolute consistency is required, I would add a snapshot boundary and explain its cost.”

Step-by-step deep answer

Step 1: Define list semantics

Decide whether this is an audit history, live feed, or admin table. A history usually needs a stable boundary; a live feed may show new rows only after refresh. Do not promise real-time updates, arbitrary jumps, exact counts, and low cost simultaneously.

Step 2: Choose a stable sort key

Timestamps can tie or be edited, so add a unique id as a tie-breaker. Avoid display fields. If updates can move records, use an immutable creation sequence or explicitly document that rows can move between pages.

Step 3: Design an opaque cursor

Include sort values, direction, filter hash, API version, and expiry. Sign it or store it server-side; Base64 is encoding, not security. Reject a cursor when filters change instead of silently returning an unrelated page.

Step 4: Write the keyset query

For descending (createdat,id), the next page uses createdat < t OR (created_at = t AND id < id0) with the same composite index. Parameterize the query, cap limit, and never concatenate cursor values into SQL.

Step 5: Handle writes and deletes

Rows inserted after page one should not appear in page two; they appear after refresh. A deleted row may make a page shorter, which is an acceptable declared semantic. If omissions are unacceptable, use a snapshot boundary or versioned read.

Step 6: Bind permissions and filters

The cursor’s filter hash, tenant, and authorization scope must match the request. Recompute results when permissions tighten; an old cursor must not bypass access control. Cross-shard coordinators can merge local cursors, but the answer must state the amplification cost.

Step 7: Define response and errors

Return items, nextcursor, hasmore, and optionally a snapshot id. Expired or invalid cursors and changed filters use stable business errors; the client clears the cursor and restarts from page one. Do not expose SQL or internal database failures.

Step 8: Validate with concurrency tests

Test inserts, deletes, equal timestamps, filter changes, tampered cursors, and deep pages between requests. Verify no duplicates for one session, index seeks, and latency that does not grow linearly with page number. Track duplicate rate, skip rate, p95 latency, and cursor errors.

Query pseudocode

SELECT id, created_at, total
FROM orders
WHERE tenant_id = :tenant
  AND (created_at, id) < (:cursor_time, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size;

Trade-offs and boundaries

Cursor pagination solves position stability and query efficiency; it does not automatically solve cross-page business deduplication, permission changes, or updates that move rows. Search results also need a query version; aggregates may need a point-in-time read.

Rollout plan and evidence

Choose a high-read list and measure current duplicates, skips, deep-page latency, and count cost. Add a covering index, release versioned cursors, and add concurrent-write tests and metrics. Django REST framework documents cursor pagination as an opaque cursor; Hello Interview and TechInterview materials emphasize cursor/keyset stability on changing datasets.

Pilot exit criteria

Concurrent insert/delete tests meet the declared duplicate and omission semantics; deep-page p95 is stable; tampering and filter changes are rejected; clients recover safely to page one; and authorization review confirms tokens do not leak data.

How to prove the gain is real

At the same data size and write rate, compare offset and cursor p95/p99 latency, rows scanned, duplicate rate, omission rate, and database CPU. Separate cache effects so a single cold query does not decide the result.

Common mistakes and follow-ups

Treating Base64 as a secure cursor

Base64 is encoding. Clients can alter an id or tenant. Sign or store the token and bind filters, version, and expiry.

Sorting only by timestamp

Many rows can share a millisecond, making the boundary ambiguous. Add a unique tie-breaker and matching composite index.

Can a cursor jump to any page?

Standard cursors are not designed for arbitrary jumps. Offer bounded offset, precomputed anchors, or search-engine paging with explicit consistency and cost.

Can updates duplicate a row?

If the sort field changes, a row can move between pages. Use an immutable creation sequence or snapshot/version semantics and document the behavior.

How do you implement a previous-page button?

Keep a stack of prior cursors or run the reverse query and reverse the result on the server. The client should not infer cursor internals.

Must exact total count be returned?

No. Infinite scroll usually needs has_more; exact counts can be async or separate so every page does not scan the table.