Offset pagination: the item that disappears when the list changes between pages

A client asks for the first page with offset=0 and limit=2 and gets items 1 and 2. Before the second call, someone inserts a new item at the front of the list. Then the client asks for offset=2 and limit=2. The response comes back without an error and contains items 2 and 3. The client already has item 2. It will never get item 4, because pagination clients don't usually go back a page to check what they missed.

There is no HTTP server in this example. The "200" is only an analogy: the Java helper takes the list, returns a page and doesn't throw. That's what makes this bug annoying. Nothing fails. Page 2 arrives looking successful and repeats an item that page 1 already showed.

By the end you'll have seen:

• the duplicate and the skip reproduced with an ArrayList and two offset calls;
• why offset ties a page to an item's position and not to its identity;
• a keyset implementation (a cursor on the last id seen) where the next page is still [3, 4] after the same insert;
• the cases where offset is still the right choice.

Environment and versions

• JDK Temurin 25.0.4 (25.0.4+7-LTS), maven.compiler.release=25, no --enable-preview.
• Maven 3.9.12, maven-compiler-plugin 3.14.0, maven-surefire-plugin 3.6.0.
• junit-jupiter 6.1.3, test scope.
• No web framework and no database. All the code is in the devdojo.pagination package.

If you go looking for a Pageable in the JDK, you won't find one for pagination. The only one in Java SE 25 is java.awt.print.Pageable, which is for printing and has nothing to do with Spring Data's. That's why both helpers in this example are written by hand.

The pom.xml only needs this:

<properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <maven.compiler.release>25</maven.compiler.release>
</properties>
<dependencies>
  <dependency>
    <groupId>org.junit.jupiter</groupId>
    <artifactId>junit-jupiter</artifactId>
    <version>6.1.3</version>
    <scope>test</scope>
  </dependency>
</dependencies>
<build>
  <plugins>
    <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-compiler-plugin</artifactId>
      <version>3.14.0</version>
    </plugin>
    <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-surefire-plugin</artifactId>
      <version>3.6.0</version>
    </plugin>
  </plugins>
</build>

The model, and offset via subList

The item is just a record:

package devdojo.pagination;

public record Item(long id, String name) {}

Offset pagination does the same thing as LIMIT/OFFSET, only over a List:

package devdojo.pagination;

import java.util.List;

public final class Pages {
  private Pages() {}

  public static List<Item> offsetPage(List<Item> list, int offset, int limit) {
    if (offset < 0 || limit < 0) {
      throw new IllegalArgumentException("offset and limit must be >= 0");
    }
    int from = Math.min(offset, list.size());
    int to = Math.min(offset + limit, list.size());
    return List.copyOf(list.subList(from, to));
  }

  // keysetPage logo abaixo
}

(The comment in Portuguese just says "keysetPage below".)

The List.copyOf around subList is deliberate. subList doesn't make a copy. It returns a view of the original list. According to the Java 25 javadoc for List.subList), the view's semantics become undefined if the backing list is structurally modified in any other way. If you returned the view without copying it, a page you had already handed out could change later because someone modified the source list.

Look at what the method actually reads: offset, limit and size(). The item's id never comes up. The page is a window of indexes and has no idea which items sit inside it. Databases behave the same way. The PostgreSQL 18 documentation says "OFFSET says to skip that many rows before beginning to return rows", and the rows it skips are the ones in the current result, not the ones the client saw on the previous call.

Reproducing it: 2 shows up twice and 4 disappears

The list starts as [1, 2, 3, 4, 5]. Between the two pages, the test inserts item 6 at index 0:

