Hibernate ORM pagination with PostgreSQL: ensuring idempotent retries for offset-based queries

Answer

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.

Confirmed facts

• Hibernate translates setFirstResult and setMaxResults to database-specific LIMIT/OFFSET. The rows returned depend on the ORDER BY and on the snapshot visible at execution time.
• PostgreSQL REPEATABLE READ provides a stable snapshot for the duration of one transaction. Re-executing the same offset query inside that transaction is stable.
• A retry after a failure starts a new transaction with a new snapshot. Stability is lost across retries.
• Offset pagination is inherently non-idempotent under concurrent writes. Inserts or deletes before the offset shift logical rows, causing duplicates or gaps when the same offset/limit is re-executed, even with a deterministic ORDER BY.

Likely explanation

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.

Minimal steps for this case

1. Verify the pagination 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.
2. Accept the limitation: with offset + retry in a new transaction, duplicates or gaps are possible under concurrent writes. Do not rely on REPEATABLE READ to fix cross-retry idempotence.
3. If true idempotence is required, change the paging contract to keyset pagination using the last seen sort key and id. Keep offset only for the first page.
4. If you must keep offset semantics, retry must happen inside the same database transaction that produced the first attempt. That is generally impractical for HTTP requests and increases lock and resource pressure.

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.