ReviseAlgo Logo

Spring Data JPA & Hibernate

Pagination and Sorting — Handling Large Datasets

Configuring Pageable queries, sorting variables, and parsing Page results.

Last Updated: June 14, 2026 • 10 min read

Introduction

When querying large tables, returning all database records in a single query is a performance hazard. **Pagination** splits the result set into smaller pages, and **Sorting** allows ordering results using specific columns.

Why It Matters

Loading hundreds of thousands of records consumes massive server memory, causes heavy Garbage Collection pauses, and exhausts bandwidth. Pagination ensures queries remain responsive, resource usage is bounded, and API clients receive predictable chunked payloads.

Real-World Analogy

Think of pagination like a search engine results page (SERP). When searching for a keyword, the system does not show all 3 million matching web pages on a single, long scroll. Instead, it serves the top 10 results (Page 1) and displays navigation controls for subsequent pages. This reduces load time for both search engine servers and your browser.

Detailed Mechanics

1. Page vs Slice

Spring Data JPA supports two primary container types for paginated results:

  • Page<T>: Computes the total elements count and total page count by executing a secondary COUNT query. Excellent for traditional pagination UI tables.
  • Slice<T>: Only checks if there is next page data by fetching pageSize + 1 records, without running a COUNT query. Excellent for mobile infinite-scroll lists where total count is unnecessary.
Return Container Executes SQL COUNT? Performance Trade-off Use Case
Page<T> Yes Slower (COUNT queries block large tables) Classic Web UI Grid tables with pages numbers
Slice<T> No Faster (Only LIMIT/OFFSET query runs) Infinite Scroll Feeds, Mobile Swipe Lists

Practical Example

Here is a production-grade controller and repository integration illustrating sorting and Slice pagination:

Quick Quiz

Q1: Why are COUNT queries on large tables (e.g. 50 million rows) a performance hazard?

A) DB connections cannot run COUNT queries concurrently.

B) COUNT queries force a full database index or table scan to compute counts, taking several seconds and blocking CPU resources.

C) Hibernate is incapable of translating COUNT queries efficiently.

D) SQL databases do not support COUNT metrics queries.

Answer: B — For large tables, count scanning is a heavy disk and CPU read operation. Bypassing it via Slice improves throughput.

Q2: How does Slice know if there is next page data without a count query?

A) It reads transaction log metadata.

B) It queries the database twice.

C) It fetches (pageSize + 1) records. If the list size returned equals (pageSize + 1), it knows a next page exists, and strips the extra entity before returning.

D) It guesses using statistics.

Answer: C — Slice requests one extra element from the database. If that extra element is returned, Slice sets the boolean attribute "hasNext" to true.

Scenario-Based Challenge

Production Scenario:

You implement database pagination using Pageable. When clients request page number 50,000 (deep pagination offset), query response time degrades to 12 seconds. Why does this happen and how do you resolve it?

View Solution

This is the **Offset Pagination Performance Limit**. In database engines, OFFSET queries (e.g. LIMIT 20 OFFSET 1000000) require the database to scan, sort, and discard the first 1,000,000 records before returning the next 20. To resolve this:

  1. Use **Keyset Pagination (Cursor-based Pagination)**. Instead of using offsets, search relative to the last seen item ID (e.g., WHERE id < :lastSeenId ORDER BY id DESC LIMIT 20).
  2. Cap the maximum page index clients can request (e.g. do not allow requesting pages beyond page 100).

Interview Questions

1. Conceptual: How can you combine multiple sorting columns dynamically using Sort?

You can chain sorting criteria using the and() operator: Sort sort = Sort.by("lastName").ascending().and(Sort.by("salary").descending());

2. Coding: How do you configure a custom COUNT query for @Query pagination methods?

By default, Spring tries to generate a count query, but for complex queries, you should define it explicitly using the countQuery parameter:

Production Considerations

Validate and limit client page size. If a client requests size=50000, they can provoke an Out Of Memory crash on the JVM. Implement an API Gateway limit or use @PageableDefault(maxPageSize = 100) filters.