Choosing Pagination Strategies in Quarkus with Hibernate Panache

The Pagination Performance Gap

When building APIs in Quarkus that return large datasets, the primary challenge is avoiding memory exhaustion and database timeouts. The common instinct is to use offset and limit, but as the dataset grows, the database must scan and discard thousands of rows before returning the requested page, leading to linear performance degradation.

The decision rests on whether your application requires random access to pages (jumping to page 100) or a continuous stream of data (infinite scroll or "next page" navigation).

Comparison: Offset vs. Keyset Pagination

Feature	Offset-based (Limit/Offset)	Keyset-based (Seek Method)
Implementation	Simple (Built-in Panache methods)	Manual WHERE clause logic
Performance	Degrades as page number increases	Constant time (O(1) lookup)
Data Consistency	Prone to duplicates/skips on inserts	Stable across data changes
Page Jumping	Supported (Jump to page N)	Not supported (Next/Previous only)

Trade-offs and Constraints

Offset-based Pagination

This method uses setFirstResult() and setMaxResults() under the hood. It is ideal for small datasets or administrative panels where users need to jump to specific pages. However, because the database must read all preceding rows to find the starting point, high offset values cause significant CPU and I/O spikes on the database server.

Keyset-based Pagination

Also known as the "Seek Method," this strategy uses the value of the last item from the previous page as a filter for the next query. It requires a strictly ordered, non-nullable unique column (usually a primary key or a composite of a timestamp and ID). This ensures the database can perform an index seek rather than a scan.

Implementation in Quarkus Panache

Assuming a Quarkus project using quarkus-hibernate-orm-panache and a Product entity with a unique id.

Option 1: Offset Implementation

Run this in a PanacheRepository. This is the standard approach for simple requirements.

public List<Product> findProductsOffset(int pageIndex, int pageSize) {
    // pageIndex is 0-based
    return findAll(
        Sort.by("name"), 
        PanacheQuery.paginationPage(pageIndex)
    ).list();
}

Option 2: Keyset Implementation

For high-performance feeds, implement a seek filter. This example assumes sorting by id ascending.

public List<Product> findProductsKeyset(Long lastSeenId, int pageSize) {
    if (lastSeenId == null) {
        return find("order by id asc", Sort.by("id")).page(0, pageSize).list();
    }
    // Seek only records greater than the last ID seen
    return find("id > ?1 order by id asc", lastSeenId).page(0, pageSize).list();
}

Validating the Decision

To verify which method is necessary for your scale, use the following diagnostic checks:

• Execution Plan Analysis: Run a query with a high offset (e.g., 100,000) in your database console. Look for Index Scan or Full Table Scan. A Keyset query should consistently show an Index Seek regardless of how deep the pagination goes.
• Drift Test: While navigating from page 1 to page 2 using Offset pagination, insert a new record that belongs on page 1. You will notice the last item of page 1 shifts to the first item of page 2, creating a duplicate in the UI. Keyset pagination avoids this because the filter is tied to a specific ID, not a position.

Limitations

Keyset pagination cannot support "Jump to Page 50" because the application does not know the ID of the 50th page's first element without fetching all prior records. If your business requirements mandate random page access, you must use Offset pagination and implement strict limits on the maximum allowable page number to protect database health.