Choosing Cursor‑Based Pagination in Hibernate to Avoid Offset Overhead

Use a stable identifier instead of OFFSET for large result sets

When you need to page through hundreds of thousands or millions of rows with Hibernate, the typical setFirstResult/setMaxResults approach translates to SQL LIMIT … OFFSET …. As the offset grows, the database must scan and skip preceding rows, which degrades performance even when the sorted column is indexed. A more scalable alternative is cursor‑based pagination: remember the last value of a unique, monotonic column (often the primary key) and fetch the next page with a WHERE column > :lastValue predicate.

Worked example with JPA/Hibernate

Assume an entity Order with a surrogate primary key id of type Long. The service method below returns the next page of orders after a given cursor.

@Transactional(readOnly = true)
public List findOrdersAfter(Long lastId, int pageSize) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery cq = cb.createQuery(Order.class);
    Root root = cq.from(Order.class);
    
    // Cursor condition: only rows with id greater than the last seen id
    if (lastId != null) {
        cq.where(cb.greaterThan(root.get("id"), lastId));
    }
    cq.orderBy(cb.asc(root.get("id")));
    
    TypedQuery query = entityManager.createQuery(cq)
                                            .setMaxResults(pageSize);
    
    List result = query.getResultList();
    return result.stream()
                 .map(this::toDto)
                 .collect(Collectors.toList());
}

The generated SQL (visible with Hibernate’s show_sql or a JDBC logger) will resemble:

select order0_.id as id1_0_, order0_.customer_id as customer2_0_, ...
from orders order0_
where order0_.id > ?
order by order0_.id asc
limit ?

No OFFSET clause appears, so the database can use an index on id to locate the starting point directly.

Limits and common mistakes

• Monotonic, unique column required – If the cursor column can repeat or decrease, rows may be skipped or duplicated. Primary keys or a combination of columns that together form a unique, ever‑increasing sequence satisfy this requirement.
• Deletions affect page size – Removing rows with ids lower than the cursor does not cause duplicates, but it can make a page appear shorter because the total count of remaining rows is lower. Applications that rely on a fixed page size for UI navigation must account for this.
• Index on the cursor column – Without an index, the database still scans to find the first matching row. Verify that id (or your chosen column) is indexed.
• Fetch joins can break the cursor – Adding JOIN FETCH for associations may produce duplicate root rows when the association is one‑to‑many. Use DISTINCT carefully or fetch associations lazily after the page is loaded.
• No random page jumps – Cursor‑based pagination does not support jumping directly to page N without iterating through preceding pages. If random access is essential, consider a hybrid approach (e.g., store pre‑computed boundaries) or accept the offset cost for occasional jumps.

Practical verification steps

1. Enable Hibernate SQL logging: logging.level.org.hibernate.SQL=DEBUG (or show_sql=true for quick tests).
2. Run the method with a known lastId and page size, then inspect the log for the generated SQL. Confirm the absence of an OFFSET clause and presence of a WHERE id > ? predicate.
3. On a table with >1 M rows, execute an EXPLAIN ANALYZE (PostgreSQL) or equivalent for the generated query. Look for an index scan on the primary key rather than a sequential scan with a filter.
4. Measure latency for increasing cursor values (e.g., lastId = 0, 100k, 500k) and verify that response time remains roughly constant, contrasting it with the growing latency of an offset‑based query.