Complete Guide to Pagination

Table of Contents

1. What is Pagination?
2. Why is Pagination Necessary?
3. Types of Pagination
4. Offset-Based Pagination
5. Cursor-Based Pagination
6. Keyset Pagination
7. Seek Method
8. Comparison and When to Use Each
9. Best Practices

---

What is Pagination?

Pagination is the process of dividing a large dataset into smaller, discrete pages or chunks that can be retrieved and displayed incrementally rather than all at once.

Simple Analogy

Think of a phone book with 10,000 contacts. Instead of loading all 10,000 contacts at once, pagination shows 20 contacts per page, allowing users to navigate through pages.

Example

Total Records: 1000
Page Size: 10

Page 1: Records 1-10
Page 2: Records 11-20
Page 3: Records 21-30
...
Page 100: Records 991-1000

---

Why is Pagination Necessary?

1. Performance Optimization

• Database Load: Fetching millions of records in one query can overwhelm the database
• Network Bandwidth: Transferring large datasets consumes bandwidth unnecessarily
• Memory Usage: Loading all data into memory can cause OutOfMemoryErrors

Example:

// ❌ BAD: Fetches 1 million records
List<User> users = userRepository.findAll(); // 500MB of data!

// ✅ GOOD: Fetches 20 records
Page<User> users = userRepository.findAll(PageRequest.of(0, 20)); // ~50KB

2. User Experience

• Faster Load Times: Users see results immediately instead of waiting for all data
• Better Navigation: Easier to browse through organized chunks
• Reduced Cognitive Load: Less overwhelming than viewing thousands of items at once

3. Resource Management

• Server Memory: Prevents server from running out of memory
• Client Memory: Mobile devices can't handle large datasets
• Connection Timeouts: Large responses may timeout before completion

4. Cost Efficiency

• Database Query Costs: Many cloud databases charge per query time/rows scanned
• Data Transfer Costs: Less data transferred = lower costs
• Infrastructure Scaling: Smaller responses reduce need for expensive infrastructure

---

Types of Pagination

There are four main approaches to pagination:

1. Offset-Based Pagination (Page Number)
2. Cursor-Based Pagination (Stateless)
3. Keyset Pagination (Range-Based)
4. Seek Method (SQL-Optimized)

Let's explore each in detail.

---

Offset-Based Pagination

What is it?

The traditional approach using page numbers and offset to skip records.

How it Works

SQL Mechanism

-- Page 1 (skip 0, take 10)
SELECT * FROM products 
ORDER BY id 
LIMIT 10 OFFSET 0;

-- Page 2 (skip 10, take 10)
SELECT * FROM products 
ORDER BY id 
LIMIT 10 OFFSET 10;

-- Page 100 (skip 990, take 10)
SELECT * FROM products 
ORDER BY id 
LIMIT 10 OFFSET 990;

Internal Database Process

Database execution for OFFSET 990:
1. Start scanning from the beginning
2. Read row 1 ✓
3. Read row 2 ✓
4. Read row 3 ✓
   ...
991. Read row 991 ✓
992. Read row 992 ✓
   ...
1000. Read row 1000 ✓
   (Discard rows 1-990, return rows 991-1000)

The database must scan through ALL skipped rows!

Implementation

Spring Data JPA

// Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
    Page<Product> findAll(Pageable pageable);
}

// Service
@Service
public class ProductService {

    @Autowired
    private ProductRepository repository;

    public Page<Product> getProducts(int page, int size) {
        Pageable pageable = PageRequest.of(page, size, Sort.by("id").ascending());
        return repository.findAll(pageable);
    }
}

// Controller
@GetMapping("/products")
public ResponseEntity<Page<Product>> getProducts(
    @RequestParam(defaultValue = "0") int page,
    @RequestParam(defaultValue = "10") int size
) {
    return ResponseEntity.ok(productService.getProducts(page, size));
}

QueryDSL

