Preventing In-Memory Pagination in Hibernate for Large Datasets

The Performance Trap: HHH000104

When paginating through large datasets in Hibernate, developers often combine setFirstResult() and setMaxResults() with JOIN FETCH to avoid the N+1 select problem. However, this combination often triggers the warning HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

The critical takeaway is that when Hibernate cannot translate a collection join into a database-level pagination clause (like LIMIT/OFFSET), it fetches every single matching row from the database into the application memory and performs the pagination in Java. For tables with millions of rows, this leads to immediate OutOfMemoryError crashes or severe application latency.

Comparing Pagination Strategies

Choosing the right strategy depends on whether your dataset is static and how deep your users need to paginate.

Strategy	Mechanism	Pros	Cons
Offset-based	setFirstResult(n)	Easy to implement; supports random page jumps.	Performance degrades as offset increases; unstable if data changes.
Keyset (Seek)	WHERE id > :lastId	Constant performance; stable results.	No random access (next/prev only); requires unique, ordered index.

Implementation: Keyset Pagination

To avoid the memory overhead of offsets and the risks of in-memory pagination, implement the Seek Method. This requires a unique, non-nullable column (usually the Primary Key) to act as the anchor for the next page.

Example Configuration

Assume a User entity with a createdAt timestamp and a unique id. To fetch the next page of 20 users after the last user seen on the previous page:

// Run this in your Service/Repository layer
// Required: Index on (createdAt, id)
String hql = "FROM User u WHERE (u.createdAt < :lastDate) " +
             "OR (u.createdAt = :lastDate AND u.id < :lastId) " +
             "ORDER BY u.createdAt DESC, u.id DESC";

List<User> results = session.createQuery(hql, User.class)
    .setParameter("lastDate", lastSeenDate)
    .setParameter("lastId", lastSeenId)
    .setMaxResults(20)
    .getResultList();

Optimizing for Read-Only Mass Exports

If you are paginating through a massive dataset for a report or export rather than a UI, use a StatelessSession. This bypasses the first-level cache (persistence context), preventing Hibernate from tracking every entity it loads, which significantly reduces heap usage.

// Use StatelessSession for read-heavy pagination
try (StatelessSession stateless = sessionFactory.openStatelessSession()) {
    // Execute paginated query here
}

Verification and Diagnostics

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

• SQL Inspection: Set hibernate.show_sql=true in your properties. If you see a SELECT without a LIMIT, OFFSET, or ROWNUM clause despite calling setMaxResults(), Hibernate is paginating in memory.
• Execution Plan: Run the generated SQL directly in your database using EXPLAIN ANALYZE. For Keyset pagination, you should see an Index Seek; for Offset pagination, you will see an Index Scan that grows linearly as the offset increases.
• Stress Test: Execute a query with a high offset (e.g., 100,000). If the response time increases significantly compared to the first page, you are experiencing offset degradation.

Rollback and Recovery

If you discover that HHH000104 is occurring in production, you must decouple the collection fetching from the pagination query:

1. Remove the JOIN FETCH from the paginated query to restore database-level pagination.
2. Fetch IDs first: Execute a query to get only the primary keys for the requested page.
3. Fetch full entities: Use those IDs in a second query with WHERE id IN (:ids) and JOIN FETCH to retrieve the associated collections for only those 20-50 records.