Optimizing Large Dataset Pagination in Hibernate with Projections and Fetch Strategies

The problem: paging big tables drags down performance

When a REST endpoint needs to return a page of rows from a table with dozens of associations, the naïve approach often looks like this:

@Repository
public interface OrderRepository extends JpaRepository {
    Page findAllByCustomerId(Long customerId, Pageable pageable);
}

Even if the Pageable limits the result to, say, 50 rows, Hibernate will still load the entire Order object graph for each row unless you explicitly control fetching. With default @ManyToOne or @OneToMany associations marked LAZY, accessing any of those fields inside the controller or a DTO mapper triggers additional SELECT statements – the classic N+1 problem. At the same time, forcing EAGER fetches pulls in huge amounts of data you may never need, blowing up memory and increasing query complexity.

Thesis: fetch only the columns you need, per page, using projections and tuned fetch modes

By combining two well‑known techniques you can keep the page query lightweight:

1. Projection interfaces – tell Hibernate to select only the columns required for the view, avoiding the creation of full entity instances.
2. Fetch mode tuning – for the few associations you still need, choose a strategy that loads them in a single extra query per page (e.g., SUBSELECT) instead of per‑row lazy loads.

The result is a page query that touches the database a predictable number of times, returns only the data you will actually serialize, and keeps memory usage proportional to the page size.

Understanding the default behavior

Consider a simplified domain:

@Entity
public class Order {
    @Id
    private Long id;
    private BigDecimal amount;
    
    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer;
    
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    private Set items;
    // getters/setters
}

A typical page request might need the order id, amount, and the customer’s name. If you call repository.findAllByCustomerId(id, Pageable.unpaged()) and then access order.getCustomer().getName() for each element, Hibernate will issue one extra SELECT per order to fetch the customer, plus potentially more for items if they are touched.

Projection‑based page query

Define a projection that matches the DTO you intend to return:

public interface OrderSummary {
    Long getId();
    BigDecimal getAmount();
    String getCustomerName();
}

Then expose it through a Spring Data repository method:

@Repository
public interface OrderRepository extends JpaRepository {
    Page findAllByCustomerId(Long customerId, Pageable pageable);
}

When Spring Data sees the return type Page, it generates a query that selects only the columns mapped by the projection interface. The generated SQL roughly looks like:

select o.id as id, o.amount as amount, c.name as customerName
from orders o
join customers c on o.customer_id = c.id
where o.customer_id = ?
limit ? offset ?

No entity instances are materialized; the result is a list of proxy objects that implement OrderSummary and delegate to the underlying Object[] row.

Tuning fetch strategy for the remaining associations

Sometimes you still need an association that isn’t covered by the projection (e.g., you want to include a flag that indicates whether any OrderItem is backordered). You have two practical options:

• @Fetch(FetchMode.SUBSELECT) on the association – Hibernate will load the associated collection for all rows returned by the page query with a single additional SELECT.
• Fetch profiles – define a profile that switches specific associations to JOIN or SUBSELECT only when the profile is activated (useful for different UI screens).

Example using SUBSELECT:

@Entity
public class Order {
    // ...
    @OneToMany(mappedBy = "order")
    @Fetch(FetchMode.SUBSELECT)
    private Set items;
}

With this setting, after the page query loads the 50 orders, Hibernate issues one extra SELECT to fetch all OrderItem rows whose foreign key matches any of the 50 order ids. This eliminates the per‑row lazy load while still keeping the extra query bounded by the page size.

Worked example: putting it all together

Assume a service method that returns a page of order summaries together with a boolean flag indicating if any item is backordered.

@Service
@RequiredArgsConstructor
public class OrderService {
    private final OrderRepository orderRepository;
    
    public Page getCustomerOrders(Long customerId, Pageable pageable) {
        Page base = orderRepository.findAllByCustomerId(customerId, pageable);
        return base.map(order -> new OrderSummaryWithFlag(
                order.getId(),
                order.getAmount(),
                order.getCustomerName(),
                hasBackorderedItem(order.getId())   // uses a separate query or cached data
        ));
    }
    
    private boolean hasBackorderedItem(Long orderId) {
        // Could be a @Query on OrderItem repository; omitted for brevity
        return orderItemRepository.existsByOrderIdAndBackorderedTrue(orderId);
    }
}

The OrderSummaryWithFlag is a simple DTO (not a projection) that we build after the projection‑based page query. The expensive data retrieval is already optimized; the extra flag check is a lightweight lookup that can be cached or batched further if needed.

Verifying the effect

To confirm that your fetch strategy is behaving as expected, enable Hibernate statistics in your test or development environment:

hibernate.statistics.enabled=true
hibernate.generate_statistics=true

After exercising the endpoint, inspect the statistics via JMX or by injecting Statistics into a bean:

@Autowired
private Statistics stats;

public void logFetchCounts() {
    System.out.println("Entity load count: " + stats.getEntityLoadCount());
    System.out.println("Collection load count: " + stats.getCollectionLoadCount());
    System.out.println("Query execution count: " + stats.getQueryExecutionCount());
}

You should see the entity load count roughly equal to the page size (orders) and the collection load count close to 1 (the subselect for items) rather than page size × (average items per order). If the collection load count remains high, the fetch mode is not being applied – double‑check that the annotation is on the correct field and that the entity is not being proxied away by another framework.

Trade‑offs and limitations

While projection‑based paging solves many performance problems, it introduces a few considerations:

• Loss of lazy loading transparency: fields not included in the projection are simply unavailable; any code that expects to navigate the full entity will receive null or throw an exception.
• DTO mapping overhead: you still need to convert projection instances (or the Object[] rows) into the final JSON representation, which adds a small CPU cost.
• Version‑specific features: @Fetch(FetchMode.SUBSELECT) and fetch profiles are available from Hibernate 5.2 onward. Older releases may ignore the annotation or require a custom Loader.
• Complex queries: when the projection includes columns from multiple joined tables, the generated SQL can become large; ensure your database has appropriate indexes on the join and filter predicates.

Practical way to check the result: compare the response time and memory usage of the projection‑based endpoint against a baseline that returns full entities. Use a load‑testing tool (e.g., JMeter) with a realistic dataset and observe that the 95th‑percentile latency drops and heap allocation per request scales linearly with page size rather than with the total size of the associated graphs.

Actionable closing

1. Identify the fields actually needed for each list view.
2. Create a projection interface that mirrors those fields.
3. Replace the repository method returning Page<Entity> with one returning Page<YourProjection>.
4. For associations still required, annotate them with @Fetch(FetchMode.SUBSELECT) or activate a fetch profile.
5. Enable Hibernate statistics in a staging environment and verify that entity and collection loads match expectations.
6. Measure latency and memory under load; adjust page size or fetch mode if the trade‑off shifts.

By following these steps you keep the database round‑trips predictable, limit the data transferred to what the client truly consumes, and retain control over when extra associations are fetched.