public Page<Product> findProducts(int page, int size) {
    QProduct product = QProduct.product;

    long total = queryFactory
        .selectFrom(product)
        .fetchCount();

    List<Product> results = queryFactory
        .selectFrom(product)
        .orderBy(product.id.asc())
        .offset((long) page * size)
        .limit(size)
        .fetch();

    return new PageImpl<>(results, PageRequest.of(page, size), total);
}

API Request/Response

Request:

GET /api/products?page=2&size=10

Response:

{
  "content": [
    {"id": 21, "name": "Product 21"},
    {"id": 22, "name": "Product 22"},
    ...
    {"id": 30, "name": "Product 30"}
  ],
  "pageable": {
    "pageNumber": 2,
    "pageSize": 10,
    "offset": 20
  },
  "totalElements": 1000,
  "totalPages": 100,
  "first": false,
  "last": false,
  "number": 2
}

Advantages ✅

1. Simple to Implement

   • Built-in support in most frameworks
   • Easy to understand for developers

2. Random Access

   • Jump to any page directly: Page 1, 50, 100
   • Users can bookmark specific pages

3. Total Count Available

   • Know exactly how many pages exist
   • Show "Page 5 of 100"

4. Familiar UX

   • Standard pagination UI (1, 2, 3, ... 10)
   • Users understand page numbers

Disadvantages ❌

1. Performance Degrades with Deep Pagination

   Page 1:    OFFSET 0      → 10 rows scanned    (5ms)
   Page 10:   OFFSET 100    → 110 rows scanned   (15ms)
   Page 100:  OFFSET 1000   → 1010 rows scanned  (150ms)
   Page 1000: OFFSET 10000  → 10010 rows scanned (1500ms)

1. Inconsistent Results
   • If data changes between requests, items can appear twice or get skipped

   User on Page 1 (IDs 1-10)
   → New item inserted with ID 5
   User clicks Page 2
   → Gets IDs 10-19 (ID 10 appears on both pages!)

1. Expensive COUNT Query

   SELECT COUNT(*) FROM products; -- Scans entire table!

1. Database Load
   • OFFSET scans and discards rows (wasted work)
   • No index optimization for OFFSET

Use Cases ✅

Best suited for:

1. Admin Dashboards

   • Small datasets (< 10,000 records)
   • Need to jump to specific pages
   • Users rarely go beyond page 10

2. Search Results

   • Google-style pagination (limited to ~100 pages)
   • Users rarely go beyond first few pages

3. Reports and Tables

   • Fixed datasets that don't change frequently
   • Need page numbers for reference

4. Small Applications

   • Datasets under 1000 records
   • Performance not critical

Example Scenarios:

• Employee directory (500 employees)
• Product catalog (2000 products)
• Blog posts (300 articles)
• Customer list in admin panel

---

Cursor-Based Pagination

What is it?

A stateless pagination approach using an opaque cursor (pointer) to mark the position in the dataset.

How it Works

SQL Mechanism

-- Page 1 (no cursor)
SELECT * FROM posts 
ORDER BY id ASC 
LIMIT 10;
-- Returns IDs: 1-10
-- Last ID = 10 → becomes cursor

-- Page 2 (cursor = 10)
SELECT * FROM posts 
WHERE id > 10 
ORDER BY id ASC 
LIMIT 10;
-- Returns IDs: 11-20
-- Last ID = 20 → becomes cursor

-- Page 3 (cursor = 20)
SELECT * FROM posts 
WHERE id > 20 
ORDER BY id ASC 
LIMIT 10;
-- Returns IDs: 21-30

Internal Database Process

For cursor = 20:

Database execution:
1. Index seek to ID > 20 (uses B-tree index)
2. Start reading from ID = 21
3. Read 10 rows (21-30)
4. Return results

Total rows scanned: 10 (not 30!)

No scanning of previous rows!

Implementation

Repository

@Repository
public class PostRepository {

    private final JPAQueryFactory queryFactory;

