Why can offset pagination skip or duplicate rows, and how do Page, Slice, sort stability, and keyset pagination address the trade-offs at scale?

principalshould knowfreq 30%

answer

non-unique sort => ties reorder => skip/dup
append PK tie-breaker for total order
Page = count query; Slice = size+1, no count
deep OFFSET scans+discards N rows
keyset: WHERE (col,id) < cursor + index seek

basics

~20 s

Offset paging (page*size) is only correct if the sort order is total and stable — otherwise ties or concurrent inserts shift rows and pages overlap or skip. Add a unique tie-breaker like id. Page runs a costly count; deep offsets scan far. For large data, use keyset pagination.

solid answer

~50 s

Offset pagination fetches rows at OFFSET page*size. Two problems: (1) if the ORDER BY isn't a total order — e.g. sorting only by a non-unique column — the database may return tied rows in any order, so consecutive pages can duplicate or drop rows. Fix: append a unique tie-breaker (usually the primary key) to the Sort so ordering is deterministic. (2) Concurrent inserts/deletes shift offsets between requests, so a row can appear twice or be missed even with a stable sort. Additionally, Page<T> issues a count query (expensive on large tables) and high offsets force the DB to scan and discard many rows, degrading deep pages. Slice<T> avoids the count (fetches size+1 for hasNext). At scale, keyset/cursor pagination — WHERE (sortCol, id) > (:lastSort, :lastId) ORDER BY sortCol, id LIMIT size — replaces OFFSET with a seek on an indexed key, giving O(1)-ish deep pages and stability against inserts before the cursor.

code

java · 14 lines

java

// 1) Make offset paging deterministic with a unique tie-breaker
Sort stable = Sort.by(Sort.Order.desc("createdAt"), Sort.Order.desc("id"));
Page<Event> page = eventRepo.findByType("LOGIN", PageRequest.of(0, 20, stable));

// 2) Prefer Slice when totals aren't needed (no count query)
Slice<Event> feed = eventRepo.findByType("LOGIN", PageRequest.of(0, 20, stable));

// 3) Keyset (seek) pagination via Spring Data's scroll API (3.1+)
Window<Event> first = eventRepo.findByType("LOGIN",
        ScrollPosition.keyset(), PageRequest.ofSize(20).withSort(stable));
if (!first.isEmpty() && first.hasNext()) {
    Window<Event> next = eventRepo.findByType("LOGIN",
            first.positionAt(first.size() - 1), PageRequest.ofSize(20).withSort(stable));
}

go deeper

for a junior

Understand that pages need an ORDER BY and page 0 is first.

for a middle

Explain Page vs Slice count cost and why a tie-breaker matters for stable paging.

for a senior

Diagnose skip/duplicate from non-total sort and concurrent inserts; pick Slice to drop count queries.

for a principal

Choose offset vs keyset per dataset, enforce max page size and default deterministic sort, and reason about index support for seek pagination and consistency guarantees under writes.

How offset pagination works. PageRequest.of(page, size) maps to ... ORDER BY ... LIMIT size OFFSET page*size. The database produces the full ordered stream, skips offset rows, and returns the next size. This is simple but has correctness and performance pitfalls at scale.

Pitfall 1 — non-total (unstable) sort order. SQL only guarantees the order you specify. If you sort by a non-unique column (say ORDER BY createdAt) and many rows share a value, the database is free to emit tied rows in any order, and that order can differ between two queries (different plans, parallelism, buffer state). So page 1 and page 2 can overlap (a row shown twice) or skip a row entirely. The fix is to make the sort a total order by appending a unique tie-breaker — almost always the primary key: Sort.by("createdAt").descending().and(Sort.by("id").descending()). Now every row has a unique position and pages partition cleanly.

Pitfall 2 — concurrent modification shifts offsets. Even with a stable sort, offset pagination reads each page in a separate query at a later time. If rows are inserted or deleted before the current offset between requests, every subsequent row shifts by one — so a user scrolling can see a duplicate row or miss one. Offset paging is inherently a snapshot-per-request, not a consistent cursor.

Pitfall 3 — count-query cost (Page vs Slice vs List). The return type changes the work:

Page<T> runs an extra SELECT count(...) to compute getTotalElements()/getTotalPages(). On large or heavily-filtered tables this count can be as expensive as the data query itself, and it runs on every page request.
Slice<T> skips the count: it fetches size + 1 rows and, if the extra row exists, sets hasNext(). Ideal for infinite scroll where you never show a total.
List<T> returns just the rows — no count, no next flag. Choosing Slice (or List) instead of Page when totals aren't needed removes a whole query per request.

Pitfall 4 — deep-offset cost. OFFSET 1_000_000 LIMIT 20 still forces the database to generate and discard a million rows before returning 20. Offset cost grows linearly with page depth, so the last pages of a big dataset are slow regardless of indexing.

Keyset (cursor / seek) pagination — the scale answer. Instead of an offset, remember the sort values of the last row seen and seek past them:

sql

SELECT * FROM events
WHERE (created_at, id) < (:lastCreatedAt, :lastId)
ORDER BY created_at DESC, id DESC
LIMIT :size

With an index on (created_at, id) the database seeks directly to the cursor and reads forward — deep pages cost the same as the first. It's also stable against inserts/deletes before the cursor, because the cursor is a value, not a positional offset. Trade-offs: you can only go next/prev (no random jump to "page 500"), and no cheap total count. In Spring Data, keyset support exists via KeysetScrollPosition and the Window<T>/scroll(...) API (Spring Data 3.1+), or you implement the WHERE-tuple manually. Offset scrolling is also available via OffsetScrollPosition.

