OutOfMemoryError during pagination with join fetch in Hibernate ORM

Why the OutOfMemoryError happens

When a query uses setFirstResult() and setMaxResults() together with a join fetch on a collection (to‑many) association, Hibernate cannot push the LIMIT/OFFSET clauses to the database.

Reason: the join creates a Cartesian product that would duplicate parent rows for each child, making offset/limit semantics ambiguous. Hibernate therefore falls back to in‑memory pagination: it fetches the full result set into the JVM heap and applies the firstResult/maxResults there.

Confirmed facts

• This behavior is documented in Hibernate JIRA HHH‑8796 and persists across Hibernate 5.x and 6.x.
• Enabling SQL logging shows the generated SELECT lacks LIMIT/OFFSET when a collection join fetch is present.
• Heap profiling during execution shows a spike proportional to the total number of parent‑child rows, not just the page size.

Steps to avoid in‑memory pagination

1. Two‑query approach (recommended)
   1. First query: select only the parent identifier (or the whole parent entity) with pagination, without any join fetch.
   2. Second query: load the parents and their collections using WHERE parent.id IN (:ids) together with the desired join fetch. Because the ID list is already limited to the page, the second query returns exactly the needed rows.
2. Batch fetching
   1. Annotate the collection with @BatchSize(size = N) (or set hibernate.default_batch_fetch_size).
   2. Hibernate will first execute the paginated parent query (with LIMIT/OFFSET) and then issue a single additional SELECT to fetch the collections for the parents in that page.
3. Entity graph or @Fetch(FetchMode.SUBSELECT)
   1. Define an EntityGraph that includes the collection, or annotate the collection with @Fetch(FetchMode.SUBSELECT).
   2. Hibernate will issue a secondary SELECT to load the collections for the current result set, again avoiding the Cartesian product.

Verification

• Turn on SQL logging (hibernate.show_sql=true) and run the original query; confirm the lack of LIMIT/OFFSET.
• Run the two‑query version; verify both statements contain LIMIT/OFFSET and the second returns only the page’s parents with their collections.
• Monitor heap usage (VisualVM, JFR) while executing the original query on a dataset larger than the page size; you should see memory consumption drop to roughly the size of a page when using the alternatives.

Missing diagnostic detail

Are you using Hibernate 5.x or 6.x? (If you are on a version older than 5.2, the @BatchSize annotation behaves slightly differently; let me know if you need a version‑specific note.)