Stop Using setFirstResult for Large Tables: Cursor Pagination in Hibernate

The slow page 10,000 problem

If your Hibernate application pages through a large table with setFirstResult(offset) and setMaxResults(limit), you've probably noticed that early pages are fast and later pages crawl. That's not your imagination. Offset pagination asks the database to count and discard thousands of rows before returning the ones you want, so page 10,000 costs roughly 10,000 times the row-skipping work of page 1. The latency grows linearly with the offset, and on unindexed sort columns it can be worse.

The fix is cursor-based pagination (sometimes called keyset pagination): instead of saying "skip 100,000 rows," you say "give me rows after the last ID I saw." The database uses an index seek to jump straight to that point, so every page costs about the same regardless of how deep you are.

How the cursor approach works

The idea rests on one requirement: a unique, monotonically increasing column to paginate on — usually the primary key. Each page query carries the last identifier from the previous page as a parameter:

// Runs in your service/repository layer; needs a read transaction.
// pageSize: e.g. 100. lastId: 0 (or null) for the first page.
List<Order> page = session.createQuery(
        "select o from Order o where o.id > :lastId order by o.id asc",
        Order.class)
    .setParameter("lastId", lastId)
    .setMaxResults(pageSize)
    .getResultList();

Long nextCursor = page.isEmpty()
        ? null
        : page.get(page.size() - 1).getId();

Because id is the primary key, the WHERE id > :lastId predicate resolves to an index range scan starting exactly at the boundary — no skipped rows. Your client or loop passes nextCursor into the next call until a page comes back short or empty.

Two traps that quietly erase the gains

Accidental joins and N+1 loads. If Order has lazily fetched associations and your code touches them while iterating, Hibernate issues extra queries per row — the pagination is fast but the overall traversal isn't. For bulk reads, select only what you need with an explicit projection (a constructor expression or a tuple query), or fetch associations in batches with hibernate.default_batch_fetch_size.

An unstable cursor. If you paginate on a non-unique column like created_at, rows sharing the same timestamp can be skipped or duplicated between pages. Tie-break with the primary key: WHERE (createdAt, id) > (:lastTs, :lastId) (row-value syntax works on PostgreSQL and MySQL; on older databases, expand it to an OR of two comparisons). Also note that rows inserted behind the cursor during traversal are simply never seen — fine for exports, wrong for "show me everything new."

Verifying it actually helped

Two quick checks, both runnable in a staging environment with realistic data volume:

1. Correctness: insert rows with known IDs (say 1–1000), page with size 100, and assert each page's first ID equals the previous page's last ID plus the expected gap, with no duplicates across pages.
2. Performance: compare execution time of the offset query versus the cursor query at a deep page (e.g., offset 500,000). Then inspect the query plan — with EXPLAIN on PostgreSQL/MySQL, the cursor query should show an index range scan/seek on the primary key, not a full scan with a large rows-examined count. Exact timings depend on your schema and indexes, so measure rather than trust any published numbers.

The trade-off you accept

Cursor pagination gives up random access: there's no "jump to page 47," because page 47 only exists relative to page 46. It's also tied to a fixed sort order on the cursor column. That's why it shines for infinite scroll, data exports, batch jobs, and API list endpoints — and why a traditional admin grid with numbered pages may legitimately keep offsets.

The actionable step: find the deepest offset query in your slow-query log, confirm it sorts on an indexed unique column, and convert it to a WHERE id > :cursor predicate. It's usually a ten-line change with a measurable payoff.