Reducing Count Query Cost in Pagination

While building list APIs, I initially used Spring Data JPA Page because it returns the content, total count, and total page count together.

A review made me reconsider that default. Page usually runs a count query in addition to the content query. With complex filters, joins, or large tables, that count can become expensive.

Problem

The content query is only one part of a Page response.

select *
from product
where category_id = ?
order by created_at desc
limit ? offset ?;

To calculate total elements and total pages, Spring Data also needs a count.

select count(*)
from product
where category_id = ?;

With simple filters, that may be acceptable. With joins, search conditions, or large tables, the count can become the expensive part of the request.

Page is useful when the UI needs exact total counts or direct page navigation. Admin pages often need that information.

Slice focuses on whether there is a next page. For infinite scroll or load-more interfaces, that can be enough. It avoids calculating the full count.

Solution

I started checking whether the screen actually needs the total count, how expensive the count query can become, and whether response speed is more important than exact pagination metadata.

The decision became a UI and query-cost question.

For Slice, the usual idea is to fetch one more row than requested and use that extra row to decide whether a next page exists.

Slice<ProductSummary> products = productRepository.findSliceByCategory(
        categoryId,
        PageRequest.of(page, size)
);

After this review, I stopped using Page as the automatic default for list APIs. The UI requirement had to justify the extra count query.

Takeaway

Pagination is not only about splitting data. It also decides which additional information the database must calculate. Choosing Slice when total count is unnecessary can reduce query cost with a simple design decision.