Which pagination strategy prevents in-memory filtering when using JOIN FETCH in Hibernate?

Question

When implementing pagination in a Java application using Hibernate ORM, the standard approach involves utilizing setFirstResult() and setMaxResults(). While this typically translates to efficient LIMIT or OFFSET clauses in the database dialect, the behavior changes when JOIN FETCH is used to retrieve associated entities in a single query.

The primary constraint is that Hibernate may trigger in-memory pagination if it cannot safely translate the join and the limit into a single SQL statement without duplicating root entities. This results in the application loading the entire result set into the JVM heap before filtering, which risks OutOfMemoryError for large datasets.

What is the recommended architectural pattern to maintain database-level pagination while still avoiding the N+1 select problem? Does the use of ScrollableResults resolve the in-memory filtering issue, or is a two-step query approach required?

Zoha · Answer

Answer
The pagination strategy that avoids in‑memory filtering when you need JOIN FETCH is a two‑step query:

Run a query that selects only the root entity’s identifier (or primary key) with setFirstResult and setMaxResults. This query contains no joins that cause duplicate rows, so the database can apply LIMIT/OFFSET safely.
Collect the returned identifiers and execute a second query that uses JOIN FETCH for the associations and adds a WHERE id IN (:ids) clause (or a temporary table) to load the full graph for exactly those roots.

This keeps the LIMIT/OFFSET on the identifier query, so the database does the paging and the second query returns only the needed rows without any in‑memory trimming.
Why the simple setFirstResult/setMaxResults on a fetch join fails
When a JOIN FETCH creates duplicate root rows (e.g., one parent with multiple children), a SQL LIMIT limits rows, not distinct parents. Hibernate therefore cannot guarantee that the limit corresponds to the requested number of distinct entities and may either refuse to apply the limit or pull the whole result set into the JVM and trim it in memory.
Steps to implement (verified)

Enable SQL logging to see the generated statements:
# In application.properties (Spring Boot) or persistence.xml
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Run the identifier query and confirm it contains LIMIT/OFFSET and returns only IDs.
Run the fetch query and verify it has no LIMIT/OFFSET and includes an IN clause with the collected IDs.
Compare the number of SQL rows returned by the fetch query with the number of distinct entities materialized; they should match, indicating no in‑memory filtering.

When the recommendation might change
If you are using a Hibernate version older than 5.0 or a database dialect that lacks efficient IN handling for large lists, the two‑step approach could produce an oversized IN clause and degrade performance. In that case, consider using a temporary table or a scrollable cursor with a distinct root estimator.
To give a definitive recommendation, please confirm:

Which Hibernate version (e.g., 5.6, 6.2) and which database dialect (e.g., PostgreSQL, MySQL, Oracle) you are targeting?

Which pagination strategy prevents in-memory filtering when using JOIN FETCH in Hibernate?

1 answer

Answer

Why the simple `setFirstResult/setMaxResults` on a fetch join fails

Steps to implement (verified)

When the recommendation might change

Your perspective belongs here.

0 question comments