Efficient Hibernate Pagination with Cursor-Based Techniques

The problem: offset pagination stalls on large tables

When a REST endpoint needs to return a page of results from a table with millions of rows, the typical Hibernate approach uses setFirstResult and setMaxResults. Under the hood Hibernate issues a count query to determine the total number of rows before applying the offset, which forces the database to scan the entire table (or at least a large index range) even though only a few rows are ultimately returned. As the offset grows, the cost of that count query rises linearly, making deep‑page requests slow and increasing load on the database.

Thesis: cursor‑based pagination avoids the costly count and streams rows

By using a stable, ordered column (usually the primary key or a unique indexed column) as a "cursor" and letting Hibernate fetch rows in a forward‑only stream, we can eliminate the count query and keep memory usage constant regardless of page size. Hibernate’s ScrollableResults combined with a sensible fetchSize provides exactly this behavior.

How cursor‑based pagination works

1. Order the query by a column that is guaranteed to be unique and never changes (e.g., id BIGINT PRIMARY KEY).
2. Remember the last value of that column returned on the previous page.
3. On the next request, add a WHERE id > :lastId clause and keep the same ordering.
4. Set a reasonable fetchSize on the JDBC statement so Hibernate pulls rows in batches from the database without materializing the whole result set.
5. Iterate over the ScrollableResults until the desired page size is reached, then close the scroll.

Worked example

Assume we have an entity Order with a primary key id. The service method below returns a page of orders after a given cursor.

@Transactional(readOnly = true)
public List fetchOrdersAfter(Long cursor, int pageSize) {
    // Build the JPQL query ordered by id
    String jpql = "SELECT o FROM Order o WHERE (:cursor IS NULL OR o.id > :cursor) ORDER BY o.id ASC";
    
    // Create the query and set parameters
    TypedQuery query = entityManager.createQuery(jpql, Order.class)
            .setParameter("cursor", cursor)
            .setMaxResults(pageSize);
    
    // Enable streaming: hint Hibernate to use a scrollable result set
    query.setHint("org.hibernate.fetchSize", 50); // fetchSize tuned to your DB driver
    
    // Execute and collect results
    List orders = query.getResultList();
    
    // Convert to DTOs (or return entities if appropriate)
    return orders.stream()
            .map(OrderDto::fromEntity)
            .collect(Collectors.toList());
}

Key points in the example:

• The query uses a cursor condition (o.id > :cursor) instead of setFirstResult.
• setMaxResults limits the number of rows returned; no count query is issued because the ordering column is unique and the query can stop after the limit.
• The hint org.hibernate.fetchSize (or the equivalent JDBC setFetchSize when using native SQL) tells the driver to fetch rows in batches, keeping memory usage low.
• If cursor is null, the first page is returned.

Trade‑offs and limitations

While cursor‑based pagination solves the offset problem, it introduces a few constraints:

• Only forward‑only navigation: jumping to an arbitrary page number (e.g., "page 42") is not possible without iterating through all preceding cursors. Applications that require random page jumps must keep a separate index or accept the overhead.
• Stable ordering column required: if the ordered column can change (e.g., a timestamp that may be updated), duplicates or gaps can appear. Using a monotonic primary key or a combination of columns that together form a unique, immutable key mitigates this.
• Fetch size tuning: a too‑small fetch size increases round‑trips; a too‑large value may consume excess memory. The optimal value depends on the JDBC driver, network latency, and average row size. Monitoring via Hibernate statistics (session.getSessionFactory().getStatistics()) helps verify actual row fetch counts.

Practical verification: enable Hibernate’s statistics (hibernate.generate_statistics=true) and after invoking the method, check statistics.getEntityLoadCount() and statistics.getQueryExecutionCount(). You should see exactly one query execution and the entity load count matching the returned page size (plus any overhead for lazy‑loaded associations).

Actionable closing

If your API suffers from slow deep‑page queries, replace offset‑based pagination with a cursor‑based approach using a stable ordered column and ScrollableResults (or the JPQL hint shown above). Start by measuring the current query execution time and row fetch count, then apply the cursor pattern and re‑measure. The improvement is most noticeable when the table exceeds a few hundred thousand rows and the client frequently requests pages beyond the first few.

---

Note: The code snippets illustrate the pattern; adjust the fetch size, exception handling, and DTO mapping to fit your project’s conventions.