@Test
void offset_insertAtZero_duplicatesPreviousItem() {
  List<Item> list = seed();
  List<Item> page0 = Pages.offsetPage(list, 0, 2);
  assertEquals(List.of(1L, 2L), ids(page0));

  list.add(0, new Item(6, "new-front"));
  List<Item> offsetPage1 = Pages.offsetPage(list, 2, 2);

  System.out.println("page0=" + ids(page0));
  System.out.println("offset-page1-after-insert=" + ids(offsetPage1));

  // Lista viva agora é [6,1,2,3,4,5]; offset 2/limit 2 é posicional: [2,3].
  // Analogia do HTTP 200: devolveu uma lista, nenhuma exceção.
  assertEquals(List.of(2L, 3L), ids(offsetPage1));
  List<Long> seen = new ArrayList<>(ids(page0));
  seen.addAll(ids(offsetPage1));
  assertTrue(seen.size() != new HashSet<>(seen).size(), "id 2 duplicated");
  assertTrue(!seen.contains(4L), "original id 4 skipped on the first two pages");
}

The two comments say: the live list is now [6,1,2,3,4,5], offset 2/limit 2 is positional, so [2,3]; and that's the HTTP 200 analogy, since a list came back and nothing was thrown.

These are the seed() and ids(...) helpers:

private static List<Item> seed() {
  List<Item> list = new ArrayList<>();
  list.add(new Item(1, "one"));
  list.add(new Item(2, "two"));
  list.add(new Item(3, "three"));
  list.add(new Item(4, "four"));
  list.add(new Item(5, "five"));
  return list;
}

private static List<Long> ids(List<Item> page) {
  return page.stream().map(Item::id).toList();
}

Surefire captured this output:

page0=[1, 2]
offset-page1-after-insert=[2, 3]

What happened is simple. After the insert, the list is [6, 1, 2, 3, 4, 5] and every item moved one position to the right. offset 2 still takes indexes 2 and 3, but those now hold ids 2 and 3. OFFSET 2 doesn't mean "after item 2". It means "skip two, whoever they are right now". It counts rows without knowing which rows they are.

Markus Winand puts it bluntly in Use The Index, Luke: no-offset. An insert between pages makes OFFSET return duplicates, and his summary is: "The idea to use the number of rows seen to skip over them later is simply wrong."

The duplicate at least shows up on screen, and somebody eventually complains. The skip goes unnoticed. Item 4 isn't on either page, and nobody files a ticket about an item they never saw.

Keyset: the next page starts after the last id you saw

The fix is to replace "how many items have I seen" with "which item did I see last". The cursor is the id of the last item on the previous page. The helper finds that item in the list and returns whatever comes after it:

public static List<Item> keysetPage(List<Item> list, long afterId, int limit) {
  if (limit < 0) {
    throw new IllegalArgumentException("limit must be >= 0");
  }
  int start = 0;
  if (afterId != 0L) {
    start = -1;
    for (int i = 0; i < list.size(); i++) {
      if (list.get(i).id() == afterId) {
        start = i + 1;
        break;
      }
    }
    if (start < 0) {
      throw new IllegalArgumentException("unknown cursor id " + afterId);
    }
  }
  if (start >= list.size()) {
    return List.of();
  }
  int to = Math.min(start + limit, list.size());
  return List.copyOf(list.subList(start, to));
}

The helper relies on two assumptions. List order is the pagination order (think insertion order or created_at), and ids are unique. afterId == 0 means start from the beginning. If the cursor id isn't in the list, the helper throws. The other option would be to quietly fall back to the first page, and we'd rather have the exception.

The shortcut that brings the bug back

The most obvious keyset version would be something like "take the first N items with id > afterId, in the list's current order". In this example, that's wrong. Item 6 has the largest id of all, but it was inserted at the front. In [6, 1, 2, 3, 4, 5], an id > 2 filter applied in list order picks up 6 first, and the new item ends up on page 2. The symptom is different but it's the same bug.

In SQL, the seek works because the predicate and the ORDER BY use the same key. Winand shows this in fetch-next-page, with (sale_date, sale_id) < (?, ?) and the sort on those same columns. In the in-memory list, order comes from position, not from the id. That's why keysetPage finds the cursor row and walks forward from it instead of comparing ids.

The same Winand page also warns that the cursor key has to be unique. A cursor on a timestamp alone goes back to duplicating or skipping items when two records share the same value. That can't happen in this example because id is unique.

The same insert, with keyset

