OFFSET/LIMIT versus Keyset Pagination in Hibernate for Stable Ordering and Deep Page Access

Recommendation

Switch to keyset (seek) pagination and redesign the API around cursor-based navigation. It is the only option that simultaneously satisfies your two hard requirements: stable ordering under concurrent writes and low latency at deep positions. Retaining setFirstResult/setMaxResults keeps the page-number UI but fails both of those requirements, so the page selector is the constraint that has to give.

Why OFFSET/LIMIT cannot meet the stated goals

Hibernate translates setFirstResult(n) into SQL with an OFFSET n clause. The database must still read and discard the first n rows, so cost grows linearly with page depth. Worse, a page is defined by position, not by data: if rows are inserted or deleted ahead of the current offset between requests, subsequent pages silently skip or repeat rows. No index or tuning fixes this — it is inherent to positional addressing.

What keyset pagination requires

Instead of skipping rows, each query seeks past the last row of the previous page:

SELECT e FROM Event e
WHERE (e.createdAt, e.id) < :lastCreatedAt, :lastId
ORDER BY e.createdAt DESC, e.id DESC
-- page size via setMaxResults only

Two conditions are non-negotiable:

• Deterministic ordering: the ORDER BY must end with a unique tie-breaker, typically the primary key. Without it, rows sharing a sort value can be skipped or duplicated between pages.
• Supporting index: a composite index on (created_at, id) lets the database satisfy the seek with an index range scan, giving roughly constant time regardless of depth. Without the index, keyset degrades to a scan and loses its advantage.

Handling the page-number requirement

Keyset cannot jump to page 47 without traversing pages 1–46, because cursors are data values, not positions. Your realistic options:

1. Cursor navigation only (next/previous, possibly "jump to date" if the sort column is meaningful) — the cleanest and most common resolution.
2. Hybrid: OFFSET/LIMIT for the first few pages where its cost is trivial, keyset beyond a threshold. This complicates the API and still leaves shallow pages unstable under writes.
3. Cursor mapping table: precompute page-number→cursor mappings; rarely worth the maintenance cost and goes stale under writes.

If the page selector is truly immutable, OFFSET/LIMIT is unavoidable and you should mitigate instead: cap the maximum page, add a covering index, and accept instability — but be explicit that this trades away the stated goals.

Verification before committing

• Enable SQL logging (hibernate.show_sql or a JDBC proxy) and confirm the keyset query emits a WHERE seek plus ORDER BY, with no OFFSET.
• Run EXPLAIN on both queries: OFFSET should show a scan with limit; keyset should show an index range scan on the composite index.
• Benchmark at offsets 0, 10,000, and 100,000: expect linear growth for OFFSET and near-flat timing for keyset.
• Run a background inserter while paging: confirm keyset returns each row exactly once.

Assumption: this reflects well-established behavior in Hibernate 5/6 and mainstream relational databases; row-value comparison syntax ((a, b) > (?, ?)) varies by database, so verify against your dialect — expanded OR conditions are the portable fallback.