Spring Data JPA Pagination & Sorting: How Pageable & PageRequest Shape Database Performance

Problem: Growing Data, Slow Queries

When an application’s tables grow beyond a few thousand rows, fetching all rows for a list view becomes a bottleneck. A naive SELECT * FROM orders will return a huge result set, consuming memory and network bandwidth. Developers need a way to ask the database for only the data they actually want to display, and to order that data reliably.

Thesis: Use Pageable & PageRequest, but be mindful of the hidden cost of count queries and offset scans.

Spring Data JPA offers the Pageable abstraction and the concrete PageRequest factory to express pagination and sorting in a clean, type‑safe way. Returning a Page<T> gives you both the data slice and metadata such as total elements, but it forces an extra COUNT(*) query. If you don’t need the total, Slice<T> is a lighter alternative. Understanding these trade‑offs lets you design APIs that scale.

1. Pagination Basics with Pageable

The Pageable interface represents a request to a repository for a specific page of data. It contains three pieces of information:

• Page index – zero‑based number of the requested page.
• Page size – how many records per page.
• Sort – optional ordering of the results.

Spring Data automatically translates a Pageable into the database’s LIMIT and OFFSET clauses. The generated SQL looks like:

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 40;

Where LIMIT 20 is the page size and OFFSET 40 skips the first 40 rows (2 pages of 20).

2. Sorting with Sort

Sorting is expressed via the Sort class. It supports multiple properties and directions:

Sort sort = Sort.by(Sort.Order.desc("createdAt"), Sort.Order.asc("customerName"));

When you build a PageRequest, you can embed that sort:

PageRequest pageRequest = PageRequest.of(page, size, sort);

Because the sort is part of the Pageable, the repository method can look simple:

@Repository
public interface OrderRepository extends PagingAndSortingRepository {
    Page findByStatus(String status, Pageable pageable);
}

Calling findByStatus("PAID", pageRequest) returns a Page<Order> that includes the sorted subset.

3. Performance Trade‑offs: Page vs Slice

When you request a Page<T>, Spring Data JPA will issue two queries:

1. SELECT the page rows (LIMIT/OFFSET).
2. SELECT COUNT(*) for the total number of matching rows.

The count query can be expensive on large tables or complex joins. If the client only needs to know whether a next page exists (e.g., infinite scroll), you can return a Slice<T> instead. A Slice runs the same data query but adds a LIMIT + 1 to determine if more data is available, avoiding the count.

Choosing Slice is a practical engineering decision when:

• The UI only needs to know if more items exist.
• Table size is in the millions and a full count would hit the database budget.

However, if pagination controls need total pages or total elements, you must use Page and accept the count cost.

4. Concrete Example: Orders API

Below is a minimal Spring Boot example that demonstrates the key steps. Replace placeholders with your actual entity and repository names.

// Order.java – JPA entity
@Entity
public class Order {
    @Id @GeneratedValue
    private Long id;
    private String status;
    private LocalDateTime createdAt;
    // getters/setters omitted for brevity
}

// OrderRepository.java
@Repository
public interface OrderRepository extends PagingAndSortingRepository {
    Page<Order> findByStatus(String status, Pageable pageable);
}

// OrderService.java
@Service
public class OrderService {
    @Autowired
    private OrderRepository repo;

    public Page<Order> getOrders(String status, int page, int size, String sortBy) {
        Sort sort = Sort.by(Sort.Direction.DESC, sortBy);
        Pageable pageable = PageRequest.of(page, size, sort);
        return repo.findByStatus(status, pageable);
    }
}

// OrderController.java
@RestController
@RequestMapping("/api/orders")
public class OrderController {
    @Autowired
    private OrderService service;

    @GetMapping
    public Page<Order> listOrders(
            @RequestParam String status,
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "20") int size,
            @RequestParam(defaultValue = "createdAt") String sortBy) {
        return service.getOrders(status, page, size, sortBy);
    }
}

**Where to run this**: Deploy the application in a Spring Boot environment (JVM, appropriate DB driver). No special permissions beyond standard database read access are required.

**Verification**:

• Enable SQL logging (e.g., logging.level.org.hibernate.SQL=DEBUG in application.yml). Inspect the console for the LIMIT and OFFSET clauses.
• Check the Page response: totalElements, totalPages, and content list size should match the requested page size (except the last page).
• For Slice, confirm that the hasNext() flag is true when there are more rows.

Limitations & Practical Check

1. Offset cost: For very large page numbers, OFFSET forces the database to skip many rows, which can degrade performance. Mitigate by using keyset pagination (e.g., WHERE id > lastSeenId) if your use case allows it.

2. Non‑indexed sort columns: Ordering by a column without an index results in a full table scan. Always index columns used in ORDER BY when they are part of a pageable query.

3. Count query overhead: When using Page, the COUNT(*) can be expensive on large tables with complex joins. Profile the query with EXPLAIN and consider Slice if the total count is not required.

Actionable Closing

When you first expose a list endpoint:

• Start with Pageable and Page<T> – it’s the most straightforward API.
• Enable SQL logging and watch for LIMIT/OFFSET and COUNT(*) queries.
• If the count query is a bottleneck or you’re building an infinite‑scroll UI, switch to Slice<T> and remove the count.
• Always index sort columns and consider keyset pagination for deep paging scenarios.
• Document the trade‑offs in your API spec so front‑end developers know when to expect total counts versus simple “has next” flags.

By following these guidelines, you’ll build a pagination system that scales, stays responsive, and gives developers the data they need without unnecessary overhead.