Tutorials/Pagination and scrolling

Pagination and keyset scrolling

An offset page costs more the deeper it is, and a total takes a count query. Storm reads pages and slices by offset, scrolls by keyset with cursors a client can send back, and tells you in the log when a count is slow or an offset is deep.

Series · JPA to Storm6 min readKotlin

01The task

Two screens read the same table of owners. The front desk shows numbered pages and a total. An API serves the list to a client that loads the next part as the list scrolls. The first reads by offset; the second should continue from the last row it returned.

02The JPA way

Spring Data reads a page through a Pageable:

OwnerService.kt Kotlin · JPA
1
2
3
4
5
6
val page = ownerRepository.findAll(
    PageRequest.of(0, 10, Sort.by("lastName"))
)

val owners = page.content
val total = page.totalElements

Spring Data runs the count only when the page is full, and a Slice result skips it. The sort names the property as a string, or through TypedSort. Since 3.1, a repository method that takes a ScrollPosition and returns a Window scrolls by keyset. The keyset position holds its keys as a map of property names to values, and encoding it for a client is left to the application.

03Pages and slices

Storm's page() takes a Pageable that sorts by metamodel fields:

OwnerService.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
11
val owners = orm.entity(Owner::class)
val pageable = Pageable.ofSize(10).sortBy(Owner_.lastName)

val page = owners.select()
    .where(Owner_.address.city eq city)
    .page(pageable)

page.content        // the rows
page.totalCount     // the matching rows
page.totalPages()
page.next()         // the Pageable for page 2, same sort
↳ generated sql
-- the page
SELECT o.id, o.first_name, o.last_name, o.street, c.id, c.name, c.population, o.telephone
FROM owner o
LEFT JOIN city c ON o.city_id = c.id
WHERE o.city_id = ?
ORDER BY o.last_name
LIMIT 10 OFFSET 0

-- the total, counted only when the page is full
SELECT COUNT(*)
FROM owner o
LEFT JOIN city c ON o.city_id = c.id
WHERE o.city_id = ?

The sort orders travel with the Pageable, so next() and previous() keep them. A page that is not full is its own total, so the count runs only for a full page. page(pageable, totalCount) reuses a total the application already has, and slice(pageable) reads one row beyond the page to report hasNext() and runs no count.

Storm also reports what an offset costs. In development, a page read with an offset of a thousand rows or more gets deep-offset advice in the log, and a limit or offset without an ORDER BY gets unordered-limit, in production too, since the pages can repeat or skip rows. When the count behind a page is slow, the slow statement log adds page-count advice, which names slice as the read without it.

04Keyset scrolling

A Scrollable states the ordering and the window size. scroll() returns a Window with the rows and the requests that continue after and before it:

OwnerService.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
// The ordering: last name first, the primary key breaks ties
val request = Scrollable.of(Owner_.id, 20).sortBy(Owner_.lastName)

val first = owners.scroll(request)
render(first.content)

if (first.hasNext()) {
    // next() is the request positioned after the last row of the window
    val second = owners.scroll(first.next()!!)
}
↳ generated sql
-- first window: one row beyond the size sets hasNext
SELECT o.id, o.first_name, o.last_name, o.street, c.id, c.name, c.population, o.telephone
FROM owner o
LEFT JOIN city c ON o.city_id = c.id
ORDER BY o.last_name, o.id
LIMIT 21

-- next window: continues after the last name and id of the last row
SELECT o.id, o.first_name, o.last_name, o.street, c.id, c.name, c.population, o.telephone
FROM owner o
LEFT JOIN city c ON o.city_id = c.id
WHERE (o.last_name > ? OR (o.last_name = ? AND o.id > ?))
ORDER BY o.last_name, o.id
LIMIT 21

The next window compares the sort fields and the key with the values of the last row read, so an index on (last_name, id) serves every window at the same cost. The key must be unique and not null, typically the primary key. Sort fields go before it in any number, each in its own direction, and may belong to any table the query joins. A nullable sort field is refused, since rows with a NULL would fall out of every window.

The Scrollable owns the ORDER BY, so a scrolled query with its own orderBy() fails with a PersistenceException. orderFirst, which puts the rows matching a condition first, works with scroll. There is no total count; if a screen needs one, resultCount on the query builder runs it separately.

05Cursors for REST APIs

A Window encodes its positions as opaque, URL-safe strings with nextCursor() and previousCursor(). Scrollable.from(cursor) puts a request at the position a client sends back:

OwnerController.kt Kotlin · Storm
1
2
3
4
5
6
// The ordering lives in code; the position comes from the client
val request = Scrollable.of(Owner_.id, size).sortBy(Owner_.lastName)
val window = owners.scroll(if (cursor != null) request.from(cursor) else request)

// nextCursor() is null when no rows followed the window
return OwnerList(window.content, window.nextCursor())

The cursor carries the position only. The ordering and the size stay in code, so a client can ask for a different size on the next request. A cursor that does not match the request's ordering throws InvalidCursorException, which a web layer maps to a fresh first window. The format is not signed; see Cursor Serialization for the supported key types and how to protect cursors from tampering.

06Side by side

Spring Data JPAStorm
Offset pagesPage; the count is skipped on a partial pagepage(), the same; page(pageable, totalCount) reuses a known total
Without a countSliceslice()
Keyset scrollingScrollPosition.keyset() with a Window, since 3.1scroll(Scrollable) with a Window
Sort fieldsProperty names in Sort, or TypedSortMetamodel fields, of any table in the query
Client cursorsA map of keys, encoded by the applicationnextCursor() and Scrollable.from(), an opaque string
Cost in the logNo equivalentdeep-offset, unordered-limit, and page-count when the count is slow

07Keep going

The reference documentation covers the mechanics in depth: