Compare KeysetScrollPosition and OffsetScrollPosition for GraphQL cursor paging, explain forward vs backward paging, and why the sort must be unique and stable.

seniorshould knowfreq 26%

answer

Offset = LIMIT/OFFSET: simple, unstable, slow deep
Keyset = WHERE (keys) > (:vals): stable, index seek
backward = flip comparator + reverse order, then re-order
sort must be unique -> append primary key
non-unique sort -> skip/duplicate at boundary

basics

~20 s

Offset positions seek by row count (LIMIT/OFFSET) — simple but shifts under writes and is slow deep in the list. Keyset positions seek by the last row's sort-key values — stable and fast, but the sort must be unique or rows get skipped or duplicated.

solid answer

~50 s

Spring Data's ScrollPosition has two forms. OffsetScrollPosition pages by counting rows (OFFSET n): easy and supports jump-to-page, but re-counts on every request, degrades at deep offsets, and shifts if rows are inserted/deleted before your position. KeysetScrollPosition pages by the sort-key values of the last-seen row, translating to WHERE (createdAt, id) > (:c, :i) ORDER BY createdAt, id LIMIT n — an index seek that's fast at any depth and stable under concurrent writes. Forward paging (first/after) seeks 'greater than' the cursor; backward (last/before) seeks 'less than' with reversed ordering, then re-orders results forward for the edges. Keyset requires a deterministic, unique sort: if two rows share the sort value and it isn't a unique tuple, the > comparison can't distinguish them, so you skip or duplicate items at page boundaries. Always append a unique tie-breaker like the primary key.

Spring Data's `ScrollPosition` has two forms. `OffsetScrollPosition` pages by counting rows (`OFFSET n`): easy and supports jump-to-page, but re-counts on every request, degrades at deep offsets, and shifts if rows are inserted/deleted before your position. `KeysetScrollPosition` pages by the sort-key values of the last-seen row, translating to `WHERE (createdAt, id) > (:c, :i) ORDER BY createdAt, id LIMIT n` — an index seek that's fast at any depth and stable under concurrent writes. Forward paging (`first`/`after`) seeks 'greater than' the cursor; backward (`last`/`before`) seeks 'less than' with reversed ordering, then re-orders results forward for the edges. Keyset requires a **deterministic, unique** sort: if two rows share the sort value and it isn't a unique tuple, the `>` comparison can't distinguish them, so you skip or duplicate items at page boundaries. Always append a unique tie-breaker like the primary key.

code

java · 16 lines

java

@QueryMapping
Window<Book> books(ScrollSubrange subrange) {
    // Unique, stable sort: business key + PK tie-breaker.
    Sort sort = Sort.by("createdAt", "id");
    ScrollPosition position = subrange.position()
            // Empty on first page: start from the keyset zero position.
            .orElse(ScrollPosition.keyset());
    int count = subrange.count().orElse(20);

    // For backward paging Spring Data flips the seek internally based on
    // the KeysetScrollPosition's direction carried in `position`.
    return repository.findBy(Specification.unrestricted(), q -> q
            .sortBy(subrange.forward() ? sort : sort.descending())
            .limit(count)
            .scroll(position));
}

go deeper

for a junior

Know offset vs keyset at a high level: page-number-ish vs remember-the-last-values.

for a middle

Explain the SQL each generates and that keyset needs a unique sort with a tie-breaker.

for a senior

Detail forward/backward query construction, the skip/duplicate failure mode, and the composite-index requirement.

for a principal

Weigh keyset vs offset as an API/perf trade-off (stability, deep paging, totalCount cost, index design) and pick per endpoint.

ScrollPosition, the input to scrolling. Spring Data org.springframework.data.domain.ScrollPosition tells a repository where to continue. Two implementations:

OffsetScrollPosition (ScrollPosition.offset(n)): 'skip n rows.' Maps to SQL LIMIT k OFFSET n. Pros: trivial, allows arbitrary jumps, works with any ordering. Cons: (1) the database still scans and discards the first n rows, so latency grows with depth; (2) it's unstable — inserting/deleting a row before your offset shifts everything, causing skipped or repeated items between requests.
KeysetScrollPosition (ScrollPosition.keyset() / ScrollPosition.of(keys, direction)): 'continue after the row whose sort keys were {createdAt: X, id: Y}.' Maps to a seek predicate: WHERE (createdAt, id) > (:X, :Y) ORDER BY createdAt, id LIMIT k. Pros: with a matching composite index it's an index range scan — O(log n) to locate, constant cost per page regardless of depth; and it's stable because it anchors on values, not a count. Cons: no jump-to-arbitrary-page, and it demands a well-formed ORDER BY.

Forward vs backward paging.

Forward (first: k, after: cursor): ScrollSubrange.forward() is true; the keyset uses the 'greater-than' comparator in the ORDER BY direction and returns the next k rows after the cursor.
Backward (last: k, before: cursor): forward() is false. For keyset, Spring Data flips the comparison to 'less-than' and reverses ORDER BY to grab the k rows before the cursor, then the result is re-ordered back to natural (forward) order so edges read correctly and pageInfo cursors are right. For offset, backward paging is emulated by subtracting the count from the offset, which is imprecise near the head of the list and can't be as exact as keyset — another reason to prefer keyset.

Why the sort must be unique and stable.

Deterministic/stable: the same query must always order rows the same way; otherwise the cursor's 'position' is meaningless between requests. Never rely on unspecified/default ordering.
Unique: the seek predicate (a, b) > (:a, :b) needs a total order. If you sort only by a non-unique column (say createdAt) and several rows share a timestamp, the comparator can't tell them apart at a page boundary — you'll either skip rows in that group or return duplicates on the next page. Fix by making the sort tuple unique, conventionally by appending the primary key: Sort.by("createdAt", "id"). The keyset then encodes both values.

