Hibernate Pagination: Transitioning from setFirstResult to ScrollableResults for Large Collections

Pagination Strategy for Large Datasets

Hibernate ORM provides setFirstResult() and setMaxResults() to handle result set pagination. While these methods generate dialect-specific SQL (such as LIMIT/OFFSET), they can trigger significant performance issues when combined with join fetch on OneToMany collections. In these scenarios, Hibernate may perform pagination in memory to avoid Cartesian product inconsistencies, potentially leading to OutOfMemoryError.

For applications processing massive datasets where deep pagination is required, the ScrollableResults interface offers a mechanism to stream results without loading the entire set into the persistence context.

Given the risk of memory-based pagination in Hibernate 6.x, what are the specific criteria for choosing ScrollableResults over standard offset-based pagination when collection joins are necessary? How does the persistence context management differ between these two approaches during large-scale data retrieval?