Pagination and keyset scrolling

Offset pages are fine until they are not: deep pages scan the table and every request pays for a count. Storm gives you classic pages when you want them and cursor-based scrolling when you need to scale.

01The task

Two flavors of the same problem: an admin table with numbered pages and a total count, and a feed with infinite scroll where nobody cares about page 7 of 4,812. They need different SQL strategies, and picking the wrong one shows up as database load.

02The JPA way

Spring Data's Pageable and Page handle the first case well:

val page = userRepository.findAll(
    PageRequest.of(0, 10, Sort.by("createdAt"))   // the sort column is a string
)

val users = page.content
val total = page.totalElements   // a COUNT(*) runs on every request

-- every page issues two queries
SELECT COUNT(*) FROM user u
SELECT u.* FROM user u ORDER BY u.created_at LIMIT 10 OFFSET ?

-- page 5,000: the database walks and discards 50,000 rows to reach it

Two costs hide in there. Page runs a count query on every request, whether the UI needs a fresh total or not. And OFFSET makes the database produce and discard every row before the page you asked for, so latency grows with page depth. The sort column is also a string; rename the property and the query fails at runtime. Spring Data added keyset scrolling in 3.1 with ScrollPosition, which addresses the depth problem; the comparison below is about how the two APIs handle the details.

03Classic pages, typed

When the UI genuinely needs page numbers and totals, Storm's page() works the way you expect, with the sort expressed against the metamodel instead of a string:

val pageable = Pageable.ofSize(10).sortBy(User_.createdAt)   // type-checked sort

val page = orm.entity<User>().select()
    .where(User_.active, EQUALS, true)
    .page(pageable)

page.content          // the rows
page.totalCount       // total matches
page.totalPages()     // computed
page.nextPageable()   // sort orders carry over

-- page(): a count and a data query, classic offset pagination
SELECT COUNT(*) FROM "user" u WHERE u.active = ?
SELECT u.id, u.email, u.name FROM "user" u
WHERE u.active = ? ORDER BY u.created_at LIMIT 10 OFFSET ?

Sort orders attach to the Pageable and carry over automatically when you navigate with nextPageable(), so page 2 cannot accidentally sort differently from page 1.

04Keyset scrolling

For feeds and load-more lists, scroll() replaces offsets with a cursor: it remembers the last key seen and asks the database for rows after it. The database seeks via the index instead of scanning:

val users = orm.entity<User>()

// First window: ordered by the key, no offset anywhere
val first = users.select().scroll(Scrollable.of(User_.id, 10))
render(first.content())

// Next window: seeks straight to the cursor position
first.next()?.let { cursor ->
    val second = users.select().scroll(cursor)
}

-- first window: one extra row decides hasNext, then is discarded
SELECT u.id, u.email, u.name FROM "user" u ORDER BY u.id LIMIT 11

-- next window: an index seek, not a scan
SELECT u.id, u.email, u.name FROM "user" u
WHERE u.id > ? ORDER BY u.id LIMIT 11
-- window 5,000 costs the same as window 1

The Scrollable key is a typed metamodel reference, and it must be unique so the sort is stable; sorting by a non-unique column takes an explicit sort overload with the key as tiebreaker. Two guardrails are built in: adding your own orderBy() to a scrolled query is rejected at runtime instead of silently corrupting page boundaries, and there is deliberately no total count, because counting a large filtered set on every request is exactly the cost scrolling exists to avoid.

05Cursors for REST APIs

Scroll state usually needs to cross a network boundary. Window serializes its position to an opaque cursor string, and a Scrollable reconstructs from it:

// Serialize the position into an opaque string for the client
val cursor: String? = window.nextCursor()

// The client sends it back; reconstruct the position and continue
val scrollable = Scrollable.fromCursor(User_.id, cursor)
val next = users.scroll(scrollable)

nextCursor() returns null when no further results existed at query time, so it drops straight into a JSON response without extra checks. See Cursor Serialization for supported key types and security notes.

06Side by side

07Keep going

The reference documentation covers the mechanics in depth: