From LIMIT/OFFSET to Keyset: Fast Pagination for Hibernate on Big Tables

When an application needs to list millions of rows, the first few pages feel instant, but the 10th, 100th, or 1,000th page can take seconds or trigger timeouts. The culprit is usually the way Hibernate translates setFirstResult() and setMaxResults() into LIMIT/OFFSET SQL. This article explains why deep offsets are expensive, how cursor‑based (keyset) pagination keeps latency constant, and how fetch strategies can amplify or mitigate the problem.

Why Deep Offsets Break Down

In a typical offset query, the database must read and discard OFFSET rows before it can return the requested LIMIT rows. Even if the table is indexed, the engine still performs a full scan up to the offset point, then an index seek for the final rows. The cost grows linearly with the offset value.

For example, a query that asks for page 500 on a table with 1 M rows and a page size of 20 will cause the engine to read and sort 500 × 20 = 10 000 rows before it returns the 20 rows you actually need. If the table is heavily updated or the index is fragmented, the cost can become a bottleneck.

Keyset (Cursor) Pagination: The Constant‑Time Alternative

Keyset pagination removes the need for a numeric offset by using a stable, indexed column (usually a primary key or timestamp) to mark the starting point of each page. The query uses a WHERE clause that selects rows “after” the last seen value, and the database can jump directly to that point via an index seek.

Because the engine never has to read the skipped rows, the latency stays roughly constant regardless of how deep you paginate.

Sample Code – From Offset to Keyset

Assume we have an entity Order with columns id (PK) and created_at (timestamp). The goal is to list orders sorted by created_at DESC, id DESC.

// Offset‑based (slow for deep pages)
List<Order> list = em.createQuery(
    "SELECT o FROM Order o ORDER BY o.createdAt DESC, o.id DESC", Order.class)
    .setFirstResult(pageNumber * pageSize)
    .setMaxResults(pageSize)
    .getResultList();

// Keyset‑based (fast for any depth)
// The client supplies the last seen created_at and id from the previous page.
Long lastTimestamp = request.getLastTimestamp();
Long lastId = request.getLastId();

List<Order> list = em.createQuery(
    "SELECT o FROM Order o " +
    "WHERE o.createdAt < :lastTimestamp OR " +
    "(o.createdAt = :lastTimestamp AND o.id < :lastId) " +
    "ORDER BY o.createdAt DESC, o.id DESC", Order.class)
    .setParameter("lastTimestamp", lastTimestamp)
    .setParameter("lastId", lastId)
    .setMaxResults(pageSize)
    .getResultList();

Notice the extra id check: it guarantees deterministic ordering when multiple rows share the same timestamp.

Fetch Strategies and the N+1 Problem

Pagination can also interact badly with Hibernate’s fetch mode. Using JOIN FETCH on a collection while paginating the parent entity often forces Hibernate to load the entire collection for every parent, resulting in the dreaded N+1 query pattern.

Three common fetch modes:

• SELECT – default; loads the parent, then lazily loads collections on demand.
• JOIN – eager fetch via JOIN FETCH; can explode when used with pagination.
• SUBSELECT – batch loads collections in a separate subquery; mitigates N+1 but still requires careful tuning.

When using keyset pagination, it is still possible to hit N+1 if you access lazy collections after the page load. The recommended approach is to use @BatchSize or an EntityGraph that fetches only the columns you need.

Practical Check: EXPLAIN on Offset vs Keyset

To verify the performance difference, run the following on your database (replace orders with your table name):

EXPLAIN SELECT * FROM orders
WHERE (created_at < :lastTimestamp OR (created_at = :lastTimestamp AND id < :lastId))
ORDER BY created_at DESC, id DESC
LIMIT :pageSize;

Look for an Index Seek on the composite index (created_at DESC, id DESC). In contrast, the offset query will show a Range Scan or Full Scan up to the offset value.

Benchmark by measuring EXPLAIN ANALYZE output or by timing the query in your application for offsets 0, 1000, and 10000. The keyset query’s runtime should remain flat while the offset query’s runtime will grow.

Trade‑offs and Limitations

• Random Access: Keyset pagination cannot jump to an arbitrary page number. If the UI requires “Go to page 42”, you must first fetch the preceding pages or maintain a mapping from page number to cursor.
• Stable Ordering: The ordering column must be immutable. If created_at changes, the cursor can skip or duplicate rows.
• Complex Sorting: When sorting by a non‑indexed column or a computed value, keyset is not feasible; you may need a hybrid approach.
• Fetch Strategy Overhead: Using JOIN FETCH with pagination still triggers extra joins; prefer EntityGraph or @BatchSize to keep memory usage predictable.

Actionable Checklist

1. Identify the ordering column(s) used for pagination and ensure they are indexed.
2. Replace setFirstResult()/setMaxResults() with a keyset query that takes the last seen values as parameters.
3. Run EXPLAIN on both the old and new queries to confirm an index seek appears.
4. Measure latency for deep offsets (e.g., page 5000) before and after the change.
5. Review fetch strategies: avoid JOIN FETCH on collections when paginating; use @BatchSize or EntityGraph instead.
6. Document the cursor contract (what the client must store and send back) so the UI can maintain state.

By moving away from LIMIT/OFFSET and adopting keyset pagination, you can keep your application responsive even when users scroll through millions of rows. Combine this with careful fetch strategy tuning, and your database will handle deep pagination without the hidden cost of full scans.