Hibernate Pagination Limits with Collection Join Fetches

Answer

Maintain database‑level pagination by splitting the work into two queries: first fetch only the root entity identifiers with the desired setFirstResult()/setMaxResults(), then load the full entities and their collections using those IDs.

Confirmed facts

• When a JOIN FETCH is applied to a @OneToMany or @ManyToMany collection, Hibernate cannot push LIMIT/OFFSET (or dialect equivalent) down to the database because doing so could cut off rows belonging to the same root entity.
• This triggers the warning HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! and causes the entire matching result set to be materialized in the JVM before pagination is applied.
• In‑memory pagination can lead to OutOfMemoryError for large data sets and degrades performance linearly with the number of rows fetched.

Likely explanation

The Cartesian product produced by the join fetch multiplies rows for each collection element. Applying a limit after the join would arbitrarily discard some collection rows, breaking the object graph. Hibernate therefore chooses to fetch all rows and paginate in memory.

Steps to keep pagination at the DB level

1. Identify root IDs:
   

   List ids = session.createQuery(
       "select e.id from RootEntity e where …", Long.class)
       .setFirstResult(offset)
       .setMaxResults(pageSize)
       .list();

2. Load entities with collections using the ID list:
   

   List results = session.createQuery(
       "select distinct e from RootEntity e
        left join fetch e.collection
        where e.id in :ids", RootEntity.class)
       .setParameter("ids", ids)
       .setHint("org.hibernate.readOnly", false) // optional
       .list();

3. If the ID list can become large (e.g., deep paging), consider keyset pagination: store the last sort key from the previous page and use a WHERE clause like e.id > :lastId ordered by the same key.

Performance implications

• Deep offset pagination (high setFirstResult) still requires the database to scan and skip preceding rows, which grows linearly with the offset. Even with the two‑query approach, the first query suffers this cost.
• Keyset (seek) pagination avoids the scan by using a deterministic ordering column (often the primary key or a composite key) and a WHERE clause that starts after the last retrieved key. Its cost is roughly constant regardless of page number, making it far more efficient for large data sets.
• When the collection per root entity is large, the second query may still return many rows; consider DTO projections or batch fetching (@BatchSize) to limit the amount of data transferred.

Missing diagnostic detail

To decide whether the two‑query method or keyset pagination is preferable, we need to know the typical number of collection elements per root entity. If each root owns a large collection, alternative strategies (DTOs, batch fetch) may be required.