Deep-offset performance degradation on large tables - Hibernate

Hibernate ORM offers offset-based pagination via setFirstResult() and setMaxResults(), and seek-based pagination that filters on a monotonic sort key. The goal is to retrieve result pages from large datasets without incurring prohibitive I/O or latency as page depth increases.

Offset-based pagination skips N rows at the database level, producing I/O cost that grows linearly with page number. On tables with millions of rows, deep pages may trigger query timeouts, high CPU usage, and increased latency due to index or full table scans traversing skipped rows. Seek-based pagination avoids this cost but is fragile when the ordering column permits duplicates or lacks monotonic guarantees, potentially causing result repetition or gaps across page boundaries.

Unresolved questions remain about the practical performance threshold for offset pagination and the edge conditions that cause seek-based strategies to produce inconsistent results, particularly across Hibernate Dialect configurations and database systems.

• What is the maximum offset depth before query execution time exceeds acceptable limits on tables with tens of millions of rows?
• How can Hibernate Dialect settings be adjusted to favor seek-based pagination generation while maintaining portability across database systems?
• Under what circumstances does a non-unique, non-monotonic sort key cause result inconsistency in seek-based pagination, and how can these cases be reliably detected?