Hibernate Pagination: Transitioning from Offset-based to Keyset Seek for Deep Page Performance

Performance Degradation in Deep Pagination

When utilizing setFirstResult() and setMaxResults() in Hibernate ORM, the generated SQL typically relies on LIMIT and OFFSET clauses. While efficient for initial pages, this approach causes linear performance degradation as the offset increases because the database must scan and discard all preceding rows before returning the target window.

Memory and Consistency Constraints

There is a known risk when combining pagination with join fetches, where Hibernate may perform pagination in memory rather than at the database level, potentially leading to OutOfMemoryError. Additionally, offset-based pagination is susceptible to result drifting if records are inserted or deleted between requests.

To maintain constant-time performance for large datasets, a transition to keyset pagination (the seek method) is required, filtering by the last seen unique identifier instead of a numeric offset.

• How can keyset pagination be implemented within the Hibernate Criteria API to avoid the performance penalties of setFirstResult()?
• What is the recommended strategy to prevent in-memory pagination when using join fetches in a keyset-based approach?