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.
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.
Spring Data reads a page through a Pageable:
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.
Storm's page() takes a Pageable that sorts by metamodel fields:
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
-- 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.
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:
// 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()!!) }
-- 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.
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:
// 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.
| Spring Data JPA | Storm | |
|---|---|---|
| Offset pages | Page; the count is skipped on a partial page | page(), the same; page(pageable, totalCount) reuses a known total |
| Without a count | Slice | slice() |
| Keyset scrolling | ScrollPosition.keyset() with a Window, since 3.1 | scroll(Scrollable) with a Window |
| Sort fields | Property names in Sort, or TypedSort | Metamodel fields, of any table in the query |
| Client cursors | A map of keys, encoded by the application | nextCursor() and Scrollable.from(), an opaque string |
| Cost in the log | No equivalent | deep-offset, unordered-limit, and page-count when the count is slow |
The reference documentation covers the mechanics in depth: