Backend Interview Question: Why Does Page 2 Repeat an Order From Page 1?

One new order can break an apparently correct endpoint. Here is how to trace the bug, write a cursor query, and explain the guarantee honestly.

You open an orders page, read three rows, and click Next. One of those orders appears again. Naturally, you suspect the frontend. Maybe the component appended the wrong response, or perhaps a retry slipped through. Those are reasonable possibilities, but imagine the network response itself contains the repeated order. Now the backend gets its little moment of attention.

Both requests succeeded. Both queries returned three rows. Nobody inserted a duplicate order into the database, and yet the customer is looking at one twice. This is the kind of interview problem I like because the SQL can look completely reasonable while the behavior is wrong. The interesting question is what page two means when the data changes after page one.

Let’s use an original practice scenario with a tiny order history, then build an answer that handles the follow-ups without pretending a cursor freezes time.

First, make the bug small enough to see

Suppose we list a customer’s orders newest first, three at a time. Initially, their IDs appear in this order.

106, 105, 104, 103, 102, 101

For this first illustration, larger IDs happen to belong to newer orders. We will use an explicit timestamp and ID together in the actual query.

Page one returns 106, 105, 104. The next request uses LIMIT 3 OFFSET 3, which means skip three rows in the result available to that request, then return three.

Before that request runs, a new order arrives at the front.

107, 106, 105, 104, 103, 102, 101

Skipping three now skips 107, 106, 105. Page two returns 104, 103, 102, so order 104 makes another appearance.

The database followed the instruction correctly. We asked it to skip positions in a changing list, not continue after the order the customer had last seen.

Deletion produces the opposite surprise. Start again with the original six rows and the same first page. If order 106 disappears before the next request, skipping three rows in the remaining list skips 105, 104, 103. Order 103 never reaches the customer.

Deduplicating responses in the browser can hide the repeated order, but it does not recover that missing one. Before adding a client-side patch, find out which guarantee the API is supposed to provide.

A stable order fixes one problem, not both

Every page needs a predictable order. Sorting only by creation time leaves a tie whenever two orders share that timestamp. Use a unique tie-breaker, such as ORDER BY created_at DESC, id DESC, and define both fields as non-null. For this example, creation time and ID are immutable, and ID is unique.

PostgreSQL’s LIMIT and OFFSET documentation explains why a predictable ordering matters and notes that skipped rows still have to be computed. Deep offsets can therefore add work as well as create awkward navigation behavior.

However, even a perfectly deterministic order does not prevent rows from moving to different positions after an insertion. The repeat we just traced survives the tie-breaker because the list itself changed. I like separating those two issues in an interview. We need an unambiguous order, and we need a continuation rule that does not depend on how many rows happen to precede us now.

Continue after the last row you actually returned

For forward browsing, save the ordering values of the last returned row. If that row has creation time 10:00 and ID 104, the continuation boundary contains both values. The next page asks for rows strictly after that boundary in our descending order. That means an earlier timestamp, or the same timestamp with a smaller ID.

Here is a PostgreSQL query using bound parameters. $1 is the authenticated customer's ID, $2 and $3 are the decoded cursor timestamp and ID, and $4 is the server-validated page size plus one.

SELECT id , created_at , total_cents FROM orders WHERE customer_id = $1 AND ( created_at , id ) < ( $2 , $3 ) ORDER BY created_at DESC , id DESC LIMIT $4 ;

The tuple comparison matches the descending ordering because both columns use the same direction. If you change the sort directions or allow nulls, revisit the predicate rather than copying this one unchanged.

For the first page, omit the boundary condition. With a page size of three, fetch up to four rows. Return the first three; the extra row tells you whether another result existed at that moment. Build the next cursor from the last row returned, not from the extra row you withheld. Otherwise, the next request skips a row before the customer has ever seen it. A later request may still be empty if data was deleted meanwhile, so has_more is not a reservation of future results.

In our insertion example, a new order before the saved boundary no longer changes which older orders follow it. Page two continues with 103, 102, 101.

Also, deleting the boundary row does not have to break navigation. The cursor holds its ordering values, so the query can compare against them without finding that row again.

Make the cursor a continuation token, not a permission slip

The token needs enough information to preserve the traversal. Include a format version, the timestamp at its original precision, the ID, and the relevant filter and sort identity. Treat large IDs carefully when encoding them for clients that cannot represent every integer exactly. If the customer changes a filter, start a new traversal or reject a cursor from the old one. Continuing a paid-orders query with a cursor created for all orders is a different contract, even if the SQL still runs.

An opaque, integrity-protected token can prevent clients from casually changing its contents. Base64 alone is only an encoding. Regardless of the token format, derive the customer scope from authentication and apply authorization on every request; do not trust a customer ID inside a cursor as access permission.

For this query, a candidate B-tree index is (customer_id, created_at DESC, id DESC). It puts the customer restriction before the ordered boundary, allowing an appropriate plan to seek near the cursor instead of walking through a large offset. That is a design candidate, not a promise that every production query will be fast. Check the query plan and representative data, especially if extra filters discard many rows. Selecting wide columns or adding joins changes the work too.

Offset pagination still has a place when the dataset is small or stable and jumping directly to a numbered page matters. A forward cursor does not offer cheap arbitrary page jumps. Choose the navigation behavior deliberately instead of treating one technique as universally superior.

A cursor cannot promise a snapshot it does not have

Here comes the follow-up that makes the answer more useful. Suppose the product team now wants a complete export of every order that existed when the user started. No additions, no disappearances, and values as they were at that moment. Our live cursor does not provide that.

With immutable sort keys and fixed filters, keyset pagination avoids the position-shift duplicate we traced. But a newly inserted, backdated row behind the cursor can still appear later. A new row ahead of the cursor will not appear during the remaining forward traversal. Deleted rows can disappear. If you instead sort by a mutable field such as last-updated time, an existing row can cross the boundary. Depending on the direction of that move, it may be missed or returned again.

For a fixed export, define a snapshot mechanism or materialize the result the job will export. Saving only IDs preserves membership, not necessarily the original values; a point-in-time export needs those values or a way to retrieve their historical versions too. A start-time cutoff can exclude some later inserts, but it is not a complete snapshot when timestamps can be backdated, rows can be deleted, or values can change. Long-lived database snapshots also have operational costs, so avoid holding a transaction open simply because a human might click Next eventually.

This is why I prefer explaining the browsing promise before choosing a pagination strategy. A live order history and a reconciliation export can look almost identical on a screen while requiring very different guarantees.

Test the boundary instead of staring at the happy path

Start with six rows, fetch a page, and save its cursor. Then change the data before asking for the next page. Insert a newer row and confirm that the original first-page rows do not repeat during forward traversal. Give multiple rows the same timestamp and confirm that the ID tie-breaker returns each eligible row in the correct order. Delete a row before the boundary, then delete the boundary row itself in a separate test. Neither case should require guessing a new offset. Change the filter and verify that the API resets the traversal or rejects the mismatched cursor according to its contract. Also test an empty result and an exact page-size result. The latter should not advertise another page merely because you returned three rows; that is why we fetched the extra one.

For practice, extend this paginated list and API exercise with the same tiny dataset. Define what its second page promises while you test insertion, deletion, and ties, then add loading states and backward navigation. This walkthrough covers the forward-query contract; the full exercise gives you room to design the surrounding UI too.

My goal is to leave the conversation with an explanation of what the user will observe, not just a query containing the word cursor somewhere nearby. Once you can trace that repeated order all the way from the data change to the response, pagination stops being a small UI detail. It becomes a clear, testable agreement about how someone moves through changing data.

Follow along for learning more questions and how to actually solve them. ~ Emily