Spring Data JPA: Page<T> with COUNT query vs Slice<T> for paginating a multi-million-row table

Answer

For a read‑only endpoint over a table that will hold tens of millions of rows, Slice<T> is the documented way to avoid the extra SELECT COUNT(*) that Page<T> triggers.

What you lose

• totalElements – the exact row count.
• totalPages – derived from the count and page size.

The slice still returns hasNext() (based on fetching one extra row) and the current page number, but you cannot compute page numbers beyond the current slice without the total.

Offset degradation

Both Page<T> and Slice<T> are built on Spring Data’s LIMIT/OFFSET implementation. The performance penalty that appears when the offset grows large is therefore present for either return type; choosing Slice<T> does not change the underlying query.

Steps to use Slice

1. Declare the repository method returning Slice<Entity> and accept a Pageable parameter.
2. Example:

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

3. Call the method from a service/controller, passing PageRequest.of(page, size) (or a custom Pageable that sorts on a unique column).
4. In the response, expose hasNext() and optionally the current page number; adapt the UI to use "load more" or infinite scroll instead of page numbers.

When the total count is still required

If the UI must show total pages or a progress indicator, you need an alternative to the expensive COUNT(*). Options include:

• Maintaining a separate statistics table updated via triggers or batch jobs.
• Using database‑specific approximations (e.g., PostgreSQL’s pg_tuples_count or reltuples from pg_class).
• Caching the count for a short interval.

Only adopt one of these if you confirm that the exact count is required; otherwise, Slice<T> alone satisfies the bounded‑query goal.

Version‑specific notes (Spring Boot 3.x / Spring Data JPA 3.x)

The COUNT behavior of Page<T> and the hasNext logic of Slice<T> have not changed between Spring Data JPA 2.x and 3.x. No automatic optimization removes the COUNT query unless you supply a custom count query via @Query with a countQuery attribute.

Verification you can run

• Enable SQL logging (spring.jpa.show-sql=true) and confirm that a Page call produces two statements (data + COUNT) while a Slice call produces only the data query plus one extra row.
• Run EXPLAIN ANALYZE on the generated COUNT query against a table with tens of millions of rows to observe its cost.
• Measure response times for increasing offset values (e.g., page 0, 1000, 5000) for both return types to see the offset‑related slowdown.

Missing diagnostic detail

Do you need to display the total number of rows or total pages to the client? If the answer is yes, the Slice<T> approach alone is insufficient and you will need an approximate‑count strategy; if the answer is no, Slice<T> is the recommended solution.