skip to content

You're designing a public cursor-paginated API over a very large, frequently-changing table. Compare Page, Slice, and keyset Window scrolling and justify a choice, including their failure modes.

level: principalimportance: nice to knowfreq 25%

answer

  1. 3 axes: count, depth, write-stability
  2. OFFSET drifts -> dup/skip on writes
  3. keyset cursor = data value not position
  4. opaque token + matching composite index
  5. no totals, no arbitrary jump

basics

~20 s

For a large, changing table with a public cursor API, use keyset Window scrolling: it gives stable forward cursors and constant per-page cost. Page's count query and OFFSET are too costly and OFFSET drifts as rows change; Slice fixes the count but not OFFSET drift.

solid answer

~50 s

Three axes matter: count cost, deep-page cost, and stability under concurrent writes. Page pays a count query every request and uses OFFSET — both bad at scale, and OFFSET pagination 'drifts' when rows are inserted/deleted between requests, causing skipped or duplicated items. Slice drops the count (single query) but still uses OFFSET, so deep pages stay slow and drift persists. Keyset Window scrolling encodes the cursor as the last row's sort-key tuple, so per-page cost is constant (index seek) and it's far more robust to inserts/deletes because the resume point is a data value, not a positional offset. I'd expose the KeysetScrollPosition as an opaque cursor token, require a stable unique-tiebreaker sort (e.g. created_at, id), back it with a matching composite index, and accept the trade-off that clients can't jump to arbitrary pages or get an exact total.

code

java · 16 lines
java
// Public cursor API over a hot table: opaque token in, opaque token out
public CursorResponse<UserDto> list(String cursorToken, int size) {
    ScrollPosition position = (cursorToken == null)
            ? ScrollPosition.keyset()
            : CursorCodec.decode(cursorToken);   // base64 -> KeysetScrollPosition

    Window<User> window = repo.findByActiveTrueOrderByCreatedAtAscIdAsc(
            position, Limit.of(Math.min(size, MAX_PAGE_SIZE)));

    String next = window.hasNext()
            ? CursorCodec.encode(window.positionAt(window.getContent().size() - 1))
            : null;

    return new CursorResponse<>(window.map(UserDto::from).getContent(), next);
}
// Requires composite index on (created_at, id) matching the ORDER BY.

go deeper

for a junior

Not expected to reach this depth.

for a middle

Can compare count cost and pick Slice vs Page, but may miss offset drift.

for a senior

Should identify keyset for scale and name the unique-sort/index requirements.

for a principal

Should reason across cost, depth, and write-stability, treat OFFSET drift as a correctness bug, and design the cursor contract (opaque token, index, bounded size, no totals).

## The three candidates on three axes | Concern | Page | Slice | Keyset Window | |---|---|---|---| | Count query | Yes (every request) | No | No | | Deep-page cost | O(offset) via OFFSET | O(offset) via OFFSET | O(1) index seek | | Stability under writes | Poor (offset drift) | Poor (offset drift) | Strong (value cursor) | | Totals / page numbers | Yes | No | No | | Arbitrary page jump | Yes | No | No | ## Why OFFSET drifts (the correctness bug, not just perf) OFFSET pagination addresses rows *by position*. If, between fetching page 1 and page 2, someone inserts a row that sorts before your current position, every subsequent row shifts down by one — so the last item of page 1 reappears as the first item of page 2 (**duplicate**). A delete shifts the other way and **skips** a row. On a frequently-changing table this is a real data-integrity problem for consumers, independent of performance. Both `Page` and `Slice` inherit it because both use OFFSET. ## Why keyset is stable Keyset resumes with `WHERE (created_at, id) > (:last)`. The cursor is a **data value**, not an ordinal. Inserts/deletes elsewhere don't move your boundary: you still get 'the next rows after this exact key'. You may still observe newly-inserted rows that fall after your cursor (that's usually desirable) but you won't silently duplicate or skip already-seen rows. This makes keyset the right primitive for a public API where clients hold a cursor across time. ## Designing the public API - **Opaque cursor token.** Don't expose raw `(created_at, id)` — serialize the `KeysetScrollPosition` into a base64 opaque token so you can evolve the sort internally and prevent clients from crafting arbitrary keysets. - **Fixed, deterministic sort.** Pick one sort with a unique tail (`ORDER BY created_at, id`). Back it with a **composite index** matching that order, or the seek won't be index-only and you lose the perf win. - **Bounded page size** via `Limit`, with a server-enforced max. - **hasNext-based termination** (`Window.hasNext()`); don't promise totals. ## Failure modes to call out - **Non-unique sort tail** → duplicate/skipped boundary rows. Always end in the PK. - **Nullable sort column** → NULL ordering makes `>` ambiguous; either make it non-null or use an explicit NULLS ordering that your index supports. - **Changing sort mid-scroll** → invalidates the cursor; treat cursor+sort as a bound pair. - **OffsetScrollPosition misuse** → returning `ScrollPosition.offset()` looks like scrolling but reintroduces OFFSET cost and drift; only `ScrollPosition.keyset()` gets the benefits. - **Index missing/unusable** (e.g. mixed ASC/DESC without a matching index) → planner can't seek, falls back to scan+sort, killing the O(1) property. - **Need for totals** → if the product truly needs a count, serve an approximate/cached count separately rather than switching back to Page. ## Verdict For a large, mutating table behind a public cursor API: **keyset `Window` scrolling**. Reserve `Page` for internal admin screens over modest data that need page numbers and totals; use `Slice` when you want the single-query cheapness but don't need deep pages or write-stability.

  • Explain concretely how OFFSET pagination can return a duplicate row to a client.
    Client reads page 1 (offset 0, size 20). Before page 2, a row is inserted that sorts before the current window. Now every row shifts down one position, so the item that was row 20 becomes row 21 — and page 2 (offset 20) returns it again as its first element. Position-based addressing can't detect the shift; keyset, which resumes from the last row's key value, is immune.
  • Why serialize the ScrollPosition into an opaque token instead of exposing the raw keyset values?
    It hides the internal sort/columns so you can evolve them without breaking clients, prevents clients from injecting arbitrary or inconsistent keyset values (which could yield wrong or unbounded scans), and lets you version/validate the cursor server-side.

saying these in an interview costs you the question

  • Recommending Page for a huge, hot, publicly-cursored table
  • Treating OFFSET drift as only a performance issue, not a correctness one
  • Assuming keyset works without a matching composite index
  • Exposing raw keyset tuples as the public cursor
  • Believing Slice fixes deep-page cost

context

resolving links…