Preventing In-Memory Pagination in Hibernate ORM

The Risk of In-Memory Slicing

When implementing pagination in Hibernate, developers often use setFirstResult() and setMaxResults() to limit the data returned from the database. However, combining these methods with JOIN FETCH on a collection (one-to-many or many-to-many relationships) creates a critical performance trap. Because a SQL join on a collection duplicates the parent row for every child element, Hibernate cannot accurately apply a LIMIT or OFFSET clause at the database level without risking truncated parent entities.

To resolve this, Hibernate defaults to in-memory pagination. It fetches every single matching record from the database into the JVM heap and performs the slicing in Java. For large datasets, this leads to OutOfMemoryError (OOME) and severe application latency.

Prerequisites

• Hibernate ORM 5.x or 6.x integrated into a Java project.
• A configured Dialect matching your database (e.g., PostgreSQLDialect) to ensure SQL translation.
• Logging enabled for org.hibernate.SQL to verify generated queries.

Implementing Safe Pagination

To avoid the HHH000104 warning and the associated memory overhead, you must separate the retrieval of the parent IDs from the retrieval of the associated collections.

Step 1: Fetch Paginated IDs

First, execute a query to retrieve only the primary keys of the entities you need for the current page. This query should not contain any collection fetches.

// Run this on the Application Server / Service Layer
// Required Permissions: Read access to the database schema
List<Long> ids = entityManager.createQuery(
    "SELECT p.id FROM Parent p WHERE p.status = :status", Long.class)
    .setParameter("status", Status.ACTIVE)
    .setFirstResult(0) // Offset
    .setMaxResults(20)  // Page size
    .getResultList();

Step 2: Fetch Full Entities with Collections

Use the list of IDs to fetch the full entities and their associated collections in a second query. Since the number of IDs is now limited to the page size, the JOIN FETCH is safe and efficient.

List<Parent> parents = entityManager.createQuery(
    "SELECT p FROM Parent p JOIN FETCH p.children WHERE p.id IN :ids", Parent.class)
    .setParameter("ids", ids)
    .getResultList();

Comparison: Direct Fetch vs. Two-Step Fetch

Approach	SQL Generation	Memory Impact	Risk
JOIN FETCH + setMaxResults	SELECT * FROM ... (No LIMIT)	High (Loads all rows)	OutOfMemoryError
Two-step ID fetch	SELECT id ... LIMIT 20	Low (Fixed page size)	Two database round-trips

Verification and Diagnostics

To ensure your pagination is happening at the database level and not in memory, perform the following checks:

1. Log Inspection: Check your console logs. If you see HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!, your query is unsafe.
2. SQL Verification: Verify that the generated SQL for the first query contains the dialect-specific limit clause (e.g., LIMIT 20 OFFSET 0 for PostgreSQL or FETCH FIRST 20 ROWS ONLY for Oracle).
3. Heap Monitoring: Use a tool like VisualVM or JConsole. If memory spikes significantly during a paginated request for a large table, in-memory slicing is likely occurring.

Limitations and Recovery

Offset Degradation: Be aware that setFirstResult() uses offset-based pagination. As the offset increases (e.g., page 10,000), the database must still scan all preceding rows, leading to slower response times. For extremely large datasets, consider keyset pagination (filtering by the last seen ID) instead of offsets.

Rollback: Since these changes involve modifying query logic rather than database schema, the "rollback" is simply reverting the Java code to the previous query implementation. No data migration is required.