    public CursorPage<Post> findWithCursor(Long cursor, int pageSize) {
        QPost post = QPost.post;
        BooleanBuilder builder = new BooleanBuilder();

        // Apply cursor
        if (cursor != null) {
            builder.and(post.id.gt(cursor));
        }

        // Fetch pageSize + 1 to detect if there's more
        List<Post> results = queryFactory
            .selectFrom(post)
            .where(builder)
            .orderBy(post.id.asc())
            .limit(pageSize + 1)
            .fetch();

        // Check if there are more results
        boolean hasMore = results.size() > pageSize;
        if (hasMore) {
            results.remove(results.size() - 1);
        }

        // Generate next cursor (encode last ID)
        String nextCursor = null;
        if (!results.isEmpty() && hasMore) {
            Long lastId = results.get(results.size() - 1).getId();
            nextCursor = encodeCursor(lastId);
        }

        return new CursorPage<>(results, nextCursor, hasMore);
    }

    private String encodeCursor(Long id) {
        return Base64.getEncoder().encodeToString(id.toString().getBytes());
    }

    private Long decodeCursor(String cursor) {
        byte[] decoded = Base64.getDecoder().decode(cursor);
        return Long.parseLong(new String(decoded));
    }
}

Response DTO

@Getter
@AllArgsConstructor
public class CursorPage<T> {
    private List<T> data;
    private String nextCursor;  // Opaque cursor for next page
    private boolean hasMore;
}

Service

@Service
public class PostService {

    public CursorPage<PostDTO> getPosts(String cursor, int pageSize) {
        Long cursorId = null;
        if (cursor != null) {
            cursorId = decodeCursor(cursor);
        }

        CursorPage<Post> page = repository.findWithCursor(cursorId, pageSize);

        List<PostDTO> dtos = page.getData().stream()
            .map(this::toDTO)
            .collect(Collectors.toList());

        return new CursorPage<>(dtos, page.getNextCursor(), page.isHasMore());
    }
}

Controller

@GetMapping("/posts")
public ResponseEntity<CursorPage<PostDTO>> getPosts(
    @RequestParam(required = false) String cursor,
    @RequestParam(defaultValue = "10") int pageSize
) {
    return ResponseEntity.ok(postService.getPosts(cursor, pageSize));
}

API Request/Response

Request 1:

GET /api/posts?pageSize=10

Response 1:

{
  "data": [
    {"id": 1, "title": "Post 1"},
    {"id": 2, "title": "Post 2"},
    ...
    {"id": 10, "title": "Post 10"}
  ],
  "nextCursor": "MTA=",  // Base64 encoded "10"
  "hasMore": true
}

Request 2:

GET /api/posts?pageSize=10&cursor=MTA=

Response 2:

{
  "data": [
    {"id": 11, "title": "Post 11"},
    {"id": 12, "title": "Post 12"},
    ...
    {"id": 20, "title": "Post 20"}
  ],
  "nextCursor": "MjA=",  // Base64 encoded "20"
  "hasMore": true
}

Cursor Encoding Strategies

1. Simple Base64 (Readable but not secure)

String cursor = Base64.encode("123");
// Result: "MTIz"

2. HMAC Signed (Secure, prevents tampering)

String payload = "123";
String signature = HMAC_SHA256(payload, secretKey);
String cursor = Base64.encode(payload + ":" + signature);
// Result: "MTIzOmFiY2RlZmdoaWprbG1ub3A="

// Verification
String[] parts = Base64.decode(cursor).split(":");
if (!parts[1].equals(HMAC_SHA256(parts[0], secretKey))) {
    throw new InvalidCursorException();
}

3. Composite Cursor (Multiple fields)

// For ordering by timestamp + id
{
  "timestamp": "2024-01-15T10:30:00Z",
  "id": 123
}
// Encoded: "eyJ0aW1lc3RhbXAiOiIyMDI0LTAxLTE1VDEwOjMwOjAwWiIsImlkIjoxMjN9"