Putting it together — guidance.

Always give paginated queries a deterministic, total sort (include a unique tie-breaker).
Use Slice/List over Page unless the UI truly needs totals; count queries are not free.
For large tables or infinite scroll, prefer keyset pagination (Window/scroll or a manual seek) over deep offsets.
Enforce a maximum page size at the API boundary so a client can't request an unbounded page.
Be explicit that offset paging is eventually-consistent under concurrent writes; don't promise exact totals on volatile data.

Gotchas. ignoreCase() or expression-based sorts can defeat the index that keyset seeks rely on. A Sort alone doesn't limit rows. Native @Query with Page needs a matching countQuery. And the zero-based page index still bites when mapping from UIs.

**How offset pagination works.** `PageRequest.of(page, size)` maps to `... ORDER BY ... LIMIT size OFFSET page*size`. The database produces the full ordered stream, skips `offset` rows, and returns the next `size`. This is simple but has correctness and performance pitfalls at scale. **Pitfall 1 — non-total (unstable) sort order.** SQL only guarantees the order you specify. If you sort by a **non-unique** column (say `ORDER BY createdAt`) and many rows share a value, the database is free to emit tied rows in *any* order, and that order can differ between two queries (different plans, parallelism, buffer state). So page 1 and page 2 can **overlap** (a row shown twice) or **skip** a row entirely. The fix is to make the sort a **total order** by appending a unique tie-breaker — almost always the primary key: `Sort.by("createdAt").descending().and(Sort.by("id").descending())`. Now every row has a unique position and pages partition cleanly. **Pitfall 2 — concurrent modification shifts offsets.** Even with a stable sort, offset pagination reads each page in a *separate* query at a *later* time. If rows are inserted or deleted **before** the current offset between requests, every subsequent row shifts by one — so a user scrolling can see a duplicate row or miss one. Offset paging is inherently a snapshot-per-request, not a consistent cursor. **Pitfall 3 — count-query cost (Page vs Slice vs List).** The return type changes the work: - `Page<T>` runs an extra `SELECT count(...)` to compute `getTotalElements()`/`getTotalPages()`. On large or heavily-filtered tables this count can be as expensive as the data query itself, and it runs on *every* page request. - `Slice<T>` skips the count: it fetches `size + 1` rows and, if the extra row exists, sets `hasNext()`. Ideal for infinite scroll where you never show a total. - `List<T>` returns just the rows — no count, no next flag. Choosing `Slice` (or `List`) instead of `Page` when totals aren't needed removes a whole query per request. **Pitfall 4 — deep-offset cost.** `OFFSET 1_000_000 LIMIT 20` still forces the database to generate and discard a million rows before returning 20. Offset cost grows linearly with page depth, so the last pages of a big dataset are slow regardless of indexing. **Keyset (cursor / seek) pagination — the scale answer.** Instead of an offset, remember the sort values of the **last row seen** and seek past them: ```sql SELECT * FROM events WHERE (created_at, id) < (:lastCreatedAt, :lastId) ORDER BY created_at DESC, id DESC LIMIT :size ``` With an index on `(created_at, id)` the database *seeks* directly to the cursor and reads forward — deep pages cost the same as the first. It's also **stable against inserts/deletes before the cursor**, because the cursor is a value, not a positional offset. Trade-offs: you can only go next/prev (no random jump to "page 500"), and no cheap total count. In Spring Data, keyset support exists via `KeysetScrollPosition` and the `Window<T>`/`scroll(...)` API (Spring Data 3.1+), or you implement the WHERE-tuple manually. Offset scrolling is also available via `OffsetScrollPosition`. **Putting it together — guidance.** - Always give paginated queries a **deterministic, total sort** (include a unique tie-breaker). - Use `Slice`/`List` over `Page` unless the UI truly needs totals; count queries are not free. - For large tables or infinite scroll, prefer **keyset pagination** (Window/scroll or a manual seek) over deep offsets. - Enforce a **maximum page size** at the API boundary so a client can't request an unbounded page. - Be explicit that offset paging is eventually-consistent under concurrent writes; don't promise exact totals on volatile data. **Gotchas.** `ignoreCase()` or expression-based sorts can defeat the index that keyset seeks rely on. A `Sort` alone doesn't limit rows. Native `@Query` with `Page` needs a matching `countQuery`. And the zero-based page index still bites when mapping from UIs.

Sorting only by createdAt (non-unique), users report a row appearing on two pages. Root cause and fix?
The sort isn't a total order, so tied createdAt rows come back in arbitrary, shifting order between page queries. Append a unique tie-breaker (e.g. id) so every row has a deterministic position.
When would you switch from offset to keyset pagination?
When the dataset is large and users page deep, or under heavy concurrent writes. Keyset seeks an indexed (sortCol, id) tuple so deep pages stay fast and stable, at the cost of no random page jumps and no cheap total count.
Why can Page<T> hurt performance on a big filtered table?
It runs a separate count query on every request to compute totals; on large or complex-predicate tables that count can rival the data query's cost. Use Slice/List when totals aren't required.

saying these in an interview costs you the question

Claiming offset pagination is always consistent under concurrent writes
Thinking a non-unique sort column is enough for stable paging
Believing Page<T> and Slice<T> cost the same
Assuming deep OFFSET is as fast as the first page
Not knowing keyset/scroll pagination exists in Spring Data

Why can offset pagination skip or duplicate rows, and how do Page, Slice, sort stability, and keyset pagination address the trade-offs at scale?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics2 topics

answer

go deeper

context