Efficient Pagination with Spring Data JPA Slice: Skip the COUNT(*) Query

Problem: costly COUNT(*) on large tables

When you use Spring Data JPA’s Page<T> return type, the framework adds a secondary SELECT COUNT(*) query to determine the total number of elements. For tables with millions of rows this extra scan can dominate latency and increase database load, especially if the UI only needs to know “are there more items?”.

Thesis

Replacing Page with Slice removes the unnecessary count query while still giving you the data for the current page and a flag that tells whether another page exists. This yields faster response times and lower CPU usage when total counts are not required.

How Slice works

A Slice<T> contains:

• The list of entities for the current page.
• A boolean hasNext() that is true when the query returned more elements than the requested page size (indicating at least one more page).

Because the count is omitted, the generated SQL includes only LIMIT and OFFSET (or the dialect‑specific equivalent) and no separate SELECT COUNT(*).

Worked example

Assume a simple User entity and a repository that needs to fetch users in batches for infinite scroll.

package com.example.demo.repository;

import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository {
    /**
     * Returns a Slice of users ordered by lastName, firstName.
     * No COUNT(*) is executed.
     */
    Slice findAllByOrderByLastNameAscFirstNameAsc(Pageable pageable);
}

Service layer that pages until hasNext() is false:

package com.example.demo.service;

import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Slice;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class UserPaginationService {
    private final UserRepository userRepository;

    public UserPaginationService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    /**
     * Collects all users using Slice‑based pagination.
     * Callers can stop early if they only need a subset.
     */
    public List fetchAllUsers(int pageSize) {
        List result = new java.util.ArrayList<>();
        Pageable pageable = PageRequest.of(0, pageSize);
        Slice slice;
        do {
            slice = userRepository.findAllByOrderByLastNameAscFirstNameAsc(pageable);
            result.addAll(slice.getContent());
            pageable = pageable.next(); // advances offset by pageSize
        } while (slice.hasNext());
        return result;
    }
}

Trade‑offs and limitations

• No total count: If your UI needs to show “X of Y records” or a progress bar, you must keep using Page or compute the count separately.
• Page size matters: Very small pages increase round‑trips; very large pages reduce the benefit of avoiding the count query. Choose a size that matches your UI’s batch expectations (commonly 20‑50).
• Stale hasNext: If rows are inserted or deleted between requests, hasNext() may incorrectly indicate more or fewer pages. This is the same limitation as offset‑based pagination.

Actionable guidance

1. Identify endpoints where the total count is not displayed (e.g., infinite scroll, “load more” buttons).
2. Change the repository method return type from Page<T> to Slice<T>.
3. Update the service/controller to use slice.hasNext() instead of page.getTotalPages().
4. Verify the SQL: enable Hibernate statistics (spring.jpa.properties.hibernate.generate_statistics=true) and check logs for a single SELECT with LIMIT/OFFSET and no accompanying SELECT COUNT(*).
5. Run a load test (e.g., with JMeter) against a table of >1 M rows and compare average response time and DB CPU usage between the Page and Slice implementations.

By adopting Slice where a total count is unnecessary, you eliminate an expensive query and improve scalability without changing your domain model.