Why Your Hibernate Harvester Slows Down at Page 500 — and How Keyset Pagination Fixes It

Your nightly harvest job pulls a few million rows through Hibernate, paged 500 at a time. The first hundred pages fly. By page 500, each query takes seconds. By page 2,000, the job misses its window. Nothing is "broken" — you are paying the offset tax, and it grows with every page.

The fix is not a bigger heap or a faster database. It is changing how you ask for the next page: stop saying "skip the first 250,000 rows" and start saying "give me rows after the last one I saw." That is keyset (cursor-based) pagination, and for sequential harvest workloads it is almost always the right call.

What OFFSET actually costs

SELECT ... ORDER BY id LIMIT 500 OFFSET 250000 does not jump to row 250,001. The database must locate, count, and discard 250,000 rows before it can return the 500 you want. In Hibernate, setFirstResult() combined with setMaxResults() generates exactly this kind of query. setMaxResults caps the page size just fine — it is setFirstResult that degrades, roughly linearly (or worse, once buffers spill) as the offset climbs.

For an interactive UI where a user occasionally clicks page 3, this is irrelevant. For a harvester that walks an entire table, it is the difference between a 20-minute job and a 6-hour one.

Keyset pagination: carry a discriminator, not an offset

Instead of a page number, each query carries the value of the sort column from the last row of the previous page. The query becomes a range filter, which the database resolves with an index seek instead of a scan-and-discard:

// Runs inside your harvest loop, in a short read transaction per batch.
// Requires a unique, indexed, monotonically sortable column (id works well).
List<Customer> batch = session.createQuery(
        "select c from Customer c where c.id > :lastId order by c.id asc",
        Customer.class)
    .setParameter("lastId", lastSeenId)   // 0L for the first batch
    .setMaxResults(batchSize)             // e.g. 500
    .getResultList();

if (batch.isEmpty()) break;               // harvest complete
process(batch);
lastSeenId = batch.get(batch.size() - 1).getId();
session.clear();                          // detach the batch, keep memory flat

Every page now costs the same as page one. Two requirements make this safe: the discriminator column must be unique (otherwise rows can be skipped or duplicated at page boundaries), and the query must ORDER BY that same column. If you sort by a non-unique column like created_at, add the primary key as a tiebreaker and filter on the pair.

Where scroll() fits — and where it doesn't

Hibernate's scroll() (via ScrollableResults) is the other streaming option: a forward-only cursor over the full result set, so the database does the pagination for you. It keeps memory flat without re-querying, which is attractive for a single-pass export.

The trade-offs are real, though. The cursor lives inside one transaction, so a multi-hour harvest holds a long transaction — with the lock, undo-log, and snapshot-isolation baggage that implies. There is no random access and no going backward, so a failed batch cannot simply be re-fetched; you restart or checkpoint yourself. And because everything happens in one persistence context, you must still evict entities (session.evict or periodic clear()) or the first-level cache grows until the JVM falls over. For resumable, checkpoint-friendly harvests, keyset batches with short transactions are easier to operate; scroll() suits bounded, single-pass jobs.

Don't let the fetch plan eat your gains

Pagination fixes the row-scanning problem, but a harvester that touches associations can still drown in N+1 queries. If each Customer lazily loads its orders, a 500-row batch fires 500 extra queries. Two knobs help:

• hibernate.fetch.size (or @BatchSize) batches lazy collection loads, turning 500 queries into a handful of IN queries.
• FetchMode.SUBSELECT loads a collection for the whole batch in one secondary query — often the cleanest fit for harvesting entity graphs.

Enable hibernate.generate_statistics (or a datasource proxy) in a staging run and count statements per batch. If the count scales with batch size, your fetch plan — not your pagination — is now the bottleneck.

Verify before you trust it

The offset-decay claim is easy to confirm on your own schema: run the same query with offsets of 0, 10,000, 100,000, and 1,000,000 against a production-sized copy and log latency. You should see the inflection point yourself. Then run the keyset version over the same range and confirm flat timing plus identical row counts. For correctness, assert that the union of all batches contains every expected primary key exactly once — that catches non-unique discriminator bugs immediately. These numbers are workload-dependent; treat any benchmark you didn't run on your data as a hypothesis.

The practical takeaway

If your harvester reads a table sequentially, replace setFirstResult with a keyset filter on an indexed unique column, keep transactions short, clear the session between batches, and batch your lazy fetches. Reach for scroll() only when a single long-lived pass is acceptable. Measure the query count and latency per batch in staging — that five-minute check tells you which bottleneck you actually have.