skip to content

What are Window<T> and ScrollPosition, and how does keyset scrolling avoid the cost of large-offset pagination?

level: seniorimportance: should knowfreq 45%

answer

  1. Window + ScrollPosition = Scroll API (3.1)
  2. keyset() vs offset() factory
  3. WHERE (sort,id) > (last...) not OFFSET
  4. constant cost via index seek
  5. needs unique tiebreaker in ORDER BY

basics

~20 s

Window<T> is a result chunk returned by the Spring Data Scroll API; ScrollPosition tells the query where to resume. With KeysetScrollPosition, the query resumes using WHERE last_sort_value conditions instead of OFFSET, so the database seeks via an index rather than scanning and skipping millions of rows.

solid answer

~40 s

The Scroll API (Spring Data 3.1+) has repository methods return Window<T> and take a ScrollPosition argument. ScrollPosition comes in two flavors: OffsetScrollPosition (still uses OFFSET) and KeysetScrollPosition (keyset/seek pagination). With keyset, instead of 'ORDER BY created, id LIMIT 20 OFFSET 100000', Spring Data generates 'WHERE (created, id) > (:lastCreated, :lastId) ORDER BY created, id LIMIT 20'. The database uses the index to seek directly to the resume point, so cost stays constant regardless of depth, whereas OFFSET forces it to scan and discard all skipped rows — cost grows linearly with offset. You start with ScrollPosition.keyset(), then pass window.positionAt(window.getContent().size()-1) — or the last element — to fetch the next window. Window also gives hasNext(). The requirement: a deterministic sort ending in a unique column (a tiebreaker) so the keyset condition is unambiguous.

code

java · 19 lines
java
public interface UserRepository extends JpaRepository<User, Long> {
    // ORDER BY must end in a unique column (id) for keyset to be unambiguous
    Window<User> findByActiveTrueOrderByCreatedAtAscIdAsc(ScrollPosition position,
                                                          Limit limit);
}

// Driving the scroll
ScrollPosition position = ScrollPosition.keyset();   // start
Window<User> window = repo.findByActiveTrueOrderByCreatedAtAscIdAsc(position, Limit.of(20));

process(window.getContent());

if (window.hasNext()) {
    // Resume AFTER the last element -> emits WHERE (created_at, id) > (?, ?)
    ScrollPosition next = window.positionAt(window.getContent().size() - 1);
    Window<User> page2 = repo.findByActiveTrueOrderByCreatedAtAscIdAsc(next, Limit.of(20));
}

// ScrollPosition.offset() would instead keep using SQL OFFSET (no deep-page win).

go deeper

for a junior

Likely unaware; can at most name that a cursor/seek approach exists.

for a middle

Should know OFFSET gets slow at depth and that keyset uses WHERE > last value.

for a senior

Should name Window/ScrollPosition, the keyset() vs offset() split, and the unique-sort requirement.

for a principal

Should reason about index design for the sort key, backward scrolling, nullable-column pitfalls, and API/cursor contract design.

## The problem with OFFSET Both `Page` and `Slice` paginate with SQL `LIMIT ... OFFSET n`. To return page 5000 (offset 100000, size 20), the database must still **generate and discard the first 100,000 rows** before returning 20. Cost grows linearly with the offset — deep pages get progressively slower. This is 'deep pagination' pain. ## The Scroll API Spring Data 3.1 (Spring Boot 3.1, 2023) added the **Scroll API**. A repository method returns `Window<T>` and accepts a `ScrollPosition`: ```java Window<User> findByActiveTrueOrderByCreatedAtAscIdAsc(ScrollPosition position, Limit limit); ``` `Window<T>` is like a `Slice`: it has `getContent()`, `hasNext()`, `isEmpty()`, and importantly `positionAt(index)` / `positionAt(entity)` which produce the `ScrollPosition` to resume from. ## ScrollPosition variants `ScrollPosition` is an interface with two implementations, chosen by the factory you call: - **`ScrollPosition.offset()`** → `OffsetScrollPosition`: still emits `OFFSET`. It's just an alternative cursor style; **no performance benefit** over Page/Slice for deep pages. - **`ScrollPosition.keyset()`** → `KeysetScrollPosition`: **keyset (a.k.a. seek) pagination**. This is the one that fixes deep-offset cost. ## How keyset works Instead of skipping N rows, keyset remembers the sort-key values of the **last row you saw** and asks for rows *after* that point: ```sql -- OFFSET style (slow at depth): ORDER BY created_at, id LIMIT 20 OFFSET 100000 -- Keyset style (constant cost): WHERE (created_at, id) > (:lastCreated, :lastId) ORDER BY created_at, id LIMIT 20 ``` With an index on `(created_at, id)`, the database **seeks** directly to the resume point and reads 20 rows — cost is independent of how deep you are. Spring Data builds the composite `WHERE` from the keyset captured in the `KeysetScrollPosition`. ## Driving it in code 1. First call: `ScrollPosition.keyset()` (start from the beginning). 2. Read `window.getContent()`, check `window.hasNext()`. 3. Get the next position: `window.positionAt(window.getContent().size() - 1)` (position after the last element). 4. Pass that back into the repository method for the next window. ## Hard requirements & gotchas - **Deterministic sort with a unique tiebreaker.** The final sort column must be unique (usually the primary key), or the keyset comparison is ambiguous and you get duplicates or skips. Always end `ORDER BY` with `id`. - **Sort must match the keyset.** The `ORDER BY` and the keyset columns have to align; you can't freely change sort between calls. - **Nullable sort columns are problematic** — NULL ordering makes the `>` comparison ill-defined; prefer non-null columns. - **No jump-to-arbitrary-page.** Keyset only goes forward/backward relative to a known row; you can't ask for 'page 500' directly. - **Backward scrolling** is supported (`ScrollPosition.Direction`), but the resume element/logic differs. - **Not a replacement for count** — like Slice, `Window` gives `hasNext()`, not totals. ## When to use Use keyset `Window` scrolling for large datasets with infinite scroll / cursor APIs where users page deep, and where a stable unique sort exists. Use `Page` when you need totals and random page access on modest data.

  • Why must the ORDER BY end in a unique column when using KeysetScrollPosition?
    The keyset WHERE condition compares the last seen sort-key tuple. If the sort key isn't unique, multiple rows share the same value at the boundary, so 'greater than' is ambiguous — you'd skip or duplicate the tied rows. Ending on the primary key makes each boundary tuple unique and the comparison exact.
  • Does ScrollPosition.offset() give the same deep-pagination performance benefit as keyset?
    No. OffsetScrollPosition still generates SQL OFFSET, so the database keeps scanning and discarding skipped rows — cost grows with depth. Only KeysetScrollPosition converts paging into an indexed seek with WHERE conditions for constant cost.

saying these in an interview costs you the question

  • Claiming Window/ScrollPosition always avoids OFFSET (offset() variant still uses it)
  • Saying keyset supports jumping to an arbitrary page number
  • Forgetting the unique-tiebreaker sort requirement
  • Thinking Window provides getTotalElements()

context

resolving links…