Stopping the Memory Leak: Implementing Efficient Pagination in Java with Hibernate

The Danger of the 'Fetch All' Approach

When building a Java application that interacts with a relational database, it is tempting to retrieve a list of entities using a simple findAll() or a broad HQL query. This works during development with ten test records, but in production, it becomes a liability. Attempting to load 100,000 records into the JVM heap will lead to OutOfMemoryError crashes or severe garbage collection pauses that freeze the application.

The solution is pagination: ensuring the database only sends the specific slice of data the user needs. However, implementing this incorrectly in Hibernate can lead to a deceptive performance trap where the database sends everything anyway, and Hibernate filters it in memory.

Limit-Offset Pagination

The most common way to paginate in Hibernate is using setFirstResult() and setMaxResults(). These methods tell Hibernate to append the appropriate limit and offset clauses to the generated SQL, based on the database dialect being used (e.g., LIMIT/OFFSET for PostgreSQL or OFFSET/FETCH for SQL Server).

If you are using Spring Data JPA, this is abstracted into the Pageable interface. By passing a PageRequest to your repository method, the framework handles the underlying Hibernate calls automatically.

The 'In-Memory' Pagination Trap

A critical risk occurs when combining pagination with JOIN FETCH on a collection (a OneToMany relationship). Because a join fetch creates a Cartesian product of the results, Hibernate cannot reliably calculate the offset at the database level.

If you do this, you will see the warning: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!. This means Hibernate downloads the entire table into the JVM and then discards the rows it doesn't need. This completely defeats the purpose of pagination and will crash your application under load.

Practical Implementation: Offset vs. Keyset

Depending on your dataset size, you have two primary strategies. The following example assumes Java 17+ and Hibernate 6.x.

Example 1: Standard Offset Pagination

Use this for small to medium datasets or admin panels where users jump to specific page numbers.

// Run this in your Service layer with @Transactional permissions
public List<Product> getProductsPaged(int pageNumber, int pageSize) {
    return session.createQuery("FROM Product p ORDER BY p.id ASC", Product.class)
                  .setFirstResult(pageNumber * pageSize)
                  .setMaxResults(pageSize)
                  .getResultList();
}

Example 2: Keyset Pagination (The Seek Method)

For massive datasets or "infinite scroll" feeds, offset pagination slows down because the database must scan through all skipped rows. Keyset pagination avoids this by filtering based on the last seen ID.

// Run this in your Service layer
public List<Product> getProductsSeek(Long lastSeenId, int pageSize) {
    return session.createQuery("FROM Product p WHERE p.id > :lastId ORDER BY p.id ASC", Product.class)
                  .setParameter("lastId", lastSeenId)
                  .setMaxResults(pageSize)
                  .getResultList();
}

Comparing Pagination Strategies

Feature	Offset Pagination	Keyset Pagination
Implementation	Simple (Page Number)	Moderate (Last ID)
Performance	Degrades as page increases	Constant performance
User Experience	Jump to page 50	Next/Previous only
Consistency	Items shift if rows are deleted	Stable results

Verification and Limitations

To verify that pagination is happening at the database level and not in the JVM, enable SQL logging in your application.properties:

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

Check the logs for the presence of LIMIT or OFFSET keywords. If you see a simple SELECT without these clauses but your Java list is small, Hibernate is paginating in memory, and you must remove any collection JOIN FETCH calls from that query.

Limitation: Keyset pagination requires a deterministic, unique sort column (like a Primary Key). If you sort by a non-unique column (e.g., user_name), you must include the ID as a secondary sort to prevent records from being skipped or duplicated between pages.

Actionable Summary

• Use setFirstResult and setMaxResults for basic paging.
• Never use JOIN FETCH on collections in a paginated query; use a separate query or @BatchSize to handle the children.
• Switch to Keyset pagination if your users are scrolling through thousands of records.
• Always define an explicit ORDER BY to ensure consistent results across page requests.