Advantages ✅

1. Consistent Performance

   • Same speed for page 1 and page 1000
   • Uses index seeks (O(log n)), not scans (O(n))

2. No Duplicate/Missing Items

   • Even if data changes, cursor tracks exact position
   • Stable iteration through dataset

3. Efficient for Real-time Data

   • Perfect for feeds with constant updates
   • No skipped or duplicate posts

4. Scalable

   • Works with millions/billions of records
   • Performance doesn't degrade with dataset size

5. No COUNT Query Needed

   • Don't need to know total count
   • Faster queries

Disadvantages ❌

1. No Random Access

   • Can't jump to page 50 directly
   • Must go through pages sequentially

2. No Total Count

   • Don't know how many total pages
   • Can't show "Page X of Y"

3. Can't Go Backwards

   • Only forward pagination
   • Need separate cursor for reverse pagination

4. More Complex

   • Requires cursor encoding/decoding
   • Client must track cursor state

5. Limited UI Options

   • Only "Next" button, no page numbers
   • Can't show progress bar

Use Cases ✅

Best suited for:

1. Social Media Feeds

   • Twitter, Facebook, Instagram timelines
   • Infinite scroll
   • Real-time updates

2. Activity Logs

   • Audit logs
   • System events
   • User activity history

3. Chat/Messaging

   • Message history
   • Conversation threads
   • Real-time updates

4. API Endpoints

   • RESTful APIs with large datasets
   • GraphQL connections
   • Mobile apps (bandwidth sensitive)

5. Real-time Analytics

   • Live dashboards
   • Monitoring systems
   • Stream processing

Example Scenarios:

• Instagram photo feed
• Twitter timeline
• Slack message history
• GitHub notifications
• E-commerce order history

---

Keyset Pagination

What is it?

A variant of cursor-based pagination that uses composite keys (multiple columns) for ordering and filtering.

How it Works

SQL Mechanism

-- Order by created_at, then id for tie-breaking
-- Page 1
SELECT * FROM articles 
ORDER BY created_at DESC, id DESC 
LIMIT 10;
-- Last item: created_at='2024-01-15 10:30:00', id=123

-- Page 2
SELECT * FROM articles 
WHERE (created_at < '2024-01-15 10:30:00') 
   OR (created_at = '2024-01-15 10:30:00' AND id < 123)
ORDER BY created_at DESC, id DESC 
LIMIT 10;

Why Composite Keys?

Problem: Single column isn't unique
Articles with same timestamp:
- created_at='2024-01-15 10:30:00', id=120
- created_at='2024-01-15 10:30:00', id=121  ← Same timestamp!
- created_at='2024-01-15 10:30:00', id=122
- created_at='2024-01-15 10:30:00', id=123

Solution: Add unique tiebreaker (id)
WHERE created_at = '2024-01-15 10:30:00' AND id < 123

Implementation

Composite Cursor DTO

@Getter
@AllArgsConstructor
public class CompositeKeyCursor {
    private LocalDateTime createdAt;
    private Long id;

    // Encode to JSON + Base64
    public String encode() {
        try {
            ObjectMapper mapper = new ObjectMapper();
            mapper.registerModule(new JavaTimeModule());
            String json = mapper.writeValueAsString(this);
            return Base64.getEncoder().encodeToString(json.getBytes());
        } catch (Exception e) {
            throw new RuntimeException("Failed to encode cursor", e);
        }
    }

    // Decode from Base64 + JSON
    public static CompositeKeyCursor decode(String encoded) {
        try {
            byte[] decoded = Base64.getDecoder().decode(encoded);
            ObjectMapper mapper = new ObjectMapper();
            mapper.registerModule(new JavaTimeModule());
            return mapper.readValue(decoded, CompositeKeyCursor.class);
        } catch (Exception e) {
            throw new IllegalArgumentException("Invalid cursor", e);
        }
    }
}

Repository

