Keyset Pagination in Hibernate: When and How to Use It

The problem: slow scrolling as the offset grows

When a UI shows a list of rows and the user scrolls down, many applications use Hibernate’s setFirstResult() and setMaxResults(). Under the hood this translates to SQL with LIMIT ? OFFSET ?. For small offsets the query is fast, but as the offset climbs the database must read and discard all preceding rows before it can return the page. With a table of a million rows, fetching page 900 (offset ≈ 180 000 with a page size of 200) can cause a noticeable latency spike.

Why keyset pagination helps

Keyset pagination (also called the seek method) avoids the OFFSET clause entirely. Instead, it remembers the last value seen on the previous page and uses it as a filter in the WHERE clause. Because the filter targets an indexed, ordered column, the database can jump directly to the first qualifying row and read only the needed page.

This approach works best when:

• Results are displayed in a deterministic order (e.g., by primary key or timestamp).
• The UI supports “next page” or infinite scroll rather than jumping to an arbitrary page number.
• The dataset is large enough that offset scanning becomes costly.

Implementing keyset pagination in Hibernate

Below is a minimal example using JPQL. Assume an entity Event with a monotonic id column.

// First page (no cursor)
TypedQuery q1 = em.createQuery(
    "SELECT e FROM Event e ORDER BY e.id ASC", Event.class);
q1.setMaxResults(pageSize);
List page1 = q1.getResultList();
Event last = page1.get(page1.size() - 1);

// Subsequent page – pass the last seen id as cursor
TypedQuery q2 = em.createQuery(
    "SELECT e FROM Event e WHERE e.id > :cursor ORDER BY e.id ASC", Event.class);
q2.setParameter("cursor", last.getId());
q2.setMaxResults(pageSize);
List page2 = q2.getResultList();

The generated SQL for the second query looks like:

select ... from Event e0_ where e0_.id>? order by e0_.id asc limit ?

Because id is typically the primary key (and thus indexed), the planner can use an index seek rather than a full table scan.

Trade‑offs and limitations

Keyset pagination is not a drop‑in replacement for every use case:

1. Ordered, non‑null unique column required. If you lack such a column (e.g., you need to sort by a non‑unique timestamp), you must add a tie‑breaker (like the primary key) to keep the ordering strict.
2. No random page jumps. The client cannot directly request page 42 without iterating through all preceding pages (or storing multiple cursors). If your UI must support a “go to page X” control, offset pagination may still be needed.
3. Inserts/deletes can cause duplicates or gaps. If a new row is inserted with an id lower than the cursor, it will appear in a later page; if a row is deleted, the page size may temporarily shrink. Applications usually tolerate this for infinite‑scroll feeds.

Verifying that the database uses an index seek

Enable Hibernate’s SQL logging (e.g., logging.level.org.hibernate.SQL=DEBUG) and run the query with a realistic offset. Then examine the generated SQL with your database’s explain tool:

EXPLAIN ANALYZE
SELECT * FROM event WHERE id > 123456 ORDER BY id ASC LIMIT 200;

Look for an “Index Scan” or “Index Seek” node rather than a “Seq Scan”. If you see a sequential scan, verify that an index exists on the ordered column (id) and that statistics are up‑to‑date.

Actionable takeaway

For large tables where users scroll sequentially, replace setFirstResult()/setMaxResults() with a keyset query that filters on the last seen unique, ordered identifier. Monitor the generated SQL with EXPLAIN to confirm index usage, and be aware that you lose the ability to jump to arbitrary page numbers.