Pagination, Filtering & Sorting in REST APIs - Complete Enterprise Guide

Learn how to design scalable REST APIs using Pagination, Filtering, and Sorting. Explore offset pagination, cursor pagination, keyset pagination, filtering strategies, sorting techniques, Spring Boot implementation, database optimization, and enterprise best practices.

Introduction

Modern enterprise applications manage millions of records.

Examples:

Banking Transactions
Insurance Policies
Customer Accounts
Orders
Products
Employees
Audit Logs
Notifications
Medical Records
Payment History

Imagine a database containing:

50 Million Customers
500 Million Orders
5 Billion Transactions

If an API returns every record in a single request:

Huge response payload
Slow response time
High memory usage
Database overload
Poor user experience
Network congestion

Instead, enterprise APIs return only the required data using:

Pagination
Filtering
Sorting

These three concepts are fundamental to scalable API design.

Why Are They Important?

Imagine an e-commerce application.

Database:

Products

↓

12 Million Records

Customer opens the application.

Without pagination:

API

↓

12 Million Products

↓

Browser

Problems:

OutOfMemoryError
Slow API
Browser freeze
High bandwidth usage

Instead:

API

↓

20 Products

↓

User

Fast.

Scalable.

Efficient.

High-Level Architecture

flowchart LR

CLIENT[Client]

CLIENT --> API[Spring Boot REST API]

API --> SERVICE[Service Layer]

SERVICE --> REPOSITORY[Repository]

REPOSITORY --> DATABASE[(Database)]

DATABASE --> REPOSITORY

REPOSITORY --> SERVICE

SERVICE --> API

API --> CLIENT

What is Pagination?

Pagination divides large datasets into smaller pages.

Example:

Database

1000 Customers

Instead of returning:

1000 Records

Return:

Page 1

20 Records

Then:

Page 2

20 Records

Until completion.

Pagination Flow

sequenceDiagram

participant User

participant API

participant Database

User->>API: GET /customers?page=1&size=20

API->>Database: LIMIT 20 OFFSET 0

Database-->>API: 20 Customers

API-->>User: Response

Types of Pagination

Enterprise applications generally use three approaches.

Offset Pagination
Cursor Pagination
Keyset Pagination

Offset Pagination

Most common.

Example:

GET /customers?page=2&size=20

SQL:

SELECT *
FROM customers
LIMIT 20 OFFSET 20;

Flow:

Page 1

↓

Records 1–20

↓

Page 2

↓

Records 21–40

Advantages

Easy to implement
Easy to understand
Works well for small datasets

Disadvantages

Large OFFSET values become slower because the database still scans skipped rows.

Cursor Pagination

Instead of page numbers:

Use cursor.

Example:

GET /customers?cursor=1258

SQL:

SELECT *
FROM customers
WHERE id > 1258
LIMIT 20;

Flow:

Last ID

↓

1258

↓

Next 20 Records

Advantages

Fast
Stable
Ideal for large datasets
Preferred for infinite scrolling

Disadvantages

More complex implementation
Random page navigation is difficult

Keyset Pagination

Uses indexed columns.

Example:

WHERE created_at > ?
ORDER BY created_at
LIMIT 20;

Flow:

Last Timestamp

↓

Next Records

Ideal for time-series data.

Pagination Comparison

Feature	Offset	Cursor	Keyset
Easy	✅	❌	❌
Performance	Medium	High	High
Large Dataset	Poor	Excellent	Excellent
Infinite Scroll	No	Yes	Yes
Random Page Access	Yes	Limited	Limited

What is Filtering?

Filtering returns only matching records.

Without filtering:

All Orders

↓

1 Million Orders

With filtering:

Status = PAID

↓

Only Paid Orders.

Filtering Example

GET /orders?status=PAID

SQL:

SELECT *
FROM orders
WHERE status='PAID';

Multiple Filters

Example:

GET /orders?status=PAID&country=USA

SQL:

SELECT *
FROM orders
WHERE status='PAID'
AND country='USA';

Range Filtering

Example:

GET /transactions?minAmount=100&maxAmount=1000

SQL:

SELECT *
FROM transactions
WHERE amount BETWEEN 100 AND 1000;

Date Filtering

Example:

GET /orders?startDate=2026-01-01&endDate=2026-01-31

Used extensively in:

Banking
Reporting
Analytics
Audit APIs

Search Filtering

Example:

GET /customers?name=venu

