Offset-based paging gets slower and less stable the deeper a user scrolls through a large sorted result set. How would you express keyset (seek) pagination in JPQL instead, and what does it give up?

seniorshould knowfreq 38%

answer

carry the last row's key, not an offset
(sortCol, id) < (:c, :i) row-value comparison
setMaxResults only, no setFirstResult
unique tie-breaker + matching index
no jump-to-page, no cheap totals

basics

~20 s

Instead of an offset, carry the last row's sort key and ask for rows after it: 'where (e.createdAt, e.id) < (:lastCreatedAt, :lastId) order by e.createdAt desc, e.id desc' with setMaxResults. Constant cost and stable pages, but no jumping to page N.

solid answer

~60 s

Keyset paging replaces "skip 200,000 rows" with "start after this row". The client sends back the sort key of the last row it saw, and the query becomes a range predicate plus setMaxResults — setFirstResult disappears entirely:

select o from Order o
where (o.createdAt, o.id) < (:lastCreatedAt, :lastId)
order by o.createdAt desc, o.id desc

Hibernate 6 supports that tuple comparison in HQL where the dialect supports row values; otherwise you expand it manually to createdAt < :c or (createdAt = :c and id < :i). The sort must be total — a unique tie-breaker column is mandatory — and it should match an index for the seek to be a true index range scan.

What you give up: random access (no "jump to page 500"), cheap total counts and page numbers, and simple ORDER BY changes — each sort order needs its own cursor shape. You also have to carry cursor state through the API, ideally opaque and encoded.

What you gain: cost independent of depth, and no duplicated or skipped rows when data is inserted while a user scrolls.

Keyset paging replaces "skip 200,000 rows" with "start after this row". The client sends back the sort key of the last row it saw, and the query becomes a range predicate plus `setMaxResults` — `setFirstResult` disappears entirely: ``` select o from Order o where (o.createdAt, o.id) < (:lastCreatedAt, :lastId) order by o.createdAt desc, o.id desc ``` Hibernate 6 supports that tuple comparison in HQL where the dialect supports row values; otherwise you expand it manually to `createdAt < :c or (createdAt = :c and id < :i)`. The sort must be **total** — a unique tie-breaker column is mandatory — and it should match an index for the seek to be a true index range scan. What you give up: random access (no "jump to page 500"), cheap total counts and page numbers, and simple ORDER BY changes — each sort order needs its own cursor shape. You also have to carry cursor state through the API, ideally opaque and encoded. What you gain: cost independent of depth, and no duplicated or skipped rows when data is inserted while a user scrolls.

code

java · 11 lines

java

String hql = "select o from Order o where o.status = :st "
           + (cursor == null ? "" : "and (o.createdAt, o.id) < (:c, :i) ")
           + "order by o.createdAt desc, o.id desc";

TypedQuery<Order> q = em.createQuery(hql, Order.class)
    .setParameter("st", Status.OPEN)
    .setMaxResults(pageSize);      // no setFirstResult
if (cursor != null) {
    q.setParameter("c", cursor.createdAt()).setParameter("i", cursor.id());
}
List<Order> page = q.getResultList();

go deeper

for a junior

Know that offset paging exists and that deep offsets are slow; recognising that a 'last seen id' approach is the alternative is enough.

for a middle

Write the seek predicate correctly as a tuple comparison with a unique tie-breaker, and explain why setFirstResult disappears.

for a senior

Cover the trade matrix — depth-independent cost and stability against loss of random access, totals and multi-sort flexibility — plus index requirements, nullable sort columns and cursor encoding.

for a principal

Decide the paging contract for the whole API: which surfaces get cursors, how cursors are encoded and versioned, what stability guarantee is promised, and what happens when clients need totals anyway.

What is wrong with offsets

limit 20 offset 200000 asks the database to produce the ordered result and throw away the first 200,000 rows. Two consequences:

Cost grows with depth. Page 1 is instant; page 10,000 reads 200,020 rows to return 20. Latency is proportional to the offset, not the page size.
Pages are unstable. The offset is relative to a result set that changes. If five rows are inserted before your position between two requests, five rows you already saw shift onto the next page and you see them twice; deletions make you skip rows entirely.

The seek predicate

