Choosing Page<T> vs Slice<T> in Spring Data JPA: When to Pay the Count Cost

1. The Problem: Deep Offset on a Huge Table

When a table contains millions of rows, a request such as SELECT * FROM user ORDER BY id ASC LIMIT 50 OFFSET 500000 forces the database engine to skip 500,000 rows before it can deliver the 50 rows you asked for. The cost grows linearly with the offset, and the response time can become unacceptable for a web API.

Spring Data JPA hides this complexity behind the Pageable abstraction, but the underlying SQL still matters. The two most common return types—Page<T> and Slice<T>—behave differently in terms of the generated SQL and the number of queries executed.

2. How Spring Data JPA Translates Pageable to SQL

For any repository method that accepts a Pageable and returns either Page<T> or Slice<T>, Hibernate generates two statements:

• Data query: SELECT * FROM user WHERE last_name = ? ORDER BY id ASC LIMIT ? OFFSET ?
• Count query (only for Page): SELECT COUNT(*) FROM user WHERE last_name = ?

The count query is executed only when the return type is Page<T> because callers need the total number of elements to compute totalPages and totalElements. A Slice<T> only needs to know whether a next slice exists, so Hibernate skips the count query entirely.

3. Page<T> vs Slice<T> – When to Use Which

• Page<T> is ideal when the UI displays page numbers, total counts, or when you need to calculate the exact number of pages. The extra COUNT(*) query can dominate the response time on very large tables, especially if the column used in the WHERE clause is not highly indexed.
• Slice<T> is perfect for infinite‑scroll or “load more” UIs that never show a total count. It fetches pageSize + 1 rows, then discards the extra row to determine hasNext. This eliminates the expensive count query.
• Sorting matters. If you omit an ORDER BY, the database may return rows in an arbitrary order that can change between requests, causing duplicate or missing rows when paging. Always supply an explicit sort, e.g., Sort.by("id").ascending().
• Maximum page size. Exposing unbounded size parameters via web‑bound Pageable lets clients request millions of rows. Configure spring.data.web.pageable.max-page-size to protect the database.

4. Deep Offset vs Keyset Pagination

Offset pagination is simple but suffers from linear scan cost. Keyset (seek) pagination uses a filter on an indexed column to jump directly to the next set of rows:

SELECT * FROM user WHERE id > :lastId ORDER BY id ASC LIMIT :size

Spring Data’s Pageable is offset‑based by design, so you must write a custom query or use a Specification that implements keyset logic. The trade‑off is a slightly more complex query but a dramatic performance win for deep pages.

5. Worked Example: Switching from Page to Slice

Suppose you have a user list endpoint that currently returns Page<User>:

interface UserRepository extends JpaRepository<User, Long> {
    Page<User> findByLastName(String lastName, Pageable pageable);
}

@RestController
class UserController {
    @Autowired
    private UserRepository repo;

    @GetMapping("/users")
    public Page<User> list(@RequestParam String lastName, Pageable pageable) {
        return repo.findByLastName(lastName, pageable);
    }
}

To switch to Slice<User> you only need to change the return type in the repository and controller:

interface UserRepository extends JpaRepository<User, Long> {
    Slice<User> findByLastName(String lastName, Pageable pageable);
}

@RestController
class UserController {
    @Autowired
    private UserRepository repo;

    @GetMapping("/users")
    public Slice<User> list(@RequestParam String lastName, Pageable pageable) {
        return repo.findByLastName(lastName, pageable);
    }
}

With this change, Spring logs only one query per request. If you enable spring.jpa.show-sql=true, you’ll see:

SELECT * FROM user WHERE last_name = ? ORDER BY id ASC LIMIT 51 OFFSET 0

The LIMIT 51 is pageSize + 1 (here 50 + 1) to determine hasNext. No COUNT(*) appears.

6. Checklist Before Deploying

• Confirm that the WHERE clause is indexed; otherwise the count query will still be expensive.
• Set a maximum page size in application.yml or application.properties.
  • spring.data.web.pageable.max-page-size=200
• Verify that the Sort is deterministic (e.g., id or a composite key).
• Log SQL and run EXPLAIN ANALYZE on the data query to ensure the database uses indexes.
• If you need deep pages (>100,000 rows), consider a keyset query and write a custom repository method.

7. Closing Thoughts

Choosing between Page<T> and Slice<T> is a simple decision that can have a huge impact on response latency for large tables. If your UI never needs total counts, go with Slice to avoid the costly COUNT(*) query. For traditional paginated tables where users expect page numbers, Page is the right choice—but be mindful of the count cost and consider keyset pagination for very deep offsets.

Always benchmark with your own data and monitor the SQL logs. Small changes in the repository signature can save seconds of latency, especially under load.