@Test
void keyset_insertAtZero_nextPageUnchanged() {
  List<Item> list = seed();
  List<Item> page0 = Pages.offsetPage(list, 0, 2);
  long afterId = page0.getLast().id();

  list.add(0, new Item(6, "new-front"));
  List<Item> keysetPage1 = Pages.keysetPage(list, afterId, 2);

  System.out.println("page0=" + ids(page0));
  System.out.println("keyset-page1-after-insert=" + ids(keysetPage1));

  assertEquals(List.of(1L, 2L), ids(page0));
  assertEquals(List.of(3L, 4L), ids(keysetPage1));
  List<Long> seen = new ArrayList<>(ids(page0));
  seen.addAll(ids(keysetPage1));
  assertEquals(List.of(1L, 2L, 3L, 4L), seen);
  assertEquals(seen.size(), new HashSet<>(seen).size());
}

Output:

page0=[1, 2]
keyset-page1-after-insert=[3, 4]

Item 6 went in before the cursor. The cursor points at item 2, not at position 2, so the next page is still whatever comes after item 2.

The four observed sequences

Running mvn -q test on the fixture, the Surefire report TEST-devdojo.pagination.OffsetVsKeysetTest.xml recorded tests=5, failures=0, errors=0, skipped=0. These were the sequences for each scenario:

The first row explains why this bug survives manual testing. As long as the list doesn't change, position and identity line up, and offset and keyset return the same thing. Clicking "next" twice on a quiet dev database proves exactly that and nothing more.

When offset is still the better choice

Offset works on an immutable snapshot. Offset isn't the problem. The problem is using offset on a list that changes between calls. The List.copyOf javadoc) guarantees that if the original collection is modified afterwards, the copy won't reflect those changes:

@Test
void offset_onImmutableSnapshot_isStable() {
  List<Item> live = seed();
  List<Item> snapshot = List.copyOf(live);
  List<Item> page0 = Pages.offsetPage(snapshot, 0, 2);
  live.add(0, new Item(6, "new-front"));
  List<Item> page1 = Pages.offsetPage(snapshot, 2, 2);
  assertEquals(List.of(1L, 2L), ids(page0));
  assertEquals(List.of(3L, 4L), ids(page1));
  assertEquals(List.of(6L, 1L, 2L, 3L, 4L, 5L), ids(live));
}

The live list became [6, 1, 2, 3, 4, 5], and the snapshot still returned [1, 2] and then [3, 4].

Numbered pages need offset. An admin screen that shows "page 7 of 40" and lets the user jump straight to page 7 won't work with a seek. Winand says so explicitly: keyset can't fetch an arbitrary page. A cursor also doesn't give you the total item count. With keyset you can tell whether there's a next page, but the total still comes from a size() or a COUNT:

@Test
void cursorDoesNotReplaceTotalCount() {
  List<Item> list = seed();
  List<Item> page = Pages.keysetPage(list, 0L, 2);
  assertEquals(List.of(1L, 2L), ids(page));
  int totalCount = list.size();
  boolean hasMore = !Pages.keysetPage(list, page.getLast().id(), 1).isEmpty();
  assertEquals(5, totalCount);
  assertTrue(hasMore);
}

On the SQL side, the PostgreSQL documentation also notes that rows skipped by OFFSET still have to be computed, and that a large OFFSET might be inefficient. This article didn't measure that, so treat it as a note and not a result.

Recommendation

DevDojo would use offset in only two cases: on an immutable snapshot, or in a UI that has to show "page 7 of 40", with the total coming from a separate COUNT. For a feed or an API where the client keeps paging while the list changes, use keyset, with the cursor on a unique, monotonic key.

The sign that it's time to drop offset: you can't say which row "offset 20" refers to without counting everything again from the start.

Next step

If your collection can get items added at the front between one request and the next, stop handing page=2 to the client. Return the id of the page's last item as the cursor, or take a snapshot with List.copyOf and paginate over that. Then write this article's test for your own case: fetch the first page, insert an item at index 0, fetch the second page, and check that no id appears twice and none is missing.