SQL:

WHERE name LIKE '%venu%'

Advanced Filtering

Example:

GET /employees?

department=IT

&location=Texas

&experience=10

&status=ACTIVE

Enterprise APIs often support multiple optional filters.

Filtering Workflow

flowchart LR
    U["User"]
    API["REST API"]
    F["Apply Filters"]
    DB["Database Query"]
    RES["Filtered Result"]

    U --> API --> F --> DB --> RES

What is Sorting?

Sorting arranges records.

Example:

Without sorting:

John

Alex

Venu

Chris

Ascending:

Alex

Chris

John

Venu

Sorting Example

GET /customers?sort=name

SQL:

ORDER BY name ASC

Descending Sort

GET /orders?sort=date,desc

SQL:

ORDER BY order_date DESC;

Newest orders appear first.

Multiple Sorting

Example:

GET /employees?

sort=department

&sort=salary,desc

SQL:

ORDER BY department,
salary DESC;

Pagination + Filtering + Sorting Together

Real-world API:

GET /orders?

page=0

&size=20

&status=PAID

&country=USA

&sort=createdDate,desc

Execution order:

Database

↓

Filter

↓

Sort

↓

Pagination

↓

Response

Complete Flow

flowchart TD

Request

-->

Filtering

-->

Sorting

-->

Pagination

-->

Database

-->

Response

Spring Boot Support

Spring Data JPA provides built-in support.

Typical classes:

Pageable
Page
PageRequest
Sort

Example:

Pageable pageable =
PageRequest.of(
0,
20,
Sort.by("name")
);

Page<Customer> page =
repository.findAll(pageable);

Benefits:

Minimal code
Automatic pagination
Built-in sorting

REST Response Example

{
  "content":[
    ...
  ],
  "page":0,
  "size":20,
  "totalPages":25,
  "totalElements":500,
  "last":false
}

Clients receive both data and pagination metadata.

Database Optimization

Ensure indexes exist on:

ID
Created Date
Status
Customer ID
Email
Order Number

Indexes significantly improve filtering and sorting performance.

Common Mistakes

❌ Returning millions of records

❌ No pagination

❌ Sorting non-indexed columns

❌ OFFSET with huge datasets

❌ Loading unnecessary columns

❌ Missing indexes

❌ Returning entire entities when projections suffice

Enterprise Best Practices

Always paginate list APIs.
Prefer cursor/keyset pagination for large datasets.
Validate page size.
Apply indexes to filter and sort columns.
Return metadata with paginated responses.
Allow multiple filters.
Use consistent sorting defaults.
Prevent SQL injection through parameter binding.
Limit maximum page size.
Cache frequently requested pages when appropriate.

Real-World Use Cases

Banking

Transactions

↓

Filter by Account

↓

Sort by Date

↓

Page Size = 50

Insurance

Claims

↓

Status

↓

Claim Date

↓

Pagination

E-Commerce

Products

↓

Category

↓

Price

↓

Rating

↓

Pagination

Healthcare

Patients

↓

Doctor

↓

Appointment Date

↓

Pagination

Audit System

Audit Logs

↓

Severity

↓

Timestamp

↓

Cursor Pagination

Enterprise Architecture

flowchart LR
    CLIENT["Client"]
    API["API Gateway"]
    SB["Spring Boot API"]
    SERVICE["Service Layer"]
    REPO["Repository"]
    DB["Database"]
    RESPONSE["Page Response"]

    CLIENT --> API --> SB --> SERVICE --> REPO --> DB --> RESPONSE --> CLIENT

Interview Questions

Why is pagination important?
Explain Offset Pagination.
Explain Cursor Pagination.
What is Keyset Pagination?
Which pagination is best for large datasets?
Why is OFFSET slow?
How does Spring Boot implement pagination?
What is the difference between filtering and sorting?
Why should filtered columns be indexed?
How would you design a scalable search API?

Summary

Pagination, Filtering, and Sorting are essential building blocks of scalable REST APIs.

A production-ready API should:

Support efficient pagination.
Filter data using indexed columns.
Allow flexible sorting.
Return pagination metadata.
Protect databases from excessive load.
Optimize queries using indexes.
Validate client parameters.
Scale to millions or billions of records.

Spring Boot and Spring Data JPA provide first-class support for these features, making it straightforward to build performant enterprise APIs used in banking, insurance, healthcare, retail, and other large-scale systems.