JPA collection fetch and pagination: page IDs before loading children

The row multiplication

A receipt with six lines appears six times in a join result before entity deduplication. Applying a page limit directly to a collection fetch can produce incomplete or in-memory pagination behavior, depending on provider settings. It also makes count queries harder. Count joins are a separate concern: count distinct parents if a join multiplies them. A to-one association does not have the same row-multiplication cost.

Two-query page

First select a stable, tenant-scoped page of receipt IDs ordered by created time and ID. Then fetch the selected parents with the required children using those IDs. Reorder results in memory to match the first query because an IN predicate does not preserve order. Keep the selected page small and specify whether concurrent inserts between the two statements are acceptable; stricter snapshot needs an isolation decision. Keyset pagination helps keep deep parent pages stable and avoids large offset scans.

Choose the cheaper read model

If the response needs only child counts or totals, use an aggregate projection rather than loading all line entities. If the client needs full lines for one receipt, expose a separate bounded child query. Record parent count, joined-row count and bytes transferred during verification. The useful performance unit is the whole response, not the apparent number of repository calls.

Implementation contract

@Query("select receipt.id from ReceiptHeader receipt where receipt.tenantId = :tenantId order by receipt.createdAt desc, receipt.id desc")
Slice<UUID> findPageIds(UUID tenantId, Pageable page);

@Query("select distinct receipt from ReceiptHeader receipt left join fetch receipt.lines where receipt.id in :receiptIds")
List<ReceiptHeader> fetchPageWithLines(Collection<UUID> receiptIds);

Cost and verification

The two-query plan adds one round trip and an O(p) in-memory reorder for p parents. It protects page membership, but child-row transfer still grows with the total lines on that page.

Common Mistakes

• Do not apply Pageable directly to an unbounded collection fetch join.
• Do not assume IN returns rows in the order of the ID page.
• Do not load all children when a count projection meets the response contract.

Read next

JPA collection batch fetching: reduce N+1 without a giant join, Spring Data JPA count query for a joined Page, Spring Data JPA Page versus Slice: pay for totals only when needed, Spring Data keyset pagination: continue after the last stable identifier, Spring Data JPA entity graph for a bounded to-one read.