Reflex pagination: migrating from offset to keyset in Hibernate

Problem and takeaway

Offset‑based pagination on large tables incurs increasing latency per page and can return inconsistent row ordering when concurrent writes occur. Because the database must scan and discard rows to reach the offset, a page request for position 100000 on a 10‑million‑row table may take seconds, and inserts or deletes between pages can shift the visible set. The takeaway: adopt keyset (cursor) pagination to achieve constant‑time page retrieval and predictable result ordering.

Desired outcome

Migrate an existing offset‑based pagination implementation to a keyset approach that eliminates OFFSET‑scan penalties while preserving accurate page metadata and entity‑graph fidelity. The new strategy should produce page counts that reconcile with a SELECT COUNT(*) total, and page navigation should remain stable under moderate concurrency.

Necessary prerequisites

• Hibernate 5+ or 6 – the research brief notes that version‑sensitive behavior differs: Hibernate 6 introduces improved cursor streaming for large datasets, reducing memory pressure compared to Hibernate 5’s default list‑loading approach. Choose the version that matches your runtime environment.
• A database table with a stable, indexed primary key (e.g., bigint auto‑increment or timestamp) that can serve as the pagination cursor. The cursor column should ideally be immutable or append‑only to guarantee ordering consistency.
• Read access to the schema and the ability to execute ad‑hoc HQL or SQL queries from a console, test harness, or integrated development environment.
• Aware­ness of the N+1 query risk: if the pagination query fetches a graph without an explicit fetch plan, additional queries may fire for associated entities. Always validate that the pagination query’s fetch profile matches the intended entity graph.

Focused procedure

1. Locate the current pagination query that invokes setFirstResult() and setMaxResults() (or the equivalent SET FIRST/FETCH NEXT HQL syntax). Identify the entity, the ordering column, and the page size parameter.
2. Rewrite the query to replace the OFFSET clause with a WHERE condition on the chosen cursor column. This is the core of the Reflex keyset pattern:

SELECT e
FROM Entity e
ORDER BY e.id ASC
SET FIRST 0 ROWS FETCH NEXT :pageSize ROWS ONLY

SELECT e
FROM Entity e
WHERE e.id > :lastSeenId
ORDER BY e.id ASC
SET MAX :pageSize ROWS ONLY

In the rewritten query, :lastSeenId is bound to the last primary‑key value returned from the previous page. For the first page, bind a value that selects the first row (e.g., 0 or the column’s minimum).

1. Update the repository or data‑access method to accept :lastSeenId as a bind parameter and pass the last observed ID from the preceding page response.
2. If using Hibernate 6, consider enabling cursor‑streaming mode to further reduce memory overhead when processing large result sets.
3. Deploy the change to a staging environment and run the verification steps described below.

Concrete comparison

Aspect	Offset pagination	Keyset (Reflex) pagination
Row location strategy	Counts and skips N rows via OFFSET	Seeks past the last seen primary‑key value
Performance scaling	Latency grows linearly with offset; full table scan or filesort likely	Constant‑time index seek; performance independent of row count
Ordering stability	May shift if concurrent inserts/updates affect the ordering column	Stable when the cursor column is immutable or append‑only
Memory profile	Hibernate materializes the skipped‑plus‑fetched rows into a collection	Hibernate 6 cursor streaming materializes only the current page; Hibernate 5 may load the entire page into memory
COUNT query alignment	Total row count obtained via a separate SELECT COUNT(*) with identical WHERE conditions	Page counts must be mapped manually to cursor boundaries; the sum of page sizes should equal the total count without gaps

Expected checks

1. Count reconciliation: execute the keyset query with :lastSeenId set to the page‑starting boundary and run a SELECT COUNT(*) with the same WHERE conditions. The returned row count should match the COUNT(*) result. Increment :lastSeenId and repeat for each subsequent page; the cumulative total of fetched rows must equal the overall COUNT(*) without gaps or overlaps.
2. Execution‑plan comparison: use the database client’s EXPLAIN (or Hibernate’s format=sql log level) to compare the keyset query’s plan against the original offset query. The keyset plan should show an index seek on the cursor column; the offset plan may reveal a filesort or full table scan.
3. Ordering verification: for three consecutive pages, collect the first and last primary‑key values from each page. Confirm that the sequences are strictly increasing and that no primary‑key value appears in more than one page.

Relevant recovery options

If the keyset migration introduces regressions, revert the query rewrite in the data‑access layer. Because the change is confined to application code and does not alter database schema or data, a simple redeploy or feature‑flag toggle restores the previous setFirstResult()/setMaxResults() behavior without requiring a database rollback. Ensure the feature flag is persisted in a configuration store that survives restarts, and document the rollback procedure in your team’s runbook.

Limitations

Keyset pagination hinges on a monotonically increasing or otherwise deterministic cursor column. It is best suited for append‑only tables or scenarios where the cursor column values are rarely updated. If the application frequently modifies the primary‑key column, or requires arbitrary page jumps (e.g., go to page 100 without sequential traversal), offset pagination—with its known trade‑offs—may be more practical. Additionally, keyset pagination does not support efficient random‑access page numbering; the application must track the cursor value rather than relying on a 1‑based page index.

Practical way to check the result

After deploying the keyset query, validate the implementation with the following pattern in a database client or Hibernate console:

-- 1. Verify first page count matches total
SELECT COUNT(*)
FROM entity
WHERE id > 0;  -- or id >= minimum(id)

-- 2. Fetch first page with keyset
SELECT e.*
FROM entity e
WHERE e.id > 0
ORDER BY e.id ASC
SET MAX 20 ROWS ONLY;  -- adjust page size as needed

-- 3. Verify second page
-- Replace :lastSeenId with the last id from page 1
SELECT COUNT(*)
FROM entity
WHERE id > :lastSeenId;

-- 4. Fetch second page
SELECT e.*
FROM entity e
WHERE e.id > :lastSeenId
ORDER BY e.id ASC
SET MAX 20 ROWS ONLY;

Sum the row counts from each fetched page; the total should equal the initial COUNT(*) result. If discrepancies appear, double‑check that the WHERE conditions are identical and that transactions are properly isolated.