Keyset paging anchors on data instead of position. The client keeps the sort key of the last row it received and passes it back; the next query asks for rows strictly after it in the ordering.

For a descending sort:

select o from Order o
where o.status = :st
  and (o.createdAt, o.id) < (:lastCreatedAt, :lastId)
order by o.createdAt desc, o.id desc

with setMaxResults(pageSize) and no setFirstResult. The first page simply omits the seek predicate.

The comparison must be a row-value (tuple) comparison, not a conjunction of per-column comparisons. createdAt <= :c and id < :i is wrong: it drops rows that have an older createdAt but a larger id. Written correctly by hand the equivalent is:

where o.createdAt < :c or (o.createdAt = :c and o.id < :i)

Hibernate 6's HQL parses (a, b) < (:a, :b) and renders a native row-value comparison on dialects that support it (PostgreSQL, MySQL 8, Oracle), emulating it as the OR form elsewhere. Row values are usually faster because the optimiser can turn them into a single index range scan; the OR form sometimes cannot.

Requirements

A total ordering. Every keyset needs a unique final component, normally the primary key. Without it, ties at the page boundary are silently dropped or repeated.
A matching index. The seek is only cheap if an index exists on the exact ordered tuple in the exact direction ((created_at desc, id desc) or a compatible one). The ORM-side obligation is to make sure the mapping's sort columns are ones you can index; whether the engine picks the index is a database concern.
Nullable sort columns are trouble. NULL comparisons in the seek predicate are neither true nor false, so nulls break the seek. Sort on non-nullable columns or normalise with a sentinel.
Descending and ascending must be consistent across all components of the tuple; mixed directions cannot be expressed as one row-value comparison and must be expanded manually.

What you give up

No random page access. You can go forward (and backward, with a mirrored predicate), but you cannot jump to page 500 — there is no key for it. That kills numbered pagers.
Totals are still expensive, and page numbers are meaningless, so the UI usually becomes infinite scroll or "next/previous".
One cursor shape per sort order. Letting users sort by five different columns means five seek predicates and a cursor that encodes which one is in use.
Cursor plumbing. The last-row key has to travel to the client and back. Encode it as an opaque token (e.g. base64 of the tuple plus the sort identifier) so clients cannot craft arbitrary values, and validate it server-side — it goes straight into query parameters, so it must be bound, never concatenated.
Deleted anchors. If the row the cursor points at is deleted, the seek still works — it is a range predicate, not a lookup — which is a nice property, but it also means a cursor can silently outlive its data.

What you gain

Depth-independent latency. Every page costs the same: one index seek plus n rows.
Stability under concurrent inserts. New rows appear where they belong in the ordering; already-seen rows never reappear. This is the property that matters for feeds and for exports that must not duplicate records.
Resumable iteration. A batch job can stop and resume from the last key without re-reading.

Choosing between them

Offset paging is fine when result sets are small, users rarely go past a few pages, and the UI wants page numbers — administrative tables, search results with heavy filtering. Keyset is the answer for feeds, infinite scroll, exports and any iteration over a large table. A common compromise is offset for the first k pages (so numbered pagers still work) and keyset beyond that, or keyset everywhere with a separately computed, cached total.

