Offset-based vs. Keyset pagination for deep-page Hibernate queries

When implementing pagination for large datasets in Hibernate ORM, there is a trade-off between ease of navigation and query performance as the result set depth increases.

Offset-based pagination using setFirstResult() and setMaxResults() allows users to jump to any arbitrary page. However, this approach requires the database to scan and discard all preceding rows, which can lead to linear performance degradation.

Keyset pagination (the seek method) maintains constant performance by filtering results based on the last seen unique identifier. While this avoids the overhead of scanning skipped rows, it prevents direct access to specific page numbers without traversing the previous keys.

• Constraint: High-volume datasets where deep-page access is required.
• Requirement: Predictable database execution plans regardless of page depth.

Which strategy is more sustainable for datasets where the underlying data changes frequently during user sessions? Under what specific conditions does the lack of arbitrary page jumping in keyset pagination outweigh the performance costs of offset-based scanning?