Preventing Memory Exhaustion in Quarkus with Hibernate Reactive Pagination

The Risk of the 'Fetch All' Pattern

When building data-driven APIs in Quarkus, it is tempting to return a simple list of entities from a database. However, as your dataset grows from hundreds to hundreds of thousands of records, a standard findAll() call becomes a liability. Loading a massive result set into the JVM heap doesn't just slow down the response; it risks an OutOfMemoryError (OOME) that can crash your entire pod.

The solution is pagination—requesting a small slice of data at a time. In a reactive stack using Hibernate Reactive and Mutiny, this must be done without blocking the event loop, ensuring your application remains responsive while the database handles the filtering.

Implementing Offset Pagination with Panache

Quarkus Panache simplifies pagination by providing a Page object that abstracts the underlying setFirstResult() and setMaxResults() calls of the Hibernate Session API. In a reactive context, these operations return a Uni<List<T>>, which is a lazy wrapper representing a future result.

Offset pagination works by telling the database to skip a specific number of rows (the offset) and return the next set of records (the limit). This is the most common approach for UI components like data tables with page numbers.

Worked Example: Paginated Product Catalog

Below is a implementation of a paginated repository. This example assumes you are using the quarkus-hibernate-reactive-panache extension.

// ProductRepository.java
import io.quarkus.hibernate.reactive.panache.PanacheRepository;
import jakarta.enterprise.context.ApplicationScoped;
import java.util.List;
import io.smallrye.mutiny.Uni;
import io.quarkus.panache.common.Page;

@ApplicationScoped
public class ProductRepository implements PanacheRepository {
    public Uni> findProductsPaged(int pageIndex, int pageSize) {
        // Page.of(index, size) creates the pagination metadata
        return find("order by name asc").page(Page.of(pageIndex, pageSize)).list();
    }
}

// ProductResource.java
import jakarta.ws.rs.*;
import jakarta.ws.rs.core.MediaType;
import io.smallrye.mutiny.Uni;
import java.util.List;

@Path("/products")
@Produces(MediaType.APPLICATION_JSON)
public class ProductResource {
    ProductRepository repository;

    @GET
    public Uni> getProducts(
            @QueryParam("page") @DefaultValue("0") int page,
            @QueryParam("size") @DefaultValue("20") int size) {
        return repository.findProductsPaged(page, size);
    }
}

Execution and Verification

• Run Location: This code runs within the Quarkus JVM.
• Permissions: The database user must have SELECT permissions on the target table.
• Expected Check: Enable SQL logging in application.properties using quarkus.hibernate-orm.log.sql=true. You should see LIMIT and OFFSET clauses in the generated SQL.
• Risk: Ensure you do not call .await().indefinitely() inside the resource method, as this blocks the reactive event loop and negates the benefits of Hibernate Reactive.

The Performance Wall: Deep Pagination

While offset pagination is easy to implement, it has a significant limitation known as "Deep Pagination." In most relational databases, OFFSET 10000 LIMIT 20 does not magically jump to the 10,001st row. Instead, the database must scan through the first 10,000 rows and discard them before returning the requested 20.

Metric	Shallow Page (Page 0)	Deep Page (Page 500)
DB Scan Effort	Minimal	High (Linear increase)
Response Time	Fast (ms)	Slower (ms to seconds)
Memory Usage	Low	Low (only the limit is returned)

If your application requires users to browse through thousands of pages, consider Keyset Pagination (or "cursor-based pagination"). Instead of an offset, you filter by the last seen ID: WHERE id > :lastSeenId LIMIT 20. This allows the database to use an index to jump directly to the starting point.

Practical Summary

To prevent memory exhaustion in Quarkus, move away from bulk fetches and implement PanacheRepository.page(). This ensures that only a manageable number of entities are instantiated in the heap at any given time. For most administrative interfaces, offset pagination is sufficient; for high-scale public APIs, evaluate the performance of deep offsets and transition to keyset pagination if response times degrade.