Stream Large Hibernate Result Sets Without Exhausting the Heap

When a Hibernate query returns millions of rows, the default List-based approach can exhaust your JVM heap. The fix is not always to switch databases or add more RAM. In many cases, using ScrollableResults with a forward-only scroll mode and a tuned fetch size lets you process large datasets as a stream, keeping only a small window of rows in memory at any time.

Why List Queries Break Under Load

Hibernate's typical query methods—such as list() or JPA's getResultList()—materialize the entire result set into a java.util.List before returning control to your application. Each entity becomes a managed object inside the persistence context, and the JVM must hold every row simultaneously. For queries touching hundreds of thousands or millions of records, this allocation pattern frequently triggers OutOfMemoryError in production.

How ScrollableResults Changes the Pattern

A ScrollableResults instance wraps a JDBC cursor. Instead of loading all rows upfront, it fetches data incrementally. By combining it with ScrollMode.FORWARD_ONLY and setting a JDBC fetch size, you instruct the driver to retrieve rows in batches—say, 500 at a time—rather than pulling the entire result set across the wire in one round trip. The persistence context still tracks entities, but you can evict them as you go to keep memory bounded.

It is worth noting that setFetchSize is a hint, not a guarantee. Some JDBC drivers ignore it entirely, and behavior varies across database dialects. You must verify the actual behavior on your target database.

A Worked Example

The following pattern assumes a recent Hibernate version (6.x line) and a standard SessionFactory. It opens a transaction, configures a fetch size, iterates through results, and evicts processed entities to prevent the persistence context from growing without bound.

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

try {
    Query<MyEntity> query = session.createQuery(
        "from MyEntity where status = :status", MyEntity.class);
    query.setParameter("status", "ACTIVE");
    query.setFetchSize(500); // hint to the JDBC driver

    ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);
    try {
        while (results.next()) {
            MyEntity entity = (MyEntity) results.get();
            process(entity);
            session.evict(entity);
        }
    } finally {
        results.close();
    }

    tx.commit();
} finally {
    session.close();
}

Run this inside a service method where you control the session lifecycle. The finally blocks ensure that both the cursor and the session are closed, returning the underlying connection to the pool. If you forget to close the ScrollableResults, the JDBC cursor may remain open and exhaust your connection pool.

Trade-offs and Limitations

Scrolling is not free. The trade-off is that pagination becomes slower per page and requires the session to remain open, which can affect connection pooling and transaction management. A long-running scroll holds a database connection for the duration of the iteration, so short transactions and aggressive timeouts become more important.

Not all JPA providers or JDBC drivers honor fetchSize. Some drivers buffer the entire result set client-side regardless of the hint. Others may throw an error if the scroll mode is unsupported. Test on your target database to ensure behavior before deploying the pattern broadly.

How to Check the Result

To validate that the pattern works on your stack, write a small Java application using a recent Hibernate version, populate a table with a large number of rows, then use a ScrollableResults with setFetchSize(500) and measure peak heap usage via jvisualvm or jcmd. Run the same query using a List-based approach and compare the heap profile; the scrolling version should show a significantly lower peak.

You can also verify resource cleanup directly. After the scroll finishes, confirm that the connection is returned to the pool. If the pool shows a leaked connection, the most likely cause is a missing close() on the ScrollableResults or the Session.

Actionable Guidance

Use ScrollableResults when you need to process a large read-only result set exactly once and you can tolerate a longer overall runtime. Avoid it for interactive pagination where users jump between pages, because backward scrolling is either unsupported or inefficient in FORWARD_ONLY mode. Always pair the scroll with explicit eviction or periodic clear() calls, and treat fetchSize as a tuning knob that requires empirical verification on your specific database and driver combination.