skip to content

Under the hood, what SQL/fetch behavior distinguishes a Slice query from a Page query, and what subtle bug can the limit+1 trick expose?

level: middleimportance: should knowfreq 35%

answer

  1. Slice: LIMIT pageSize+1, trim extra
  2. Page: data + SELECT COUNT
  3. full page != no more data
  4. unstable sort -> boundary dup/skip
  5. always tiebreak on unique id

basics

~20 s

A Slice fetches pageSize + 1 rows in a single query; if the extra row exists, hasNext() is true and that row is trimmed off. A Page runs the same data fetch plus a separate SELECT COUNT to compute totals.

solid answer

~40 s

For a Slice, Spring Data issues one query with LIMIT pageSize + 1. If it receives that extra row, it knows a next slice exists, sets hasNext()=true, and drops the extra element so getContent() still returns pageSize rows. No count query runs. For a Page, it runs the paged data query and then a second SELECT COUNT(...) with the same filters to populate getTotalElements()/getTotalPages(). A subtle consequence of the limit+1 trick: with a non-deterministic sort (no unique tiebreaker), the boundary row that determines hasNext() and the first row of the next slice can be inconsistent across requests, producing duplicates or gaps as you scroll. So always sort on a stable unique key. Also, hasNext() reflects only whether a further row existed at query time — under concurrent writes it can be momentarily stale.

code

java · 12 lines
java
// GOOD: deterministic sort with a unique tiebreaker keeps slice boundaries stable
Slice<Order> slice = repo.findByStatus(
        "OPEN",
        PageRequest.of(0, 20, Sort.by("createdAt").and(Sort.by("id"))));
// Under the hood: ... ORDER BY created_at, id LIMIT 21 OFFSET 0
// 21st row (if present) => hasNext()=true, then trimmed to 20.

// RISKY: created_at is not unique -> tied rows may reorder between slices,
// causing duplicates/gaps at the boundary.
Slice<Order> risky = repo.findByStatus(
        "OPEN",
        PageRequest.of(0, 20, Sort.by("createdAt")));

go deeper

for a junior

Can state Slice fetches one extra row to know if there's more.

for a middle

Should explain LIMIT pageSize+1 vs the separate COUNT and that a full page isn't 'last page'.

for a senior

Should connect unstable sort to boundary duplicates/gaps and prescribe a unique tiebreaker.

for a principal

Should discuss point-in-time hasNext() under concurrency and how keyset scrolling changes the guarantees.

## Slice: one query, limit+1 When a repository method returns `Slice<T>` (or `Page<T>`, since Page extends Slice), Spring Data needs to answer `hasNext()`. It does this without a count by requesting **one more row than the page size**: the generated SQL is effectively `... ORDER BY <sort> LIMIT (pageSize + 1) OFFSET (pageNumber * pageSize)`. - If `pageSize + 1` rows come back → there's at least one more row → `hasNext() = true`. Spring Data **removes the extra row** so `getContent()` returns exactly `pageSize` items. - If `pageSize` or fewer rows come back → `hasNext() = false`. This is why a full page doesn't imply more data — only `hasNext()` is authoritative. ## Page: data query + count query `Page<T>` does everything a `Slice` does (including limit+1 semantics for hasNext where applicable) **and** runs a separate `SELECT COUNT(...)` using the same `WHERE`/joins but no ordering or paging, to compute `getTotalElements()` and derive `getTotalPages()`. That's the extra query you pay for. ## The subtle bug: unstable sort + limit+1 The limit+1 mechanism assumes the ordering is **total and stable**. If your `Sort` is on a non-unique column (say `ORDER BY created_at` where many rows share the same timestamp), the database is free to return tied rows in **any order**, and that order can differ between the two requests that fetch consecutive slices. The 'extra' row that decided `hasNext()` on slice N may not be the same row that appears first on slice N+1 — so a scrolling client can see **duplicate or missing rows** at slice boundaries. The fix is a deterministic sort ending in a unique tiebreaker, e.g. `Sort.by("createdAt").and(Sort.by("id"))`. ## Concurrency nuance `hasNext()` is evaluated from the data present when the query ran. Under concurrent inserts/deletes it's a point-in-time answer and can be momentarily stale — e.g. it says true but the next fetch returns nothing because a row was deleted. This is inherent to OFFSET-based chunking; keyset scrolling mitigates it but doesn't fully eliminate visibility of concurrent changes. ## Practical takeaways - Expect **one** query for Slice, **two** for Page. - Always include a **unique tiebreaker** in the sort for any paginated/sliced query. - Don't infer 'last page' from a full content list; use `hasNext()`. - The limit+1 row is invisible to you — you always get at most `pageSize` items back.

  • If getContent() returns exactly pageSize rows, is there always a next slice?
    No. A full page is inconclusive — the limit+1 row may or may not have existed. Only hasNext() tells you: it's true iff the (pageSize+1)th row came back. Never infer 'more data' from a full content list.
  • How does the limit+1 trick interact with a non-unique sort to cause duplicates?
    With ties on the sort column, the DB may order tied rows differently across the two consecutive slice queries. The boundary row that set hasNext() on slice N might resurface as slice N+1's first row (duplicate) or a different tied row gets skipped (gap). A unique tiebreaker makes the ordering total and the boundary deterministic.

saying these in an interview costs you the question

  • Saying Slice fetches exactly pageSize rows (it fetches pageSize+1)
  • Inferring last page from a full content list instead of hasNext()
  • Ignoring the need for a unique tiebreaker in the sort
  • Claiming the extra row is visible in getContent()

context

resolving links…