Efficiently Paging Large Result Sets with Spring Data JPA: Pageable vs Slice

The Problem: Loading Too Much Data

When a REST endpoint returns a list of entities that can grow to millions of rows, loading the whole result set into memory causes out‑of‑memory errors and sluggish response times. The typical solution is to paginate the data, sending only a small chunk to the client while preserving the ability to navigate forward and backward.

Thesis

Spring Data JPA’s Pageable interface gives you LIMIT/OFFSET pagination out of the box, but it forces a costly count query. When you only need next/previous navigation, replacing Page with Slice eliminates that count and improves throughput, provided you understand the trade‑offs.

Why Pageable Works (and Its Cost)

Spring Data JPA translates a method returning Page<Entity> into SQL that includes LIMIT ? OFFSET ? plus a separate select count(*) to determine the total number of pages. The count query scans the same indexes used for the data query, which can be expensive on large tables.

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

When you call repository.findByCustomerId(42L, PageRequest.of(0, 50)), Hibernate logs something like:

select order0_.id as id1_..., order0_.customer_id as customer2_... from orders order0_ where order0_.customer_id=? limit ? offset ?
select count(*) as col_0_0_ from orders order0_ where order0_.customer_id=?

When Slice Is a Better Fit

If your UI only shows “Next” and “Previous” buttons (no total page count), you can return Slice<Entity>. Spring Data JPA then omits the count query and adds a LIMIT ? OFFSET ? clause plus an extra row to detect whether another page exists.

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

The generated SQL now looks like:

select order0_.id as id1_..., order0_.customer_id as customer2_... from orders order0_ where order0_.customer_id=? limit ? offset ?

Notice the absence of the select count(*) statement.

Worked Example: Switching from Page to Slice

Suppose you have a service that returns paginated orders for a customer controller:

@Service
@RequiredArgsConstructor
public class OrderService {
    private final OrderRepository orderRepository;

    public Slice getOrdersForCustomer(Long customerId, int page, int size) {
        Slice slice = orderRepository.findByCustomerId(customerId, PageRequest.of(page, size));
        return slice.map(order -> new OrderDto(order.getId(), order.getAmount()));
    }
}

The controller simply forwards the slice:

@GetMapping("/customers/{id}/orders")
public ResponseEntity> getOrders(@PathVariable Long id,
                                                 @RequestParam(defaultValue = "0") int page,
                                                 @RequestParam(defaultValue = "20") int size) {
    return ResponseEntity.ok(orderService.getOrdersForCustomer(id, page, size));
}

Trade‑offs and Limitations

• No total count: The client cannot display “X of Y pages” without an extra call.
• OFFSET anomalies: If rows are inserted or deleted between page fetches, you may see duplicates or gaps. This is inherent to OFFSET pagination.
• Page size tuning: Larger pages reduce round‑trips but increase memory usage and JDBC fetch size. Monitor heap and DB CPU when adjusting.
• Index requirement: The columns used in ORDER BY (and any WHERE predicates) must be indexed; otherwise the OFFSET scan becomes slow.

Practical Verification

To confirm that the count query disappears:

1. Enable Hibernate SQL logging in application.properties:

   spring.jpa.show-sql=true
   spring.jpa.properties.hibernate.format_sql=true
   logging.level.org.hibernate.type.descriptor.sql=trace

2. Run the endpoint with a tool like curl or Postman.
3. Check the console/log output: you should see only the select … limit ? offset ? line, no select count(*).

To measure the impact of page size, run a simple load test (e.g., with JMeter) varying the size parameter and record response time and DB CPU usage. Look for the point where increasing size no longer improves latency but starts to raise memory consumption.

Actionable Closing

If your API only needs forward/backward navigation, start by changing the repository return type from Page to Slice. Verify the missing count query in the logs, then tune the page size based on your observed latency and memory usage. Keep an eye on concurrent modifications that could cause OFFSET‑related duplicates, and consider keyset pagination (using a WHERE clause on the last seen value) if you need stronger consistency.