public CursorPage<Article> findWithKeysetPagination(
    CompositeKeyCursor cursor, 
    int pageSize
) {
    QArticle article = QArticle.article;
    BooleanBuilder builder = new BooleanBuilder();

    if (cursor != null) {
        // (created_at < cursor.createdAt) OR 
        // (created_at = cursor.createdAt AND id < cursor.id)
        builder.and(
            article.createdAt.lt(cursor.getCreatedAt())
            .or(
                article.createdAt.eq(cursor.getCreatedAt())
                .and(article.id.lt(cursor.getId()))
            )
        );
    }

    List<Article> results = queryFactory
        .selectFrom(article)
        .where(builder)
        .orderBy(
            article.createdAt.desc(),
            article.id.desc()
        )
        .limit(pageSize + 1)
        .fetch();

    boolean hasMore = results.size() > pageSize;
    if (hasMore) {
        results.remove(results.size() - 1);
    }

    String nextCursor = null;
    if (!results.isEmpty() && hasMore) {
        Article last = results.get(results.size() - 1);
        CompositeKeyCursor compositeCursor = new CompositeKeyCursor(
            last.getCreatedAt(),
            last.getId()
        );
        nextCursor = compositeCursor.encode();
    }

    return new CursorPage<>(results, nextCursor, hasMore);
}

Database Index

-- Composite index matching the ORDER BY
CREATE INDEX idx_articles_created_id ON articles(created_at DESC, id DESC);

Advantages ✅

1. All Cursor Benefits + More flexible ordering
2. Natural Sorting - Order by date, name, priority, etc.
3. Handles Duplicates - Composite key ensures uniqueness
4. Index Optimized - Uses composite indexes efficiently

Disadvantages ❌

1. Complex Cursor Logic - Multi-field comparisons
2. Larger Cursors - More data to encode
3. Index Requirements - Need composite indexes
4. Complex WHERE Clause - More difficult to understand

Use Cases ✅

Best suited for:

1. Time-ordered Feeds

   • News articles ordered by publication date
   • Blog posts by created date
   • Events by timestamp

2. Priority-based Lists

   • Tasks ordered by priority, then due date
   • Tickets by severity, then created date

3. Alphabetical Listings

   • User directory by name, then ID
   • Product catalog by category, then name

Example Scenarios:

• Reddit posts (score + timestamp)
• Hacker News (points + time)
• E-commerce products (popularity + name)

---

Seek Method

What is it?

An optimization technique that uses indexed columns to efficiently skip to a specific position without OFFSET.

How it Works

Traditional OFFSET (Slow)

-- Page 1000 (skip 999,000 rows!)
SELECT * FROM users 
ORDER BY last_name, first_name 
LIMIT 1000 OFFSET 999000;

-- Database scans 999,000 rows, then returns 1000

Seek Method (Fast)

-- Page 1000 (no OFFSET!)
SELECT * FROM users 
WHERE (last_name, first_name) > ('Smith', 'John')
ORDER BY last_name, first_name 
LIMIT 1000;

-- Database seeks directly to ('Smith', 'John') using index

Implementation

Using Row Value Comparisons

public List<User> findUsersSeekMethod(
    String lastNameCursor,
    String firstNameCursor,
    int pageSize
) {
    QUser user = QUser.user;
    BooleanBuilder builder = new BooleanBuilder();

    if (lastNameCursor != null && firstNameCursor != null) {
        // PostgreSQL/MySQL: Row value comparison
        // (last_name, first_name) > (?, ?)
        builder.and(
            user.lastName.gt(lastNameCursor)
            .or(
                user.lastName.eq(lastNameCursor)
                .and(user.firstName.gt(firstNameCursor))
            )
        );
    }

    return queryFactory
        .selectFrom(user)
        .where(builder)
        .orderBy(user.lastName.asc(), user.firstName.asc())
        .limit(pageSize)
        .fetch();
}

Native SQL (PostgreSQL)

