Stop Loading Whole Tables: Pagination Trade-offs in Spring Data JPA

When findAll() quietly loads the whole table

A repository method that returns List<Order> findAll() looks harmless in a test database with fifty rows. In production, the same call pulls every matching row into the JVM heap and serializes all of them into the HTTP response. As the table grows, latency climbs and the application eventually risks an OutOfMemoryError. Pagination bounds both the network payload and heap usage, but the default Spring Data JPA path carries costs that are easy to miss.

Wiring Pageable into a repository and controller

Spring Data JPA exposes pagination through the Pageable interface and the Page return type. Add a Pageable parameter to a repository method and Spring translates it into a SQL LIMIT/OFFSET clause automatically—no manual query construction required.

public interface OrderRepository extends JpaRepository<Order, Long> {
    Page<Order> findByStatus(String status, Pageable pageable);
}

With Spring Data web support on the classpath, a controller can bind Pageable directly from query-string parameters:

@GetMapping("/orders")
public Page<Order> listOrders(@RequestParam String status, Pageable pageable) {
    return orderRepository.findByStatus(status, pageable);
}

A client can then request:

GET /orders?status=SHIPPED&page=0&size=20&sort=createdAt,desc

The response wraps the rows in a Page object that carries content plus metadata such as total elements and total pages. Be aware that default page sizes and the exact parameter names for capping maximums vary between Spring Data releases; confirm the behavior against the version you run.

The hidden count query behind Page

To populate totalElements and totalPages, Spring Data issues a second SQL statement: a COUNT query over the same filter. On small tables this is invisible. On very large tables the count can dominate response time even when the page fetch itself is fast, because the database must still evaluate the full result set to compute the total.

If your UI only needs to know whether a next page exists, return a Slice instead. A Slice skips the count query entirely and reports hasNext(), trading total-page metadata for cheaper queries.

Return type	Count query	Metadata	Best for
Page	Yes	Total elements, total pages	Paginated UIs with page numbers
Slice	No	hasNext only	Infinite scroll, "load more" buttons

Sorting, offsets, and when pagination stops scaling

The sort parameter composes with pagination, but multi-column sorts on non-indexed columns degrade database performance even when the page size is small. Sorting by an association or a computed property can also generate unexpected joins or force the database to sort rows it has not yet limited. Inspect the generated SQL before trusting a sort parameter in production.

Deep pagination is the sharper edge. A request for page=10000&size=50 asks the database to scan and discard 500,000 rows before returning the fifty you want. The offset cost grows linearly. For infinite-scroll feeds or bulk exports, keyset (cursor) pagination—filtering with WHERE id > :lastSeenId ORDER BY id LIMIT :size—avoids the scan entirely, though it requires a stable sort key and does not support arbitrary page jumps.

A practical way to check your setup

You can verify the behavior without guessing. In a Spring Boot application, enable SQL logging:

# application.properties
spring.jpa.show-sql=true
logging.level.org.hibernate.SQL=DEBUG

Then call a paged endpoint and look for two statements: one with limit ? offset ? and one select count(...). Compare that against an unpaged findAll() call, which should show a single unrestricted select. For a deep page, run EXPLAIN on the generated SQL in your database client to see how many rows the engine scans before applying the offset.

Finally, cap the page size. If you leave the default unbounded, a client can request size=1000000 and undo your pagination work. Depending on your Spring Data version, you can configure a maximum through the web-support resolver or validate the Pageable argument explicitly in your controller.

Takeaway

• Use Pageable plus Page or Slice instead of unbounded List returns.
• Prefer Slice when you do not need total counts.
• Profile the count query on large tables; it may be the real bottleneck.
• Switch to keyset pagination for deep offsets, and enforce a maximum page size.