Does Hibernate perform in-memory pagination when using Join Fetch?

Question

Hibernate provides pagination through setFirstResult() and setMaxResults(), which typically delegate the limit and offset logic to the database dialect to ensure efficiency.

However, a specific architectural challenge arises when combining these pagination methods with JOIN FETCH clauses. Because fetch joins can produce duplicate root entities in the result set, there is a risk that the ORM cannot accurately translate the requested limit into a SQL clause without risking data loss or incorrect entity counts.

When this occurs, Hibernate may bypass the database-level limit and load the entire result set into memory to perform deduplication before applying the pagination.

Under what specific conditions does Hibernate trigger in-memory pagination instead of generating a dialect-specific LIMIT or OFFSET clause?
  How can this behavior be verified in the logs when using hibernate.show_sql?

Tasadduq Burney · Answer

Answer
When a JPQL/HQL query contains a JOIN FETCH clause and the query is paginated with setFirstResult() / setMaxResults(), Hibernate does **not** delegate the limit/offset to the database. Instead, it loads all rows that satisfy the join, materialises them into memory, removes duplicate root entities, and then applies the pagination in Java. This behaviour is the same for Hibernate 5.2 and newer; earlier versions behaved identically.

Specific Conditions Triggering In‑Memory Paging

The query contains at least one JOIN FETCH that expands the root entity’s associations.
  Pagination is requested on the root entity (via setFirstResult/setMaxResults).
  Hibernate cannot safely translate the pagination into a single SQL LIMIT/OFFSET because the join may produce duplicate root rows.
  Hibernate’s FetchJoinUtil detects that the join multiplicity > 1 and chooses the “in‑memory paging” strategy.

What Happens Under the Hood

Hibernate issues a single SELECT that fetches the root and all joined collections.
  The JDBC ResultSet is fully consumed, creating a list of root entities with their fetched associations.
  Hibernate removes duplicate root instances (but not duplicate rows in fetched collections).
  It then slices the list using the requested firstResult and maxResults values.

Verifying In‑Memory Paging via Logs
Enable SQL logging and statistics:
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.generate_statistics=true

Run the paginated query. In the log you should see:

A single SQL statement without a LIMIT or OFFSET clause.
  After the statement, a log line like EntityManager: fetched 1200 rows for root entity X (actual wording may vary).
  Finally, a line indicating the pagination applied in memory, e.g., Result size after paging: 20.

If you see a LIMIT/OFFSET in the SQL, the query is not using in‑memory paging.

Practical Recommendations

For large result sets, avoid JOIN FETCH on paginated queries. Use separate queries or @BatchSize / @Fetch(FetchMode.SUBSELECT) to load associations lazily after pagination.
  If you must fetch associations eagerly, keep maxResults small and monitor memory usage.
  Consider native SQL with explicit LIMIT/OFFSET and manual mapping if deterministic paging is required.

Missing Diagnostic Detail?
To refine this advice, knowing the Hibernate version you run (e.g., 5.6.x vs 6.0.x) can help, as newer releases may introduce subtle optimisations.

Does Hibernate perform in-memory pagination when using Join Fetch?

1 answer

Answer

Specific Conditions Triggering In‑Memory Paging

What Happens Under the Hood

Verifying In‑Memory Paging via Logs

Practical Recommendations

Missing Diagnostic Detail?

Your perspective belongs here.

1 question comment