EntityManager fetch reach vs Spring Data JPA Pageable pagination consistency

The Fetch Reach Contract

Fetch reach should be standardized in the repository interface using explicit query definitions rather than remaining ORM-local. Relying on global Hibernate fetch modes (EAGER/LAZY) creates unpredictable behavior when combined with Pageable, as global settings often conflict with the specific data requirements of a paged result set.

Recommended Implementation Strategy

To ensure a predictable entity graph depth, use the following contract patterns:

• Use @EntityGraph: Define the required reach directly on the repository method. This overrides global LAZY settings for that specific call, ensuring associated entities are fetched in a single join without affecting other repository methods.
• Avoid Global EAGER: Global EAGER fetching is incompatible with efficient pagination. Hibernate may struggle to translate LIMIT/OFFSET to SQL when forced to fetch multiple collections, potentially triggering in-memory pagination (HHH000104 warning), which leads to OutOfMemoryError on large datasets.
• Prefer LAZY by Default: Keep ORM-level mappings LAZY and promote specific associations to EAGER only at the repository layer via JOIN FETCH or Entity Graphs.

Count Query Semantics and Consistency

Count queries in Spring Data JPA are designed to calculate the total number of records, not to initialize the entity graph. Therefore, count queries should ignore fetch reach. Attempting to force fetch joins into a count query is logically redundant and degrades performance.

To maintain consistency between the data query and the count query:

1. Ensure that WHERE clause filters are identical in both queries.
2. If using a custom @Query, provide a separate countQuery attribute to ensure the count logic matches the filtered data set, regardless of the fetch depth.

Verification and Safety

To verify that pagination is occurring at the database level and not in-memory, enable SQL logging and check for the presence of LIMIT and OFFSET clauses in the generated SQL. If these are missing despite using a Pageable argument, the ORM is likely performing in-memory pagination due to complex fetch joins.

Diagnostic Requirement: Are you fetching multiple List collections (Cartesian product) within a single paged query? If so, @EntityGraph may still trigger in-memory pagination, and you should switch to @BatchSize or multiple targeted queries.