Stop Loading Everything: Optimizing Pagination with Spring Data JPA

The Performance Trap of Large Result Sets

A common pattern in early-stage Spring applications is returning a List<Entity> from a repository method. This works fine with a few hundred records, but as your database grows, this approach leads to OutOfMemoryError crashes or sluggish API responses because the application attempts to load the entire table into JVM memory.

The solution is server-side pagination, where the database only returns a specific subset of rows. In Spring Data JPA, this is handled by the Pageable interface, which abstracts the complex LIMIT and OFFSET logic across different database dialects.

Page vs. Slice: Choosing Your Return Type

When defining a repository method, you have two primary options for paginated returns. The choice depends entirely on whether your UI needs to show the total number of pages (e.g., "Page 1 of 500").

Using Page<T>

A Page<T> provides the content of the current page plus metadata: total elements, total pages, and whether a next page exists. To provide this, Spring Data JPA executes two queries: one to fetch the data and one SELECT COUNT(*) to determine the total size of the dataset.

Using Slice<T>

A Slice<T> is a lightweight alternative. It knows if a next slice is available but does not know the total count. It achieves this by fetching pageSize + 1 records; if the extra record exists, hasNext() returns true. This eliminates the expensive count query, which is a significant performance win for massive tables.

Implementation Example

To implement pagination, your repository method must accept a Pageable parameter. The implementation is handled automatically by the JPA proxy.

// Repository Interface
public interface ProductRepository extends JpaRepository<Product, Long> {
    // Spring generates the pagination logic based on the Pageable argument
    Page<Product> findByCategory(String category, Pageable pageable);
}

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

    public Page<Product> getProductsByCategory(String category, int page, int size) {
        // PageRequest is the standard implementation of Pageable
        // Note: page index is zero-based
        Pageable pageRequest = PageRequest.of(page, size, Sort.by("name").ascending());
        return repository.findByCategory(category, pageRequest);
    }
}

Verifying the Database Interaction

To ensure your application isn't accidentally loading all records, enable Hibernate SQL logging in your application.properties:

logging.level.org.hibernate.SQL=DEBUG

When calling the method above, you should see two distinct queries in the logs: one starting with select count(*) and another containing the database-specific limit clause (e.g., LIMIT ? OFFSET ? for PostgreSQL/MySQL).

The "Deep Pagination" Limitation

While Pageable solves memory issues, it introduces a database performance bottleneck known as deep pagination. When you request a page with a high offset (e.g., OFFSET 100000 LIMIT 20), the database must still scan through the first 100,000 rows before discarding them and returning the 20 you requested.

Diagnostic Check: If you notice that API response times increase linearly as the user navigates to higher page numbers, you are hitting the offset limit. In these cases, consider Keyset Pagination (also known as the "seek method"), where you filter by the last ID of the previous page (WHERE id > :lastId LIMIT 20) instead of using an offset.

Practical Summary

• Use Page<T> when you need a pagination bar with total page numbers.
• Use Slice<T> for "infinite scroll" or "load more" interfaces to avoid count query overhead.
• Always provide a Sort in your PageRequest; without a deterministic sort, the database may return records in a random order, causing duplicates across pages.