Solving the Memory Spike: Implementing Efficient Pagination in Hibernate

The Problem: The "Load Everything" Trap

When building a Java application that interacts with a database containing thousands or millions of records, the instinct is often to return a List<Entity>. However, fetching a full dataset into the Java Virtual Machine (JVM) leads to immediate memory pressure, increased Garbage Collection (GC) overhead, and eventually, OutOfMemoryError. Even if the JVM survives, the latency for the end‑user is unacceptable.

The solution is to move the filtering logic from the application layer to the database layer. In Hibernate, this is achieved through pagination, ensuring the database only transmits the specific slice of data requested for the current view.

Implementing Offset-Based Pagination

Hibernate provides two primary methods on the Query and TypedQuery interfaces to handle pagination: setFirstResult() and setMaxResults().

• setFirstResult(int startPosition): Defines the offset. It tells the database how many rows to skip before starting to return results.
• setMaxResults(int maxResults): Defines the page size. It limits the number of records returned in a single request.

For these methods to be useful, you must include an ORDER BY clause in your HQL or JPQL. Without a deterministic sort order, the database may return records in a different sequence between page requests, leading to duplicate items appearing on multiple pages or some records being skipped entirely.

Worked Example: Paginated User Retrieval

Assume a Hibernate 6.x environment using a PostgreSQL database. The following implementation demonstrates how to retrieve a specific page of users based on their registration date.

// Run this within a Transactional service layer
public List getUsersPage(int pageNumber, int pageSize) {
    // Calculate the offset: Page 0 starts at 0, Page 1 starts at pageSize
    int offset = pageNumber * pageSize;

    String hql = "FROM User u ORDER BY u.registrationDate DESC";
    TypedQuery query = session.createQuery(hql, User.class);

    // Apply pagination limits
    query.setFirstResult(offset);
    query.setMaxResults(pageSize);

    return query.getResultList();
}

Verification and Diagnostics

To verify that Hibernate is actually delegating the work to the database rather than fetching everything into memory, enable SQL logging in your hibernate.cfg.xml or application.properties:

hibernate.show_sql=true
hibernate.format_sql=true

Check your console output. You should see a SQL query ending with LIMIT ? OFFSET ? (for PostgreSQL/MySQL) or OFFSET ? ROWS FETCH NEXT ? ROWS ONLY (for SQL Server). If you do not see these clauses, Hibernate may be performing pagination in memory, which is a critical performance risk.

The "In-Memory" Pagination Warning

A common pitfall occurs when combining pagination with JOIN FETCH on a OneToMany collection. If you attempt to paginate a query that fetches a collection of child entities, Hibernate may log a warning: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

This happens because the join creates a Cartesian product (multiple rows for one parent entity), making the SQL LIMIT clause inaccurate. To solve this, fetch the parent IDs first using pagination, and then perform a second query to fetch the associated collections for those specific IDs.

Trade-offs: The Cost of Deep Pagination

While setFirstResult() is easy to implement, it suffers from performance degradation as the offset increases. This is known as "Deep Pagination." To fulfill a request for OFFSET 100000 LIMIT 20, the database must still scan through the first 100,000 rows before discarding them and returning the final 20.

Approach	Pros	Cons
Offset-Based	Easy to implement; supports random access to pages.	Slows down significantly on large offsets.
Keyset-Based	Constant performance regardless of depth.	No random access (cannot jump to page 500).

Actionable Summary

To implement pagination safely in Hibernate:

1. Always pair setFirstResult() and setMaxResults() with an ORDER BY clause.
2. Monitor logs for the HHH000104 warning to avoid accidental in‑memory pagination.
3. Verify the generated SQL contains the appropriate LIMIT and OFFSET keywords for your dialect.
4. For datasets where users frequently access very deep pages, consider moving from offset‑based pagination to keyset‑based pagination (filtering by the last seen ID).