---

Introduction

Enterprise applications often manage millions of database records.

Examples include:

• Banking transactions
• Customer accounts
• Orders
• Payment history
• Audit logs

Loading all records into memory is inefficient and can lead to:

• High memory usage
• Slow response times
• Increased database load
• Poor user experience

Spring Data JPA provides Pagination and Sorting to retrieve data efficiently.

---

Pagination Architecture

flowchart LR

Application --> Pageable

Pageable --> Repository

Repository --> Hibernate

Hibernate --> Database

---

Q1. What is Pagination?

Answer

Pagination divides a large dataset into smaller pages.

Instead of retrieving all records,

the application retrieves only a limited number of records at a time.

Benefits

• Lower memory usage
• Faster responses
• Better scalability
• Improved user experience

---

Q2. What is Pageable?

Pageable represents pagination information.

It contains

• Page number
• Page size
• Sort information

Example

Pageable pageable =

PageRequest.of(

0,

20);

This retrieves

• First page
• 20 records

---

Q3. What is Page?

Page<T> represents a complete page of results.

Example

Page<Customer> page =

repository.findAll(

pageable);

Page provides

• Content
• Total pages
• Total elements
• Current page
• Page size

Page Structure

flowchart LR

Page --> Content

Page --> TotalPages

Page --> TotalElements

Page --> CurrentPage

---

Q4. What is Slice?

Slice<T> is a lightweight alternative to Page.

Difference

Page	Slice
Executes COUNT query	No COUNT query
Knows total pages	Only knows next page
More expensive	Faster

Example

Slice<Customer>

slice=

repository.findAll(

pageable);

Use Slice for infinite scrolling.

---

Q5. What is Sorting?

Sorting arranges results in ascending or descending order.

Example

Sort.by("salary");

Descending

Sort.by(

Direction.DESC,

"salary");

---

Q6. How do Pagination and Sorting work together?

Example

Pageable pageable=

PageRequest.of(

0,

10,

Sort.by("name"));

Combined Flow

flowchart LR

PageRequest --> Pagination

PageRequest --> Sorting

Pagination --> Database

Sorting --> Database

Spring generates SQL using both LIMIT/OFFSET and ORDER BY.

---

Q7. How do you perform multi-column sorting?

Example

Sort.by("city")

.and(

Sort.by(

"salary")

.descending());

Generated SQL

ORDER BY CITY ASC,

SALARY DESC

Useful for complex business reports.

---

Q8. What is Offset Pagination vs Keyset Pagination?

Offset Pagination

LIMIT 20

OFFSET 100

Advantages

• Simple
• Supported everywhere

Disadvantages

• Slow for large offsets

---

Keyset Pagination

WHERE ID > 100

LIMIT 20

Advantages

• Faster
• Better scalability
• Uses indexes efficiently

Comparison

flowchart TD

Pagination --> Offset

Pagination --> Keyset

Offset --> LargeScan

Keyset --> IndexedLookup

Keyset pagination is preferred for large datasets.

---

Q9. What SQL does Spring generate?

Example

PageRequest.of(

1,

10);

Generated SQL

LIMIT 10

OFFSET 10

With sorting

ORDER BY NAME ASC

LIMIT 10

OFFSET 10

Spring automatically generates optimized SQL.

---

Q10. Pagination Best Practices

Never use findAll()

On very large tables.

---

Use Pageable

For APIs returning collections.

---

Prefer Slice

For infinite scrolling.

---

Use Keyset Pagination

For high-volume systems.

---

Always Sort

Pagination without sorting produces inconsistent results.

---

Banking Example

flowchart TD

TransactionService --> TransactionRepository

TransactionRepository --> Pageable

Pageable --> PostgreSQL

PostgreSQL --> Page

Only the requested transactions are returned.

---

Common Interview Questions

• What is Pagination?
• What is Pageable?
• What is Page?
• What is Slice?
• Page vs Slice?
• How does Sorting work?
• Multi-column sorting?
• Offset vs Keyset Pagination?
• Generated SQL?
• Pagination best practices?

---

Quick Revision

Topic	Summary
Pageable	Pagination request
Page	Complete page information
Slice	Lightweight pagination
Sort	Ordering records
PageRequest	Pageable implementation
LIMIT	Restrict result size
OFFSET	Skip records
Keyset Pagination	Faster large-scale pagination
Multi-column Sort	Multiple ORDER BY clauses
findAll()	Avoid on large datasets

---

Pagination Request Lifecycle

sequenceDiagram
Client->>Controller: GET /customers?page=0&size=20
Controller->>Service: Pageable
Service->>Repository: findAll(pageable)
Repository->>Hibernate: Generate SQL
Hibernate->>Database: LIMIT/OFFSET
Database-->>Hibernate: Page Data
Hibernate-->>Repository: Page<Customer>
Repository-->>Service: Response
Service-->>Controller: JSON
Controller-->>Client: Paginated Result

---

Production Example – Banking Transaction History

A banking application stores over 500 million transactions.

Requirements

• Display 25 transactions per page.
• Sort by transaction date (latest first).
• Support filtering by account number.
• Allow customers to scroll through transaction history efficiently.

Implementation

Pageable pageable = PageRequest.of(
    page,
    25,
    Sort.by("transactionDate").descending()
);

Page<Transaction> transactions =
transactionRepository.findByAccountNumber(
    accountNumber,
    pageable
);

For high-volume internal reporting, the application switches to keyset pagination using the transaction ID instead of OFFSET.

flowchart LR

CustomerPortal --> TransactionController

TransactionController --> TransactionService

TransactionService --> TransactionRepository

TransactionRepository --> Pageable

Pageable --> Hibernate

Hibernate --> PostgreSQL

This design minimizes database load, improves response times, and ensures a consistent user experience even with hundreds of millions of records.

---

Key Takeaways

• Pagination retrieves data in manageable chunks, improving performance and reducing memory usage.
• Pageable encapsulates page number, page size, and sorting information.
• Page<T> includes result data along with metadata such as total pages and total elements.
• Slice<T> is more efficient when total record counts are unnecessary, making it ideal for infinite scrolling.
• Sorting ensures consistent ordering of paginated results and supports multiple columns.
• Offset pagination is simple but can become inefficient for large datasets, while keyset pagination offers superior performance for high-volume applications.
• Avoid calling findAll() on large tables without pagination.
• Combining pagination, sorting, and proper indexing is essential for building scalable enterprise applications with Spring Data JPA.