Stopping the Slide: Solving Deep Pagination Lag in Hibernate

The 'Page 1000' Performance Wall

Most developers implement pagination in Hibernate using setFirstResult() and setMaxResults(). For the first few pages, this works perfectly. However, as a user navigates deeper into a dataset—say, page 1,000 of a million-row table—the response time spikes. This happens because the database cannot simply jump to the 10,000th row; it must scan and discard all preceding rows to ensure the result set is accurate.

The solution is a shift from Offset Pagination to Keyset Pagination (also known as the Seek Method). By filtering based on the last seen value of a unique identifier rather than a row count, you maintain constant performance regardless of how deep the user scrolls.

Offset Pagination: The Hidden Cost

When you call query.setFirstResult(10000), Hibernate generates SQL using OFFSET 10000 LIMIT 20. The database engine must physically read 10,020 rows, throw away the first 10,000, and return the final 20. This is an O(n) operation.

This approach is acceptable if your UI requires "Random Access" (the ability to click directly on Page 54), but it becomes a liability for large datasets or "Infinite Scroll" interfaces where users only move forward.

Keyset Pagination: The Seek Method

Keyset pagination avoids the scan by using a WHERE clause on a sorted, unique column (usually the Primary Key). Instead of telling the database how many rows to skip, you tell it exactly where to start.

This transforms the operation into an Index Seek. The database uses the B-Tree index to jump directly to the first record that satisfies the condition, keeping performance at O(log n) or O(1) regardless of the page depth.

Implementation Example

Assume we are paginating a Product entity sorted by id. To implement this in Hibernate, you must manually handle the "last seen" value from the previous page.

// Required: A unique, ordered column (e.g., Product ID) 
// This query should be executed in your Repository/DAO layer
public List<Product> findProductsPaged(Long lastSeenId, int pageSize) {
    String hql = "FROM Product p WHERE p.id > :lastId ORDER BY p.id ASC";
    
    return session.createQuery(hql, Product.class)
        .setParameter("lastId", lastSeenId != null ? lastSeenId : 0L)
        .setMaxResults(pageSize)
        .getResultList();
}

Execution Logic:

• Page 1: Pass lastSeenId = null. The query starts at ID 0.
• Page 2: Pass the ID of the last element of the Page 1 list.
• Page 3: Pass the ID of the last element of Page 2.

Trade-offs and Limitations

Keyset pagination is not a universal replacement for offset pagination. There are critical constraints to consider:

Feature	Offset Pagination	Keyset Pagination
Random Page Access	Supported (Jump to Page X)	Not Supported (Next/Prev only)
Deep Page Speed	Degrades linearly	Remains constant
Data Consistency	Items may shift/duplicate if rows are added	Stable; records are anchored to a value
Requirements	None	Strictly ordered, non-null unique key

Verifying the Performance Gain

To verify that your implementation is actually using the index rather than scanning, run the generated SQL directly in your database console with EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL).

• Offset Warning: Look for "Parallel Seq Scan" or "Index Scan" with a high number of "rows removed by filter."
• Keyset Success: Look for "Index Seek" or "Index Only Scan," where the number of rows examined is nearly equal to your pageSize.

Practical Checklist for Deployment

1. Index the Sort Key: Ensure the column used in the WHERE clause has a database index.
2. Handle Nulls: Ensure the sort key is NOT NULL to prevent records from being skipped.
3. API Contract: Update your API to accept a lastId parameter instead of a pageNumber.