Hibernate Pagination: Transitioning from setFirstResult to ScrollableResults for Large Collections

Decision criteria for ScrollableResults vs. offset pagination

Choose ScrollableResults when you must stream very large, read-only result sets—especially with deep offsets—and the query includes join fetch on a OneToMany collection. In Hibernate 6.x, offset pagination with setFirstResult()/setMaxResults() normally maps to SQL LIMIT/OFFSET, but a collection fetch join can prevent the database from applying the limit safely because of Cartesian product duplication. Hibernate may then paginate in memory, which increases heap use and can trigger OutOfMemoryError. ScrollableResults avoids materializing the full page by holding a JDBC cursor and fetching rows incrementally.

Prefer offset pagination when you need a bounded, random-access page of managed entities, when the data is mutable and must remain in the persistence context for automatic dirty checking and flush, or when the offset is small enough that database-side paging is efficient.

Persistence context behavior

With offset pagination, every entity on the requested page is loaded into the persistence context and stays managed for the life of the session unless you explicitly evict it. For large pages this keeps many managed instances in memory.

ScrollableResults lets you decouple retrieval from retention. You can mark the query read-only and call session.clear() or evict() periodically while iterating, keeping the persistence context small. The trade-off is that evicted entities lose automatic dirty checking; if you need to update them later, offset pagination is safer.

Practical steps for a read-only streaming migration

1. Confirm the query is read-only and does not require post-retrieval updates.
2. Replace setFirstResult()/setMaxResults() with a ScrollableResults loop.
3. Apply a read-only hint and clear the session every N rows.
4. Deduplicate root entities if the collection join produces duplicates.

ScrollableResults scroll = session.createQuery(hql, Root.class)
    .setReadOnly(true)
    .scroll();
int count = 0;
while (scroll.next()) {
    Root r = (Root) scroll.get();
    // process r
    if (++count % 100 == 0) {
        session.clear();
    }
}
scroll.close();

Verification before committing

Because pagination behavior with fetch joins is dialect- and patch-version sensitive, verify locally:

• Enable SQL logging and confirm whether LIMIT/OFFSET is actually pushed to the database for your current offset query.
• Run a representative data volume with heap monitoring to compare peak memory and GC pressure between the two approaches.
• Check the Hibernate 6.x release notes for your exact patch version regarding in-memory pagination warnings.

One diagnostic question

Do the entities need to remain managed for dirty checking and flush after retrieval? If yes, offset pagination with smaller pages is the safer default; if no, ScrollableResults with periodic clearing is the better fit for large collection joins.