Solving HHH000104: Preventing In-Memory Pagination in Hibernate

The Problem: The Silent Memory Leak

When you apply pagination to a Hibernate query that uses JOIN FETCH on a collection (one-to-many or many-to-many), Hibernate may be unable to translate the pagination logic into a SQL LIMIT or OFFSET clause. Instead, it executes the query without limits, pulls the entire result set into the Java Virtual Machine (JVM), and performs the pagination in memory.

The primary risk is an OutOfMemoryError (OOM). While it may work in development with ten records, it will crash in production with ten thousand.

Diagnostic Indicators

If you suspect in-memory pagination, check for these specific markers:

Indicator	Observation	Meaning
Log Warning	HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!	Hibernate has explicitly bypassed the database for pagination.
SQL Logs	Generated SQL lacks LIMIT, OFFSET, or FETCH NEXT clauses.	The database is returning all matching rows.
Heap Usage	Spike in memory usage during the execution of a specific paginated request.	The JVM is buffering the full result set before slicing it.

Step-by-Step Resolution Path

1. Verify the SQL Execution

Enable SQL logging in your application.properties or persistence.xml to see what is actually being sent to the database:

hibernate.show_sql=true
hibernate.format_sql=true

Run the paginated request. If the resulting SQL query does not contain pagination keywords specific to your dialect (e.g., LIMIT for PostgreSQL/MySQL), you are experiencing in-memory pagination.

2. Identify the Fetch Conflict

Look for JOIN FETCH in your JPQL or HQL. For example, the following query triggers the issue:

SELECT d FROM Department d JOIN FETCH d.employees WHERE d.region = :region

Because one Department can have many Employees, the result set contains duplicate Department rows to accommodate the joined Employees. Hibernate cannot accurately calculate the firstResult (offset) based on these duplicated rows at the database level, so it fetches everything.

3. Implement the Two-Step Fetch Pattern

The most reliable fix is to separate the identification of the root entities from the fetching of their collections.

Step A: Fetch the IDs
Run a paginated query to retrieve only the primary keys of the root entities. This query does not use JOIN FETCH on collections, allowing the database to handle the limit.

SELECT d.id FROM Department d WHERE d.region = :region

Step B: Fetch the Data
Use the retrieved IDs to fetch the full entities and their collections in a second query using an IN clause.

SELECT d FROM Department d JOIN FETCH d.employees WHERE d.id IN (:ids)

Comparison of Pagination Strategies

Strategy	DB Load	JVM Load	Correctness
Join Fetch + Pagination	Low (Full Scan)	Very High	Correct (but slow/risky)
Two-Step ID Fetch	Medium (2 Queries)	Low	Correct
Lazy Loading	High (N+1)	Low	Correct (but inefficient)

Limitations and Verification

Deep Pagination: Even with the Two-Step pattern, using very high offset values (e.g., setFirstResult(100000)) can degrade database performance because the DB must still scan through the preceding rows.

Verification: To verify the fix, repeat the SQL log check. You should now see a LIMIT/OFFSET clause in the first query and a WHERE id IN (...) clause in the second query. The HHH000104 warning should no longer appear in the logs.

Escalation Criteria

If the Two-Step pattern does not resolve performance issues, escalate to the following architectural changes:

• Keyset Pagination: Instead of offsets, use the last seen ID (e.g., WHERE id > :lastId) to avoid scanning large offsets.
• DTO Projection: If you only need a few fields from the collection, use a DTO projection instead of fetching full entities.
• Database View: Create a materialized view if the join logic is too complex for efficient HQL pagination.