Optimizing Database Retrieval with Spring Data JPA Pagination and Sorting

The Performance Cost of Unbounded Queries

Retrieving an entire table into application memory causes OutOfMemoryError and latency as data grows. Server-side pagination limits the database to a small subset per request.

The useful takeaway is choosing between Page and Slice. Page returns total elements and total pages but forces an additional COUNT query. Slice avoids the count query and only checks if a next slice exists, which is faster for large datasets and infinite scroll UIs.

Implementing Pageable Repositories

Spring Data JPA intercepts a Pageable parameter and translates it to LIMIT and OFFSET for the specific SQL dialect. Sorting is passed inside the Pageable via Sort.

Repository definition

Extend JpaRepository and declare methods that accept Pageable. Return type controls metadata cost.

public interface ProductRepository extends JpaRepository<Product, Long> {
    Page<Product> findByCategory(String category, Pageable pageable);
    Slice<Product> findByPriceLessThan(java.math.BigDecimal price, Pageable pageable);
}

Service usage with PageRequest

Build PageRequest with 0-based page index, size, and Sort. Run this in the service layer with read access to the repository.

public Page<Product> getProductsByCategory(String category, int page, int size) {
    Sort sorting = Sort.by("name").ascending()
                      .and(Sort.by("createdAt").descending());
    Pageable pageable = PageRequest.of(page, size, sorting);
    return productRepository.findByCategory(category, pageable);
}

PageRequest.of is the standard factory for offset and limit. Spring Data JPA uses 0-based page indexing.

Page vs Slice comparison

Feature	Page<T>	Slice<T>
SQL executed	Data query + COUNT query	Data query with size+1 rows
Metadata	Total elements, total pages	hasNext boolean
Performance on large tables	Slower due to COUNT	Faster, no count
Best use	Admin panels, numbered results	Feeds, load more

Limits and common mistakes

Deep pagination latency

High page numbers force the database to scan preceding rows for OFFSET. Avoid allowing arbitrary jumps to page 10,000. Prefer filtering or keyset pagination by last seen id.

Off-by-one page index

API clients often send 1-based pages. PageRequest is 0-based, so subtract 1 before calling PageRequest.of or the first page is skipped.

Count query overhead

SELECT COUNT(*) on millions of rows can dominate request time. Use Slice when total counts are not required for the UI.

Verification and diagnostics

Enable SQL logging in application.properties to confirm pagination is applied at the database level:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Expected checks: a Page method logs a data query with LIMIT and OFFSET and a separate COUNT query. A Slice method logs only the data query with a larger limit. If only a SELECT * appears, pagination is not applied.

Test the API response to ensure totalPages and totalElements match the database state for Page, and hasNext is correct for Slice.