Implementing Non-Blocking Database Pagination with Quarkus and Hibernate Reactive

The Problem: Memory Exhaustion During Large Data Retrieval

Fetching thousands of records from a database into an application's memory leads to high heap usage, increased latency, and potential OutOfMemoryError crashes. In a reactive environment, blocking the event loop while waiting for a massive result set defeats the purpose of an asynchronous stack. To maintain scalability, you must retrieve data in small, manageable chunks—a process known as pagination.

Prerequisites

• Quarkus project with the quarkus-hibernate-reactive-panache extension.
• A compatible reactive driver, such as quarkus-reactive-pg-client for PostgreSQL.
• A database entity extending PanacheEntity or PanacheEntityBase.

Implementing Offset-Based Pagination

Quarkus Panache provides a Page object that abstracts the calculation of offsets and limits. This allows you to request a specific page index and page size without manually calculating the starting row.

The Implementation Pattern

To implement pagination, you must use the .page() method on a PanacheQuery. Because Hibernate Reactive is non‑blocking, these operations return a Uni (a Mutiny type representing a single asynchronous result).

import io.quarkus.panache.common.Page;
import io.quarkus.panache.common.PanacheQuery;
import io.smallrye.mutiny.Uni;
import javax.enterprise.context.ApplicationScoped;

@ApplicationScoped
public class ProductService {
    public Uni<List<Product>> getProductsPaged(int pageIndex, int pageSize) {
        PanacheQuery<Product> query = Product.findAll();
        return query.page(Page.of(pageIndex, pageSize)).list();
    }
}

Handling Total Counts for Frontend Navigation

A list of results is rarely enough; the frontend typically needs the total number of pages to render pagination controls. You can retrieve the total count using the pageCount() method on the query object.

public Uni<PagedResponse<Product>> getProductsWithMetadata(int pageIndex, int pageSize) {
    PanacheQuery<Product> query = Product.findAll();
    return Uni.combine().all().unis(
        query.page(Page.of(pageIndex, pageSize)).list(),
        query.pageCount()
    ).asTuple().onItem().transform(tuple -> {
        return new PagedResponse<Product>(tuple.getItem1(), tuple.getItem2(), pageIndex, pageSize);
    });
}

Diagnostic Verification

1. Enable SQL Logging: Add quarkus.hibernate-orm.log.sql=true to your application.properties.
2. Execute Request: Trigger your endpoint with parameters (e.g., ?page=2&size=10).
3. Inspect Logs: Look for the LIMIT and OFFSET clauses in the console output. For PostgreSQL, you should see something similar to: SELECT ... LIMIT 10 OFFSET 20;

Performance Limitations and Risks

Risk	Technical Cause	Mitigation
Deep Pagination Lag	High OFFSET values force the DB to scan and discard thousands of rows.	Use Keyset Pagination (filtering by the last ID seen) for very large datasets.
Connection Leaks	Reactive streams not being subscribed to or transactions left open.	Ensure all methods are wrapped in @ReactiveTransactional or managed via Panache.withTransaction().
Inconsistent Pages	Data inserted/deleted between page requests shifts the result set.	Use a consistent sort order (e.g., ORDER BY id) to stabilize the window.

Rollback and Recovery

Since pagination is a read‑only operation, there is no state change to roll back in the database. However, if you experience TimeoutException during deep pagination, revert to a smaller pageSize or implement a maximum allowed pageIndex to protect database resources.