-- Row constructor syntax (PostgreSQL only)
SELECT * FROM users 
WHERE (last_name, first_name) > ('Smith', 'John')
ORDER BY last_name, first_name 
LIMIT 1000;

Database Index

CREATE INDEX idx_users_name ON users(last_name, first_name);

Advantages ✅

1. Extremely Fast - Direct index seek
2. Constant Performance - Same speed for any position
3. Index-only Scans - May not need to access table
4. Works with Existing Indexes - Leverages database optimization

Disadvantages ❌

1. Database-Specific - Row value syntax varies
2. Complex for Multiple Columns - Nested OR conditions
3. Still Forward-Only - Can't jump to arbitrary positions
4. Requires Unique Ordering - Need tie-breaking column

Use Cases ✅

Same as cursor-based pagination, but with:

• Very large datasets (100M+ rows)
• High-performance requirements
• Database supports row value comparisons

---

Comparison and When to Use Each

Performance Comparison

Pagination Type	Page 1	Page 10	Page 100	Page 1000
Offset-Based	5ms	15ms	150ms	1500ms
Cursor-Based	5ms	5ms	5ms	5ms
Keyset	5ms	5ms	5ms	5ms
Seek Method	3ms	3ms	3ms	3ms

Feature Comparison

Feature	Offset	Cursor	Keyset	Seek
Random Access	✅	❌	❌	❌
Total Count	✅	❌	❌	❌
Page Numbers	✅	❌	❌	❌
Constant Performance	❌	✅	✅	✅
Data Consistency	❌	✅	✅	✅
Scalability	❌	✅	✅	✅
Implementation Complexity	Easy	Medium	Medium	Hard
Real-time Data	❌	✅	✅	✅
Backward Navigation	✅	❌*	❌*	❌*

*Requires separate implementation

Decision Tree

Start
  |
  ├─ Need page numbers? (Admin panel, reports)
  │   └─ YES → Offset-Based
  │
  ├─ Dataset < 10,000 rows?
  │   └─ YES → Offset-Based (simple enough)
  │
  ├─ Need to jump to arbitrary pages?
  │   └─ YES → Offset-Based
  │
  ├─ Real-time feed? (social media, chat)
  │   └─ YES → Cursor-Based
  │
  ├─ Need custom sorting? (by date, priority, name)
  │   ├─ YES → Keyset Pagination
  │   └─ NO → Cursor-Based (ID only)
  │
  ├─ Dataset > 100M rows? Ultra high performance?
  │   └─ YES → Seek Method
  │
  └─ Default choice → Cursor-Based

Use Case Matrix

Scenario	Recommended	Why
Admin dashboard	Offset	Need page numbers, small dataset
Product catalog (< 5000)	Offset	Simple, users rarely go deep
E-commerce search results	Offset	Users expect page numbers
Twitter feed	Cursor	Real-time, infinite scroll
Instagram photos	Cursor	Infinite scroll, constant updates
Chat messages	Cursor	Chronological, real-time
Blog posts by date	Keyset	Order by timestamp + ID
Task list by priority	Keyset	Order by priority + ID
Audit logs (billions)	Seek	Extreme scale, high performance
GitHub activity	Cursor	Real-time updates
Financial transactions	Cursor	Data integrity critical

---

Best Practices

1. Always Add an ORDER BY Clause

// ❌ BAD: Random order
queryFactory.selectFrom(product).limit(10).fetch();

// ✅ GOOD: Deterministic order
queryFactory.selectFrom(product)
    .orderBy(product.id.asc())
    .limit(10)
    .fetch();

2. Use Indexes for Pagination Columns

-- For offset-based
CREATE INDEX idx_products_id ON products(id);

-- For cursor-based with timestamp
CREATE INDEX idx_posts_created_id ON posts(created_at DESC, id DESC);

-- For keyset with multiple columns
CREATE INDEX idx_articles_score_time ON articles(score DESC, created_at DESC, id DESC);

3. Validate Page Parameters

