Solving Deep Pagination Performance in Hibernate: Offset vs. Keyset

The 'Deep Page' Performance Trap

When building a data-heavy application with Hibernate, the standard approach to pagination is using setFirstResult() and setMaxResults(). This works perfectly for the first few pages. However, as a user navigates to page 1,000 or 10,000, the application often slows down or crashes. This happens because offset-based pagination forces the database to scan and discard thousands of rows before returning the requested slice.

The solution for high-performance data retrieval is Keyset Pagination (also known as the Seek Method). Instead of telling the database how many rows to skip, you tell it exactly where to start reading based on the last record of the previous page.

How Offset Pagination Fails at Scale

Offset pagination translates to a LIMIT X OFFSET Y SQL clause. To reach an offset of 100,000, the database engine must physically read 100,000 rows from the disk, count them, and then discard them to give you the next 20 results. This creates an O(n) complexity where performance degrades linearly as the page number increases.

Beyond performance, offset pagination suffers from result drifting. If a new record is inserted on page one while a user is navigating to page two, the last item from page one shifts to page two, causing the user to see a duplicate entry.

Implementing the Seek Method (Keyset Pagination)

Keyset pagination avoids the scan by using a WHERE clause on a unique, indexed column—usually a primary key or a timestamp combined with an ID. This allows the database to use a B-Tree index to jump directly to the starting point, maintaining O(log n) or O(1) complexity regardless of how deep the pagination goes.

Worked Example: Implementing Keyset Pagination

Assume we are fetching a list of Order entities sorted by createdAt and id. To ensure a stable sort, we must include the unique ID in the sort order.

// In a Spring Data JPA / Hibernate Repository context
// Instead of Pageable, we pass the values of the last item seen on the previous page

public List<Order> findOrdersAfter(LocalDateTime lastCreatedAt, Long lastId, int pageSize) {
    return entityManager.createQuery(
        "SELECT o FROM Order o " +
        "WHERE (o.createdAt < :lastDate) " +
        "OR (o.createdAt = :lastDate AND o.id < :lastId) " +
        "ORDER BY o.createdAt DESC, o.id DESC", Order.class)
        .setParameter("lastDate", lastCreatedAt)
        .setParameter("lastId", lastId)
        .setMaxResults(pageSize)
        .getResultList();
}

Execution Details:

• Where to run: This logic resides in your Service or Repository layer.
• Required Permissions: Standard database read permissions for the application user.
• Placeholders: lastCreatedAt and lastId must be extracted from the final element of the previous result list.
• Expected Check: The database execution plan should show an Index Seek rather than an Index Scan or Filesort.

Trade-offs and Limitations

While keyset pagination is significantly faster, it introduces specific constraints:

Feature	Offset-Based	Keyset-Based
Random Access	Supported (Jump to page 50)	Unsupported (Sequential only)
Performance	Degrades with depth	Constant/Stable
Data Stability	Prone to drifting/duplicates	Consistent
Index Requirement	Standard indexing	Requires composite index on sort columns

The most significant limitation is the loss of "Jump to Page X" functionality. If your UI requires a numbered pagination bar, keyset pagination is not a drop-in replacement. It is best suited for "Infinite Scroll" or "Load More" interfaces.

Verification and Diagnostics

To verify if your pagination is performing correctly, run the following diagnostic check on your database:

1. Execute a query with a high offset (e.g., setFirstResult(100000)).
2. Run EXPLAIN ANALYZE (PostgreSQL/MySQL) on the resulting SQL.
3. Look for Seq Scan or high rows removed by filter counts.
4. Replace the query with the Keyset method and verify the plan changes to an Index Seek.

Warning: Keyset pagination only works if the columns used in the WHERE clause are indexed. If you sort by a non-indexed column, the database will perform a full table scan, negating all performance gains.