Spring Boot Pagination Done Right: Avoiding the Common Pitfalls (2026)

Pagination is one of those features developers add in 10 minutes and spend weeks fixing. The default Spring Data implementation works correctly - but at scale, its limitations cause full-table scans, unbounded memory usage, and response times that grow with your data set.

---

The Basics: Spring Data Pageable

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    Page<Order> findByCustomerId(Long customerId, Pageable pageable);
}

@RestController
public class OrderController {

    @GetMapping("/orders")
    public Page<OrderDto> getOrders(
            @RequestParam Long customerId,
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "20") int size,
            @RequestParam(defaultValue = "createdAt,desc") String sort) {

        Pageable pageable = PageRequest.of(page, size,
            Sort.by(Sort.Direction.DESC, "createdAt"));

        return orderRepository.findByCustomerId(customerId, pageable)
            .map(orderMapper::toDto);
    }
}

This works. But there are several traps hiding in this simple code.

---

Trap 1: The Count Query Problem

Page<T> fires TWO queries: one for the data, one for the total count. The count query can be expensive:

-- Data query
SELECT * FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20 OFFSET 0

-- Count query - full table scan if no index
SELECT COUNT(*) FROM orders WHERE customer_id = ?

For tables with millions of rows, COUNT(*) with joins can take seconds. Fix it by:

Option A: Use Slice instead of Page (no count query)

// Slice doesn't fire a count query - returns hasNext() instead of total
Slice<Order> findByCustomerId(Long customerId, Pageable pageable);

// Client gets: content, hasNext, page number - but NOT total count
// Good for infinite scroll, bad for showing "Page 3 of 47"

Option B: Separate the count query

@Query(value = "SELECT o FROM Order o JOIN FETCH o.items WHERE o.customerId = :cid",
       countQuery = "SELECT COUNT(o) FROM Order o WHERE o.customerId = :cid")
Page<Order> findByCustomerIdWithItems(@Param("cid") Long customerId, Pageable pageable);

The count query avoids the JOIN FETCH, making it much faster.

---

Trap 2: Deep Offset Pagination is Slow

Offset-based pagination (LIMIT 20 OFFSET 10000) gets slower as the offset increases:

-- Page 1: fast
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0

-- Page 500: database scans 10,000 rows to skip them
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000

For large datasets, use keyset (cursor) pagination:

// Instead of: GET /orders?page=500&size=20
// Use: GET /orders?after=2024-01-15T10:30:00Z&afterId=12345&size=20

@Query("SELECT o FROM Order o WHERE " +
       "(o.createdAt < :cursor OR (o.createdAt = :cursor AND o.id < :cursorId)) " +
       "ORDER BY o.createdAt DESC, o.id DESC")
List<Order> findBeforeCursor(
    @Param("cursor") LocalDateTime cursor,
    @Param("cursorId") Long cursorId,
    Pageable pageable);

The cursor query always hits an index and takes the same time regardless of page depth.

---

Trap 3: No Size Limit

// DANGEROUS - client can request size=1000000
@GetMapping("/orders")
public Page<Order> getOrders(Pageable pageable) {
    return orderRepository.findAll(pageable);
}

Always cap page size:

@GetMapping("/orders")
public Page<OrderDto> getOrders(
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "20") int size) {

    int cappedSize = Math.min(size, 100); // Never more than 100
    Pageable pageable = PageRequest.of(page, cappedSize);
    return orderRepository.findAll(pageable).map(orderMapper::toDto);
}

Or use Spring Data's PageableHandlerMethodArgumentResolverCustomizer:

@Configuration
public class WebConfig implements WebMvcConfigurer {

    @Override
    public void addArgumentResolvers(List<HandlerMethodArgumentResolver> resolvers) {
        PageableHandlerMethodArgumentResolver resolver =
            new PageableHandlerMethodArgumentResolver();
        resolver.setMaxPageSize(100);      // Hard cap
        resolver.setFallbackPageable(PageRequest.of(0, 20)); // Default
        resolvers.add(resolver);
    }
}

---

Trap 4: Sorting on Non-Indexed Columns

// Sorting by 'customerName' - a VARCHAR column with no index
PageRequest.of(0, 20, Sort.by("customerName"));
// ? Full table scan + filesort - catastrophic on large tables

Only allow sorting on indexed columns:

public Page<OrderDto> getOrders(String sortBy, String direction, Pageable pageable) {
    Set<String> allowedSortFields = Set.of("createdAt", "total", "status");

    if (!allowedSortFields.contains(sortBy)) {
        throw new InvalidSortFieldException("Invalid sort field: " + sortBy);
    }

    Sort sort = Sort.by(Sort.Direction.fromString(direction), sortBy);
    return orderRepository.findAll(PageRequest.of(
        pageable.getPageNumber(), pageable.getPageSize(), sort)
    ).map(orderMapper::toDto);
}

---

Common Mistakes to Avoid

• Returning List<T> from API endpoints with no pagination - a list endpoint that returns all records will eventually OOM your server
• Using Page<Entity> directly in REST responses - serializes internal Spring Data fields; always map to a DTO
• Not indexing the sort column - pagination without an index on the ORDER BY column causes full table scans
• Offset pagination beyond page 100 - after a few hundred pages, offset pagination is slower than a full table scan; switch to cursor-based for deep pagination

---

Summary

Spring Data pagination is correct but has performance traps at scale: use Slice instead of Page for infinite scroll (no count query), use keyset pagination for deep page navigation, cap page size server-side, and only sort on indexed columns. These four changes cover the vast majority of pagination performance problems.

---

Detect Pagination Anti-Patterns in Your Codebase

JOptimize flags unbounded findAll() calls, missing page size caps, and sorting on likely non-indexed columns across your Spring Boot repositories.

• IntelliJ Plugin - detects unbounded queries inline and suggests pagination with a 1-click fix: Install JOptimize for IntelliJ
• Web Dashboard - full scan of your repository layer for pagination issues: Analyze your project free ?

Fix unbounded queries before they cause production memory issues - free scan, no configuration required.