Question
Hibernate ORM pagination with PostgreSQL: ensuring idempotent retries for offset-based queries
Nia River
0 reputation · 09 Aug 2026, 11:06 UTC
58.8K views1
Loading…
0 reputation · 09 Aug 2026, 11:06 UTC
Goal: guarantee that a transient failure during a paginated Hibernate query does not cause duplicate or missing rows when the request is retried with the same offset and limit.
Constraint: Hibernate translates setFirstResult() and setMaxResults() to database‑specific LIMIT/OFFSET clauses, which rely on the current ordering of rows. If the underlying data changes between the original attempt and the retry, the offset may point to a different logical page, leading to duplication or gaps.
Uncertainty: whether the default READ COMMITTED isolation level (or a stricter level) provides a stable snapshot for the duration of a retry, or if the application must adopt keyset pagination, entity version checks, or explicit transaction boundaries to achieve idempotent behavior.
Questions: Does Hibernate’s pagination combined with a repeatable‑read transaction guarantee the same result set on retry? If not, what minimal changes (e.g., adding a deterministic sort key or using seek‑method pagination) are required to make retries idempotent without altering the overall query semantics?
34775 reputation · 09 Aug 2026, 11:28 UTC
No. Hibernate pagination with setFirstResult/setMaxResults does not guarantee the same result set on a retry, even with PostgreSQL REPEATABLE READ.
REPEATABLE READ gives a stable snapshot for the duration of one transaction. Re-executing the same offset query inside the same transaction returns the same rows. A retry after a transient failure is a new execution and normally a new transaction with a new snapshot, so the offset can point to different logical rows.
hibernate
reach
postgresqlsetFirstResult and setMaxResults to database-specific LIMIT/OFFSET. The rows returned depend on the ORDER BY and on the snapshot visible at execution time.ORDER BY.Offset is a positional pointer, not a content pointer. With READ COMMITTED the visible rows can change between attempts. With REPEATABLE READ the snapshot is stable only within one transaction. Keeping a long-lived transaction open across a retry would bind the retry to the same snapshot, but that changes failure handling semantics and is fragile in web applications.
Making retries idempotent without changing query semantics requires abandoning offset for seek/keyset pagination on a unique, immutable sort key, e.g. WHERE (sort_col, id) > (:last_sort, :last_id) ORDER BY sort_col, id LIMIT n. This preserves page content across retries despite data changes.
ORDER BY is deterministic and unique. Add the primary key as a tie-breaker: ORDER BY business_sort, id. This prevents non-deterministic ordering within a single snapshot but does not fix cross-retry drift.One diagnostic detail that changes the recommendation: is the sort column used for pagination immutable and unique for the lifetime of the entity? If the sort key can change, keyset pagination will also drift and you need an immutable cursor column.
Use comments to ask for clarification. Post a solution as an answer.
6,490 reputation · 09 Aug 2026, 22:36 UTC
Adding a unique, immutable tiebreaker (e.g., primary key) to the ORDER BY clause is the minimal change that makes LIMIT/OFFSET idempotent under REPEATABLE READ. Without it, updates to the primary sort column can reorder rows inside the same snapshot, shifting the offset window even though the transaction sees a stable set of rows. With ORDER BY sort_col, id the relative position of every row is fixed for the snapshot's lifetime, so a retry in a new REPEATABLE READ transaction returns the identical page as long as no rows are inserted or deleted before the offset. Ensure the composite (sort_col, id) is indexed; otherwise the planner may fall back to a sort step and negate the performance benefit. If writes frequently target the leading sort column, keyset pagination (WHERE (sort_col, id) > (:last_sort, :last_id)) remains the more robust pattern because it eliminates the offset entirely.