Hibernate Pagination: Avoiding Memory Exhaustion with setFirstResult and setMaxResults

Problem: Large Result Sets Drain the JVM Heap

When a Hibernate query returns millions of rows, the ORM materialises each row into an entity instance. If you call query.getResultList() without any limits, the JVM will try to hold all those instances in memory. Depending on the heap size, the application can throw java.lang.OutOfMemoryError or suffer severe GC pauses.

The fix is to let the database do the heavy lifting: ask it to return only the slice you need. Hibernate provides two methods on org.hibernate.query.Query (and jakarta.persistence.TypedQuery) to express offset and limit.

How Hibernate Maps setFirstResult and setMaxResults

setFirstResult(int offset) tells the database to skip offset rows before returning results. It is 0‑indexed, so setFirstResult(0) starts from the first row.

setMaxResults(int limit) limits the number of rows the database returns. The ORM translates these calls into the appropriate SQL clause for the current dialect: LIMIT/OFFSET for PostgreSQL/MySQL, OFFSET ... FETCH NEXT for Oracle 12c+, etc.

Worked Example – Paginated User Query

The following method is a typical Spring Service snippet. It assumes a SessionFactory is injected via @PersistenceContext or @Autowired and that the transaction is managed by Spring.

// Requires a read‑only transaction
@Transactional(readOnly = true)
public List<User> findUsersPage(int pageNumber, int pageSize) {
    int offset = (pageNumber - 1) * pageSize;
    // HQL query with a deterministic order
    return sessionFactory.getCurrentSession()
            .createQuery("FROM User u ORDER BY u.createdAt DESC", User.class)
            .setFirstResult(offset)
            .setMaxResults(pageSize)
            .getResultList();
}

Key points:

• Order By – Pagination without a deterministic order can return different rows on each request.
• Offset calculation – For page 3 of size 20, offset = (3-1)*20 = 40.
• Transactional context – In Spring Boot, the @Transactional annotation ensures the session is flushed and closed properly.

Verifying That Pagination Happens in the Database

Enable SQL logging. In a Spring Boot application, add the following to application.yml or application.properties:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Running the service method should print a SQL statement similar to:

SELECT * FROM users u ORDER BY u.created_at DESC LIMIT 20 OFFSET 40

If you see LIMIT and OFFSET (or the dialect‑specific equivalent) the pagination is executed by the database. If those keywords are missing, Hibernate may have applied pagination in memory.

Common Pitfalls

Deep Pagination

When offset is very large (hundreds of thousands), the database still scans and discards that many rows before delivering the slice. The query cost grows roughly linearly with the offset. For very large tables, consider keyset pagination instead.

Join‑Fetch with Pagination

Using JOIN FETCH on a collection (e.g., LEFT JOIN FETCH u.orders) can cause Hibernate to generate a result set where each parent row is repeated for each child. In Hibernate 5, this triggers the warning HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! and forces in‑memory pagination. Hibernate 6 introduced a different strategy: it may wrap the query in a subquery to apply the limit before the join, but the exact behaviour depends on the dialect and the version of Hibernate. Therefore, if you need pagination on a parent entity with collections, either:

• Use setMaxResults on a plain query and then manually fetch the collections lazily, or
• Switch to keyset pagination on a scalar column.

OutOfMemoryError Overstatement

Whether an OutOfMemoryError occurs depends on the JVM heap size, the size of each entity, and the number of rows fetched. The statement “the application will crash” is only true if the heap is exhausted. It is safer to say “the query can consume a large amount of memory and may lead to OutOfMemoryError if not paginated.”

Keyset Pagination – An Alternative for Large Datasets

Instead of offsetting, keyset pagination uses a cursor based on a unique, monotonically increasing column (often the primary key or a timestamp). The SQL looks like:

SELECT * FROM users
WHERE created_at < :lastTimestamp
ORDER BY created_at DESC
LIMIT 20

Pros:

• Performance stays constant regardless of how many rows have already been paged.
• Ideal for infinite scroll or “load more” interfaces.

Cons:

• You cannot jump directly to page N; you must start from the last seen value.
• If two rows have the same created_at value, you need a tie‑breaker (e.g., AND id < :lastId) to avoid duplicates or skips.

Practical Verification Steps

1. Run the query with setFirstResult(0) and setMaxResults(5). Check the SQL log for LIMIT 5.
2. Repeat with setFirstResult(100000) and setMaxResults(5). Measure the execution time; it should increase roughly linearly.
3. Execute the same query with a JOIN FETCH on a collection and observe whether Hibernate logs the HHH000104 warning. If present, it indicates in‑memory pagination.
4. Switch to keyset pagination and confirm that the SQL contains a WHERE clause based on the cursor column.

Limitations and Recommendations

• Offset pagination is simple but unsuitable for very deep pagination. Use keyset pagination for datasets that grow beyond a few million rows.
• Always pair setFirstResult and setMaxResults with a deterministic ORDER BY clause.
• Beware of join‑fetch with collections; consider fetching collections lazily or using separate queries.
• When using Spring Data JPA, the Pageable interface automatically applies these two methods; just make sure your repository method returns Page<T>.

Conclusion

By correctly applying setFirstResult and setMaxResults, verifying the generated SQL, and avoiding deep offsets and collection fetches, you can keep Hibernate queries efficient and prevent memory exhaustion even when working with tables that contain millions of rows.