public Page<Product> getProducts(int page, int size) {
    // Prevent abuse
    if (size > 100) {
        size = 100; // Max page size
    }
    if (size < 1) {
        size = 10; // Default
    }
    if (page < 0) {
        page = 0;
    }

    return repository.findAll(PageRequest.of(page, size));
}

4. Cache Total Count for Offset Pagination

@Cacheable("product-count")
public long getTotalProducts() {
    return productRepository.count();
}

5. Encode Cursors as Opaque Tokens

// ❌ BAD: Exposes implementation
{
  "nextCursor": 12345
}

// ✅ GOOD: Opaque, can change implementation
{
  "nextCursor": "eyJpZCI6MTIzNDUsInRzIjoxNzA3MTIzNDU2fQ=="
}

6. Document Pagination in API

/**
 * Get products with cursor-based pagination
 * 
 * @param cursor Opaque cursor from previous response's nextCursor field.
 *               Use exact value - do not construct manually.
 * @param pageSize Number of items per page (1-100, default 10)
 * @return CursorPage containing products and next cursor
 */
@GetMapping("/products")
public CursorPage<Product> getProducts(
    @RequestParam(required = false) String cursor,
    @RequestParam(defaultValue = "10") int pageSize
) { ... }

7. Handle Edge Cases

// Empty results
if (results.isEmpty()) {
    return new CursorPage<>(Collections.emptyList(), null, false);
}

// Invalid cursor
try {
    Long cursorId = decodeCursor(cursor);
} catch (Exception e) {
    throw new InvalidCursorException("Malformed cursor. Request first page.");
}

// Deleted records
// Cursor points to deleted item → still works, jumps to next available

8. Monitor Performance

@Slf4j
@Service
public class ProductService {

    public Page<Product> getProducts(int page, int size) {
        long start = System.currentTimeMillis();

        Page<Product> result = repository.findAll(
            PageRequest.of(page, size)
        );

        long duration = System.currentTimeMillis() - start;

        if (duration > 1000) {
            log.warn("Slow pagination: page={}, size={}, time={}ms", 
                     page, size, duration);
        }

        return result;
    }
}

9. Implement Rate Limiting

// Prevent pagination abuse
@RateLimiter(name = "pagination", fallbackMethod = "rateLimitFallback")
@GetMapping("/products")
public Page<Product> getProducts(...) { ... }

10. Choose Appropriate Page Size

Mobile: 10-20 items
Web: 20-50 items
API: 50-100 items
Admin: 100-500 items

Balance: Performance vs UX

---

Summary

Quick Reference

When to use Offset-Based:

• ✅ Small datasets (< 10,000)
• ✅ Need page numbers
• ✅ Simple admin tools
• ❌ Large datasets
• ❌ Real-time data

When to use Cursor-Based:

• ✅ Large datasets (10,000+)
• ✅ Infinite scroll
• ✅ Real-time feeds
• ✅ Mobile apps
• ❌ Need page numbers
• ❌ Need random access

When to use Keyset:

• ✅ Custom ordering (date, priority)
• ✅ Large datasets
• ✅ Complex sorting
• ❌ Simple ID-based pagination

When to use Seek Method:

• ✅ Extreme scale (100M+ rows)
• ✅ Ultra-high performance
• ✅ Database supports row values
• ❌ Cross-database compatibility needed

Key Takeaways

1. Pagination is essential for performance, UX, and scalability
2. Offset-based is simple but slow for large datasets
3. Cursor-based is fast and scalable but can't jump pages
4. Always use ORDER BY for deterministic results
5. Index your pagination columns for performance
6. Choose based on use case, not what's easiest

---

Additional Resources

Sample Projects

• Spring Data Pagination Examples
• GraphQL Cursor Connections

Further Reading

• Efficient Pagination in PostgreSQL
• MySQL LIMIT Optimization
• Use the Index, Luke! - Pagination

---

Document Version: 1.0

Last Updated: February 2026

Author: Technical Documentation