Deep-offset performance degradation on large tables - Hibernate

Question

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?

Tasadduq Burney · Answer

Answer to the three questions
Maximum offset depth. There is no fixed universal maximum. Confirmed fact: offset pagination via setFirstResult()/setMaxResults() forces the database to skip N rows, so I/O and CPU cost grows with page depth. Likely practical threshold on tables with tens of millions of rows is in the tens of thousands to a few hundred thousand rows offset before latency becomes unacceptable, with timeouts and high CPU appearing at deeper depths. The exact point depends on database engine, indexing, hardware and acceptable SLA.
Dialect settings for seek-based pagination. Confirmed fact: Hibernate ORM does not automatically rewrite setFirstResult() into keyset/seek pagination. No portable Dialect property makes Hibernate generate WHERE key > :lastKey ORDER BY key from offset API. Likely explanation: seek pagination must be authored in the application query. Dialect tuning can affect how LIMIT/OFFSET is rendered, not how pagination strategy is chosen. Custom getLimitString overrides are database-specific and reduce portability.
Non-unique / non-monotonic sort key inconsistency. Confirmed fact: seek pagination requires a deterministic, monotonic ordering. With a non-unique sort key, or a sort key that can decrease, rows can be repeated or skipped across pages. Likely explanation: duplicates cause the same key value to appear on multiple pages, and inserts/deletes between pages create gaps. Reliable detection requires an ORDER BY that is unique, typically sort column plus a unique tie-breaker such as the primary key.
What to do for this case

Avoid deep offset. Replace setFirstResult(offset).setMaxResults(size) with keyset pagination for deep pages:
// HQL example
FROM Entity e WHERE e.sortKey > :lastSeenSortKey ORDER BY e.sortKey ASC, e.id ASC

Ensure the ordering is deterministic. If the sort column is not unique, add a unique tie-breaker to ORDER BY and to the WHERE clause.
Do not rely on Dialect flags to generate seek queries. Keep pagination logic in the repository layer so it remains portable across databases.
Verify behavior on a representative dataset:

Run offset and seek queries with increasing page depth and measure execution time, CPU and I/O.
Use EXPLAIN ANALYZE to confirm index seek vs skip.
Introduce duplicate sort values and compare page counts for overlaps/gaps.

One missing diagnostic detail that changes the recommendation: which database and dialect version you are using and whether the chosen sort column is indexed and nullable. Index presence and nullability determine whether keyset pagination can use an index seek and whether nulls break monotonic ordering.
Assumptions and uncertainty
This guidance assumes Hibernate ORM 6.x behavior where setFirstResult maps to SQL OFFSET. Seek pagination is application-authored. Performance thresholds are environment dependent and require current verification on your data and hardware. Claims about typical depth limits are illustrative, not guaranteed.

Deep-offset performance degradation on large tables - Hibernate

1 answer

Answer to the three questions

What to do for this case

Assumptions and uncertainty

Your perspective belongs here.

0 question comments