Spring Data JPA Pagination: Choosing Between Page and Slice

The Performance Cost of Total Counts

When implementing pagination in Spring Data JPA, the most critical decision is whether you need to know the total number of pages or simply the next set of records. By default, returning a Page<T> triggers two queries: one to fetch the requested slice of data and a second SELECT COUNT(*) to calculate the total record count.

On tables with millions of rows, the count query often becomes the primary bottleneck, leading to slow response times even when the data fetch itself is indexed and fast. Choosing the wrong return type can inadvertently introduce a linear performance degradation as your dataset grows.

Comparing Page vs. Slice

Feature	Page<T>	Slice<T>
Count Query	Executed automatically	Not executed
Total Elements	Available via getTotalElements()	Unknown
Next Page Check	Available	Available via hasNext()
Use Case	Numbered pagination (e.g., [1] [2] [3])	Infinite scroll or "Load More" buttons

Handling Sorting and Request Logic

Pagination is driven by the Pageable interface. In a Spring Boot application, you typically implement this using PageRequest. Sorting is handled via the Sort class, which maps directly to the SQL ORDER BY clause.

Risk: Sorting on columns that lack a database index will force the database to perform a full table scan and a manual sort in memory, negating the performance benefits of LIMIT and OFFSET.

Implementation Example

Assume a Spring Boot 3.x environment with Spring Data JPA. The following implementation demonstrates a repository that supports both full pagination and lightweight slicing.

// Repository Definition
public interface ProductRepository extends JpaRepository<Product, Long> {
    // Returns total count (Expensive)
    Page<Product> findByCategory(String category, Pageable pageable);

    // Returns only if next page exists (Efficient)
    Slice<Product> findByNameContaining(String query, Pageable pageable);
}

// Service Layer Usage
@Service
public class ProductService {
    @Autowired
    private ProductRepository repository;

    public Page<Product> getCategorizedProducts(String category, int page, int size) {
        // Create a PageRequest with sorting by price descending
        Pageable pageRequest = PageRequest.of(page, size, Sort.by("price").descending());
        return repository.findByCategory(category, pageRequest);
    }
}

Validating Pagination Behavior

To verify that pagination is working as expected and not fetching the entire table, use a @DataJpaTest. You should verify both the content of the page and the metadata.

@DataJpaTest
class ProductPaginationTest {
    @Autowired
    private ProductRepository repository;

    @Test
    void testPaginationMetadata() {
        // Arrange: Insert 15 records
        // ... setup code ...

        // Act: Request page 0 with size 10
        Pageable pageable = PageRequest.of(0, 10);
        Page<Product> result = repository.findByCategory("Electronics", pageable);

        // Assert: Verify slice size and total count
        assertThat(result.getContent()).hasSize(10);
        assertThat(result.getTotalElements()).isEqualTo(15);
        assertThat(result.getTotalPages()).isEqualTo(2);
    }
}

Diagnostic Verification

To confirm the actual database impact, enable SQL logging in your application.properties:

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

When calling a Page<T> method, you will see two SQL statements in the console: one starting with select count(*) and one containing limit ? offset ?. When calling a Slice<T> method, you will see only one query, but the limit will be size + 1. This extra record is how Spring determines if a next page exists without counting the whole table.

Limitations

• Deep Pagination: As the page number increases (e.g., page 10,000), OFFSET becomes slow because the database must still scan through all preceding rows. For extremely large datasets, consider keyset pagination (using the last seen ID) instead of Pageable.
• Count Query Overhead: In complex joins, the generated count query can be inefficient. You can override the count query using the @Query annotation's countQuery attribute for manual optimization.