## What is wrong with offsets `limit 20 offset 200000` asks the database to produce the ordered result and throw away the first 200,000 rows. Two consequences: 1. **Cost grows with depth.** Page 1 is instant; page 10,000 reads 200,020 rows to return 20. Latency is proportional to the offset, not the page size. 2. **Pages are unstable.** The offset is relative to a result set that changes. If five rows are inserted before your position between two requests, five rows you already saw shift onto the next page and you see them twice; deletions make you skip rows entirely. ## The seek predicate Keyset paging anchors on data instead of position. The client keeps the sort key of the last row it received and passes it back; the next query asks for rows strictly after it in the ordering. For a descending sort: ``` select o from Order o where o.status = :st and (o.createdAt, o.id) < (:lastCreatedAt, :lastId) order by o.createdAt desc, o.id desc ``` with `setMaxResults(pageSize)` and **no** `setFirstResult`. The first page simply omits the seek predicate. The comparison must be a **row-value (tuple) comparison**, not a conjunction of per-column comparisons. `createdAt <= :c and id < :i` is wrong: it drops rows that have an older `createdAt` but a larger id. Written correctly by hand the equivalent is: ``` where o.createdAt < :c or (o.createdAt = :c and o.id < :i) ``` Hibernate 6's HQL parses `(a, b) < (:a, :b)` and renders a native row-value comparison on dialects that support it (PostgreSQL, MySQL 8, Oracle), emulating it as the OR form elsewhere. Row values are usually faster because the optimiser can turn them into a single index range scan; the OR form sometimes cannot. ## Requirements - **A total ordering.** Every keyset needs a unique final component, normally the primary key. Without it, ties at the page boundary are silently dropped or repeated. - **A matching index.** The seek is only cheap if an index exists on the exact ordered tuple in the exact direction (`(created_at desc, id desc)` or a compatible one). The ORM-side obligation is to make sure the mapping's sort columns are ones you can index; whether the engine picks the index is a database concern. - **Nullable sort columns are trouble.** `NULL` comparisons in the seek predicate are neither true nor false, so nulls break the seek. Sort on non-nullable columns or normalise with a sentinel. - **Descending and ascending must be consistent** across all components of the tuple; mixed directions cannot be expressed as one row-value comparison and must be expanded manually. ## What you give up - **No random page access.** You can go forward (and backward, with a mirrored predicate), but you cannot jump to page 500 — there is no key for it. That kills numbered pagers. - **Totals are still expensive**, and page numbers are meaningless, so the UI usually becomes infinite scroll or "next/previous". - **One cursor shape per sort order.** Letting users sort by five different columns means five seek predicates and a cursor that encodes which one is in use. - **Cursor plumbing.** The last-row key has to travel to the client and back. Encode it as an opaque token (e.g. base64 of the tuple plus the sort identifier) so clients cannot craft arbitrary values, and validate it server-side — it goes straight into query parameters, so it must be bound, never concatenated. - **Deleted anchors.** If the row the cursor points at is deleted, the seek still works — it is a range predicate, not a lookup — which is a nice property, but it also means a cursor can silently outlive its data. ## What you gain - **Depth-independent latency.** Every page costs the same: one index seek plus *n* rows. - **Stability under concurrent inserts.** New rows appear where they belong in the ordering; already-seen rows never reappear. This is the property that matters for feeds and for exports that must not duplicate records. - **Resumable iteration.** A batch job can stop and resume from the last key without re-reading. ## Choosing between them Offset paging is fine when result sets are small, users rarely go past a few pages, and the UI wants page numbers — administrative tables, search results with heavy filtering. Keyset is the answer for feeds, infinite scroll, exports and any iteration over a large table. A common compromise is offset for the first *k* pages (so numbered pagers still work) and keyset beyond that, or keyset everywhere with a separately computed, cached total.

Why is 'where createdAt <= :c and id < :i' not a correct seek predicate?
It applies the id comparison to every row, not just to rows tied on createdAt, so it wrongly excludes older rows whose id happens to be larger than the anchor's. The correct form compares the tuple: createdAt < :c OR (createdAt = :c AND id < :i), which is what a row-value comparison expresses. Getting this wrong silently loses rows, which is far worse than being slow.
How would you support a backwards 'previous page' with keyset paging?
Mirror the predicate and the ordering: use the first row of the current page as the anchor, flip the comparison to '>' and the ORDER BY to ascending, take pageSize rows, then reverse the list in memory before returning it. The cursor token therefore needs to record which end of the page it came from so the server knows which direction to seek.

saying these in an interview costs you the question

Comparing sort columns independently instead of as a tuple, silently dropping rows
Omitting the unique tie-breaker and losing or repeating rows at page boundaries
Claiming keyset paging removes the need for an index
Promising 'jump to page N' with a cursor-based API
Concatenating cursor values into the query string instead of binding them

Offset-based paging gets slower and less stable the deeper a user scrolls through a large sorted result set. How would you express keyset (seek) pagination in JPQL instead, and what does it give up?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What is wrong with offsets

The seek predicate

Requirements

What you give up

What you gain

Choosing between them

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics3 topics

What is wrong with offsets

The seek predicate

Requirements

What you give up

What you gain

Choosing between them

answer

go deeper

context