Hibernate Pagination: Offset-based vs. Keyset-based for Large Datasets

When implementing pagination for tables containing millions of records in Hibernate, there is a trade-off between API simplicity and database performance. The standard approach utilizes setFirstResult() and setMaxResults(), which translates to SQL OFFSET and LIMIT clauses.

While offset-based pagination allows users to jump to any arbitrary page, it often results in performance degradation as the offset increases, because the database must scan and discard all preceding rows before returning the requested set. In contrast, keyset-based pagination (the seek method) filters results based on the last seen unique identifier, ensuring constant-time lookup regardless of the depth of the dataset.

However, keyset pagination restricts navigation to sequential movements (next/previous) and requires a strictly ordered, unique column to maintain consistency.

• Under what specific dataset size or latency threshold does the performance cost of setFirstResult() outweigh the UX benefit of arbitrary page jumping?
• Is there a documented hybrid strategy in Hibernate to support both random access and high-performance deep paging?