Practical guidance.

Default to keyset for user-facing feeds/infinite scroll: stable + fast at depth.
Use offset only for small, stable admin tables needing jump-to-page-N.
Ensure a composite index matching the exact ORDER BY tuple, or keyset loses its performance advantage.
Avoid totalCount with keyset unless necessary — it forces a separate COUNT(*).
Don't mix first and last in one request (Relay discourages it); direction is ambiguous.

How this surfaces in Spring GraphQL. The after/before cursor decodes (via ScrollPositionCursorStrategy) into the appropriate ScrollPosition; ScrollSubrange carries direction; you pass the position+limit+sort to the repository, which returns a Window<T>; the WindowConnectionAdapter reads each item's ScrollPosition to emit cursors. Choosing keyset vs offset is therefore a repository/query decision, while the GraphQL layer just transports opaque cursors.

**ScrollPosition, the input to scrolling.** Spring Data `org.springframework.data.domain.ScrollPosition` tells a repository where to continue. Two implementations: - **OffsetScrollPosition** (`ScrollPosition.offset(n)`): 'skip n rows.' Maps to SQL `LIMIT k OFFSET n`. Pros: trivial, allows arbitrary jumps, works with any ordering. Cons: (1) the database still scans and discards the first n rows, so latency grows with depth; (2) it's *unstable* — inserting/deleting a row before your offset shifts everything, causing skipped or repeated items between requests. - **KeysetScrollPosition** (`ScrollPosition.keyset()` / `ScrollPosition.of(keys, direction)`): 'continue after the row whose sort keys were `{createdAt: X, id: Y}`.' Maps to a *seek* predicate: `WHERE (createdAt, id) > (:X, :Y) ORDER BY createdAt, id LIMIT k`. Pros: with a matching composite index it's an index range scan — O(log n) to locate, constant cost per page regardless of depth; and it's *stable* because it anchors on values, not a count. Cons: no jump-to-arbitrary-page, and it demands a well-formed ORDER BY. **Forward vs backward paging.** - *Forward* (`first: k, after: cursor`): `ScrollSubrange.forward()` is true; the keyset uses the 'greater-than' comparator in the ORDER BY direction and returns the next k rows after the cursor. - *Backward* (`last: k, before: cursor`): `forward()` is false. For keyset, Spring Data flips the comparison to 'less-than' and reverses ORDER BY to grab the k rows *before* the cursor, then the result is re-ordered back to natural (forward) order so `edges` read correctly and `pageInfo` cursors are right. For **offset**, backward paging is *emulated* by subtracting the count from the offset, which is imprecise near the head of the list and can't be as exact as keyset — another reason to prefer keyset. **Why the sort must be unique and stable.** - *Deterministic/stable*: the same query must always order rows the same way; otherwise the cursor's 'position' is meaningless between requests. Never rely on unspecified/default ordering. - *Unique*: the seek predicate `(a, b) > (:a, :b)` needs a total order. If you sort only by a non-unique column (say `createdAt`) and several rows share a timestamp, the comparator can't tell them apart at a page boundary — you'll either **skip** rows in that group or **return duplicates** on the next page. Fix by making the sort tuple unique, conventionally by appending the primary key: `Sort.by("createdAt", "id")`. The keyset then encodes both values. **Practical guidance.** - Default to **keyset** for user-facing feeds/infinite scroll: stable + fast at depth. - Use **offset** only for small, stable admin tables needing jump-to-page-N. - Ensure a **composite index** matching the exact ORDER BY tuple, or keyset loses its performance advantage. - Avoid `totalCount` with keyset unless necessary — it forces a separate `COUNT(*)`. - Don't mix `first` and `last` in one request (Relay discourages it); direction is ambiguous. **How this surfaces in Spring GraphQL.** The `after`/`before` cursor decodes (via `ScrollPositionCursorStrategy`) into the appropriate `ScrollPosition`; `ScrollSubrange` carries direction; you pass the position+limit+sort to the repository, which returns a `Window<T>`; the `WindowConnectionAdapter` reads each item's `ScrollPosition` to emit cursors. Choosing keyset vs offset is therefore a repository/query decision, while the GraphQL layer just transports opaque cursors.

A client reports missing rows exactly at page boundaries under keyset paging. What's the first thing you check?
Whether the sort tuple is unique. A non-unique ORDER BY (e.g. only createdAt) makes the `>` seek ambiguous for rows sharing that value; add a unique tie-breaker like the primary key so the keyset is a total order.
Why does offset paging get slower as clients page deeper, but keyset doesn't?
Offset must scan and discard OFFSET n rows every request, so cost grows with depth. Keyset seeks directly to the last cursor's key values via an index, so each page costs the same regardless of depth.
How is backward paging (last/before) handled for keyset?
The KeysetScrollPosition direction is set to backward; Spring Data reverses the comparison and ORDER BY to fetch the k rows before the cursor, then re-orders them to natural forward order for the edges and cursors.

saying these in an interview costs you the question

Sorting by a non-unique column and expecting correct keyset paging
Claiming keyset supports arbitrary jump-to-page-N
Thinking offset paging is stable under concurrent inserts/deletes
Believing backward keyset paging just reverses the client-side list without changing the query
Assuming keyset is fast without a matching composite index

Compare KeysetScrollPosition and OffsetScrollPosition for GraphQL cursor paging, explain forward vs backward paging, and why the sort must be unique and stable.

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions3 questions

▾related topics3 topics

answer

go deeper

context