Avoiding OutOfMemoryErrors with Hibernate Offset Pagination

The Cost of Loading Everything

Loading a full dataset into a Java application is a common path to a java.lang.OutOfMemoryError. When a Hibernate query returns 100,000 entities, the Persistence Context—the internal cache that tracks entity states—must hold every single object in the JVM heap. This not only consumes memory; it slows the application as the Garbage Collector struggles to manage a massive surge of short-lived objects.

Shifting Filtering to the Database

To avoid loading everything, move the filtering logic to the database. Hibernate provides a database‑agnostic way to do this using the setFirstResult (offset) and setMaxResults (limit) methods on the Query API. The ORM translates these calls into dialect‑specific SQL, such as LIMIT/OFFSET for PostgreSQL/MySQL or OFFSET/FETCH for SQL Server.

Worked Example: Paginating a User Directory

Assume you need to show 20 users per page, ordered by username. The following method returns the requested page inside a transactional service.

// Run this inside a @Transactional service method
// Required permissions: SELECT on the user table
List getUsersPage(int pageNumber, int pageSize) {
    int offset = (pageNumber - 1) * pageSize;
    return session.createQuery("FROM User u ORDER BY u.username", User.class)
                  .setFirstResult(offset)   // skip previous pages
                  .setMaxResults(pageSize)  // limit page size
                  .getResultList();
}

Expected SQL: With MySQL the generated statement includes LIMIT 20 OFFSET 40 for page 3 (you can verify this by enabling Hibernate SQL logging).

When Pagination Falls Back to Memory

One dangerous pitfall is using JOIN FETCH on a @OneToMany collection while also applying setFirstResult. Hibernate detects that the join would produce duplicate root entities and may log: firstResult/maxResults specified with collection; applying in memory!. When this happens, the ORM executes the full query, pulls every row into the JVM, and then discards the unwanted rows—exactly the scenario pagination tries to avoid.

Trade‑offs and Limitations

Offset‑based pagination suffers from deep paging degradation: the database must scan and skip all preceding rows before returning the requested slice. The latency grows roughly linearly with the offset size.

Scenario	Performance Impact	Typical Risk
Low offset (pages 1‑10)	Negligible	Low
High offset (page 1000+)	Linear increase in latency	CPU spikes on the DB
Unsorted or non‑indexed ORDER BY	Unpredictable ordering	Items shift between pages

To mitigate, always place an index on the column(s) used in the ORDER BY clause. Without an index the database performs a full table scan and a sort for each page request.

Verification and Diagnostics

1. Enable SQL logging in application.properties: hibernate.show_sql=true (or the equivalent for your logging framework).
2. Run the method and inspect the console; you should see a statement containing LIMIT or OFFSET/FETCH and no mention of “applying in memory”.
3. Optionally, copy the generated SQL into a database client and compare execution time for a low offset (e.g., 0) versus a high offset (e.g., 100000).
4. Monitor JVM heap usage (e.g., via jstat or a profiling tool) when switching from a full‑list query to the paginated version; heap growth should be markedly lower.

Closing Recommendation

For most listing screens, offset‑based pagination with an indexed ORDER BY keeps memory usage predictable and avoids OutOfMemoryErrors. If you anticipate users frequently jumping to very high page numbers, consider a keyset (seek‑based) pagination strategy that uses the last seen value instead of an offset.