What are the performance implications of using offset-based pagination with large result sets in Hibernate when combined with join fetch?

Question

When retrieving large datasets with Hibernate, developers often rely on setFirstResult() and setMaxResults() for offset‑based pagination. This approach translates to LIMIT/OFFSET in most dialects, but the database must scan and discard preceding rows, which can degrade performance as the offset grows. Combining offset pagination with eager join fetches adds another layer of complexity because Hibernate may warn about performing pagination in memory, potentially leading to OutOfMemoryErrors.

Given these trade‑offs, an unresolved question remains about the practical impact of using offset‑based pagination alongside join fetch on both query execution time and memory consumption in a production‑scale environment.

Does the performance penalty become unacceptable beyond a certain offset threshold, and can keyset pagination reliably replace offset‑based queries when join fetch is required?

Siddharth · Answer

Direct answer
Using offset‑based pagination (setFirstResult/setMaxResults) together with eager join fetch in Hibernate degrades both query time and memory usage as the offset grows, and can trigger an in‑memory pagination warning (HHH000104) that may lead to OutOfMemoryError.
Confirmed facts

OFFSET forces the database to scan and discard all preceding rows, so execution time grows roughly linearly with the offset value.
When a join fetch is present, Hibernate may apply the limit/max results in memory after loading the full Cartesian product, producing duplicate root rows for each associated row.
This in‑memory step can cause the JVM heap to retain the entire result set before slicing, raising the risk of OutOfMemoryError for large offsets or high association cardinality.

Likely explanation
The combination creates two sources of work: the database must still read through the offset rows, and Hibernate must materialise the joined rows to eliminate duplicates before applying the page slice. As the offset increases, both the DB scan and the in‑memory buffer grow, so response time and heap usage rise sharply.
Steps needed for this case

Replace OFFSET pagination with keyset (seek) pagination using a WHERE clause on the last seen unique key and maintain the same ORDER BY.
Ensure the ORDER BY column(s) are indexed and, if using join fetch, add DISTINCT (or use @BatchSize/subselect fetch) to avoid duplicate root rows.
If keyset pagination is not feasible, limit the page size and consider disabling the join fetch for the paginated query, loading associations lazily or via secondary queries.
Monitor Hibernate statistics and SQL logs; verify that the warning HHH000104 disappears after the change.

What are the performance implications of using offset-based pagination with large result sets in Hibernate when combined with join fetch?

1 answer

Direct answer

Confirmed facts

Likely explanation

Steps needed for this case

Your perspective belongs here.

0 question comments