Pagination Deep Dive

Master advanced pagination strategies for scalable, high-performance APIs. Learn when to use offset, cursor, or keyset pagination, and how to handle filtering, sorting, and large datasets efficiently.

Why Pagination Matters

Pagination is critical for building scalable APIs that handle large datasets. Without proper pagination, your API will suffer from memory exhaustion, slow response times, poor user experience, and database strain. A production API serving 10,000 records per request can consume 100x more memory and have 50x longer response times compared to paginating with 100 records per page.

Common Problems Without Pagination

Memory exhaustion from loading thousands of records
Slow serialization and network transfer times
Poor UX as users wait for unneeded data
Database strain from full table scans
Inconsistent results when data changes mid-query
Server crashes under high traffic loads

What You'll Learn

Offset-based pagination (LIMIT/OFFSET)
Cursor-based pagination for real-time feeds
Keyset pagination for maximum performance
Pagination with filtering and dynamic sorting
Relay-style Connection pattern (GraphQL)
Database indexing strategies
Performance optimization and caching
When to use each pagination strategy

This lesson builds on Lesson 3 (basic pagination in request/response design) and integrates with Lesson 6 (error handling for invalid pagination), Lesson 8 (rate limiting paginated endpoints), and Lesson 13(testing pagination logic). You'll master the pagination strategies used by Twitter, Instagram, and LinkedIn to serve billions of records efficiently.

Prerequisites

This lesson builds upon the basic pagination concepts introduced in Lesson 3: Request & Response Design. If you haven't covered basic pagination yet, review that section first.

Offset-Based Pagination (LIMIT/OFFSET)

The most common pagination approach uses LIMIT and OFFSET (or SKIP in some databases). Simple to implement but has performance pitfalls.

How It Works

# SQL Query
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- Page 3 (skip 40, take 20)

# API Request
GET /api/users?page=3&page_size=20
# or
GET /api/users?limit=20&offset=40

FastAPI Implementation

from fastapi import FastAPI, Query
from typing import List
from pydantic import BaseModel

app = FastAPI()

class User(BaseModel):
    id: int
    username: str
    email: str
    created_at: str

class PaginatedResponse(BaseModel):
    data: List[User]
    total: int
    page: int
    page_size: int
    total_pages: int

@app.get("/users", response_model=PaginatedResponse)
async def get_users(
    page: int = Query(1, ge=1, description="Page number (1-indexed)"),
    page_size: int = Query(20, ge=1, le=100, description="Items per page")
):
    # Calculate offset
    offset = (page - 1) * page_size

    # Query database (example with SQLAlchemy)
    users = db.query(User).order_by(
        User.created_at.desc()
    ).limit(page_size).offset(offset).all()

    # Get total count
    total = db.query(User).count()

    return PaginatedResponse(
        data=users,
        total=total,
        page=page,
        page_size=page_size,
        total_pages=(total + page_size - 1) // page_size  # Ceiling division
    )

Response Format

{
  "data": [
    {
      "id": 41,
      "username": "user41",
      "email": "user41@example.com",
      "created_at": "2024-01-15T10:30:00Z"
    },
    // ... 19 more users
  ],
  "total": 1543,
  "page": 3,
  "page_size": 20,
  "total_pages": 78
}

Pros and Cons

Advantages

Simple to implement
Easy to jump to any page
Total count enables "showing X of Y"
Works well for small-medium datasets

Disadvantages

Slow for large offsets (OFFSET 10000000)
Inconsistent results if data changes
Database must scan skipped rows
COUNT(*) query can be expensive

Performance Warning

On page 1000 with page_size=100, OFFSET is 99,900. The database must scan and skip 99,900 rows before returning results. This gets progressively slower as offset increases. For datasets over 100k rows, consider cursor-based or keyset pagination.

Cursor-Based Pagination

Cursor-based pagination uses an opaque token (cursor) to mark the position in the dataset. The cursor typically encodes the last seen record's unique identifier or timestamp.

How It Works

# First request
GET /api/users?limit=20

Response:
{
  "data": [...],
  "next_cursor": "eyJpZCI6MjAsImNyZWF0ZWRfYXQiOiIyMDI0LTAxLTE1VDEwOjMwOjAwWiJ9",
  "has_more": true
}

# Next page request
GET /api/users?limit=20&cursor=eyJpZCI6MjAsImNyZWF0ZWRfYXQiOiIyMDI0LTAxLTE1VDEwOjMwOjAwWiJ9

FastAPI Implementation

import base64
import json
from typing import Optional

class CursorPaginatedResponse(BaseModel):
    data: List[User]
    next_cursor: Optional[str] = None
    has_more: bool

def encode_cursor(user_id: int, created_at: str) -> str:
    """Encode cursor from last record's ID and timestamp"""
    cursor_data = {"id": user_id, "created_at": created_at}
    json_str = json.dumps(cursor_data)
    return base64.urlsafe_b64encode(json_str.encode()).decode()

def decode_cursor(cursor: str) -> dict:
    """Decode cursor to get ID and timestamp"""
    json_str = base64.urlsafe_b64decode(cursor.encode()).decode()
    return json.loads(json_str)

@app.get("/users", response_model=CursorPaginatedResponse)
async def get_users_cursor(
    limit: int = Query(20, ge=1, le=100),
    cursor: Optional[str] = None
):
    query = db.query(User).order_by(User.created_at.desc(), User.id.desc())

    # Apply cursor filter
    if cursor:
        cursor_data = decode_cursor(cursor)
        query = query.filter(
            or_(
                User.created_at < cursor_data["created_at"],
                and_(
                    User.created_at == cursor_data["created_at"],
                    User.id < cursor_data["id"]
                )
            )
        )

    # Fetch limit + 1 to check if more pages exist
    users = query.limit(limit + 1).all()

    has_more = len(users) > limit
    if has_more:
        users = users[:limit]  # Remove extra record

    # Generate next cursor from last record
    next_cursor = None
    if has_more and users:
        last_user = users[-1]
        next_cursor = encode_cursor(last_user.id, last_user.created_at)

    return CursorPaginatedResponse(
        data=users,
        next_cursor=next_cursor,
        has_more=has_more
    )

When to Use Cursor Pagination

Real-time feeds: Social media timelines, activity streams
Infinite scroll UIs: Mobile apps, continuous loading
Large datasets: Millions of records where offset becomes too slow
Data changes frequently: Insertions/deletions won't cause duplicate/skipped records

Advantages

Consistent performance regardless of page depth
No duplicate/skipped records on data changes
Scales to billions of records
No need for expensive COUNT(*) queries

Disadvantages

Can't jump to arbitrary page
No total count (can't show "page X of Y")
More complex to implement
Cursors can be large if encoding multiple fields

Keyset Pagination (Seek Method)

Keyset pagination (also called "seek method" or "index pagination") uses indexed columns in WHERE clauses instead of OFFSET. This is the most performant approach for large datasets.

How It Works

Instead of skipping rows, use the last record's key column(s) to filter the next batch:

# Traditional offset approach (SLOW for large offsets)
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 100000;
-- Database must scan and discard 100,000 rows

# Keyset approach (FAST - uses index directly)
SELECT * FROM users
WHERE id > 100020  -- Last ID from previous page
ORDER BY id
LIMIT 20;
-- Database uses index to jump directly to id > 100020

FastAPI Implementation

from typing import Optional

class KeysetPaginatedResponse(BaseModel):
    data: List[User]
    last_id: Optional[int] = None  # Simple approach: expose the key
    has_more: bool

@app.get("/users", response_model=KeysetPaginatedResponse)
async def get_users_keyset(
    limit: int = Query(20, ge=1, le=100),
    last_id: Optional[int] = None
):
    query = db.query(User).order_by(User.id.asc())

    # Filter: only records after last_id
    if last_id:
        query = query.filter(User.id > last_id)

    # Fetch limit + 1 to check if more pages exist
    users = query.limit(limit + 1).all()

    has_more = len(users) > limit
    if has_more:
        users = users[:limit]

    return KeysetPaginatedResponse(
        data=users,
        last_id=users[-1].id if users else None,
        has_more=has_more
    )

Multi-Column Keyset (Composite Key)

When ordering by multiple columns (e.g., created_at + id for stable sorting):

@app.get("/users")
async def get_users_composite_keyset(
    limit: int = Query(20, ge=1, le=100),
    last_created_at: Optional[str] = None,
    last_id: Optional[int] = None
):
    query = db.query(User).order_by(
        User.created_at.desc(),
        User.id.desc()  # Tie-breaker for same timestamp
    )

    # Composite filter using row value comparison
    if last_created_at and last_id:
        query = query.filter(
            or_(
                User.created_at < last_created_at,
                and_(
                    User.created_at == last_created_at,
                    User.id < last_id
                )
            )
        )

    users = query.limit(limit + 1).all()
    has_more = len(users) > limit
    if has_more:
        users = users[:limit]

    return {
        "data": users,
        "last_created_at": users[-1].created_at if users else None,
        "last_id": users[-1].id if users else None,
        "has_more": has_more
    }

Performance Benchmark

On a table with 10 million rows:

OFFSET 1,000,000: ~2.5 seconds
Keyset WHERE id > 1000000: ~0.003 seconds (800x faster!)

Keyset pagination maintains constant performance regardless of dataset size or page depth.

Requirements & Considerations

Indexed columns: The keyset column(s) MUST have a database index
Unique ordering: Include a unique column (like ID) as tie-breaker
Stable sort: Order must be deterministic and repeatable
Forward-only: Can't easily go backwards (would need separate last_id logic)

Pagination with Filtering and Sorting

Real-world APIs need pagination combined with filtering and dynamic sorting. This adds complexity to cursor and keyset approaches.

Challenges

Dynamic sort orders: Cursor/keyset must adapt to sort column
Filter changes: Cursors become invalid if filter params change
Index selection: Database needs indexes on filtered + sorted columns

Offset Pagination with Filters

Simple but works well for moderate datasets:

from enum import Enum

class UserRole(str, Enum):
    ADMIN = "admin"
    USER = "user"

class SortField(str, Enum):
    CREATED_AT = "created_at"
    USERNAME = "username"
    EMAIL = "email"

@app.get("/users")
async def get_users_filtered(
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    role: Optional[UserRole] = None,
    search: Optional[str] = None,
    sort_by: SortField = SortField.CREATED_AT,
    sort_desc: bool = True
):
    query = db.query(User)

    # Apply filters
    if role:
        query = query.filter(User.role == role)
    if search:
        query = query.filter(
            or_(
                User.username.ilike(f"%{search}%"),
                User.email.ilike(f"%{search}%")
            )
        )

    # Apply sorting
    sort_column = getattr(User, sort_by.value)
    if sort_desc:
        query = query.order_by(sort_column.desc())
    else:
        query = query.order_by(sort_column.asc())

    # Always add ID as tie-breaker for stable sort
    query = query.order_by(User.id.desc() if sort_desc else User.id.asc())

    # Pagination
    total = query.count()
    offset = (page - 1) * page_size
    users = query.limit(page_size).offset(offset).all()

    return {
        "data": users,
        "total": total,
        "page": page,
        "page_size": page_size,
        "total_pages": (total + page_size - 1) // page_size
    }

Cursor Pagination with Dynamic Sort

Encode the sort field and value in the cursor:

def encode_cursor(record_id: int, sort_field: str, sort_value: any) -> str:
    cursor_data = {
        "id": record_id,
        "sort_field": sort_field,
        "sort_value": str(sort_value)  # Serialize value
    }
    json_str = json.dumps(cursor_data)
    return base64.urlsafe_b64encode(json_str.encode()).decode()

@app.get("/users")
async def get_users_cursor_filtered(
    limit: int = Query(20, ge=1, le=100),
    cursor: Optional[str] = None,
    role: Optional[UserRole] = None,
    sort_by: SortField = SortField.CREATED_AT,
    sort_desc: bool = True
):
    query = db.query(User)

    # Apply filters
    if role:
        query = query.filter(User.role == role)

    # Get sort column
    sort_column = getattr(User, sort_by.value)

    # Apply cursor filter
    if cursor:
        cursor_data = decode_cursor(cursor)

        # Validate cursor matches current sort
        if cursor_data["sort_field"] != sort_by.value:
            raise HTTPException(
                status_code=400,
                detail="Sort parameter changed - cursor is invalid"
            )

        # Apply cursor filter based on sort direction
        if sort_desc:
            query = query.filter(
                or_(
                    sort_column < cursor_data["sort_value"],
                    and_(
                        sort_column == cursor_data["sort_value"],
                        User.id < cursor_data["id"]
                    )
                )
            )
        else:
            query = query.filter(
                or_(
                    sort_column > cursor_data["sort_value"],
                    and_(
                        sort_column == cursor_data["sort_value"],
                        User.id > cursor_data["id"]
                    )
                )
            )

    # Apply ordering
    if sort_desc:
        query = query.order_by(sort_column.desc(), User.id.desc())
    else:
        query = query.order_by(sort_column.asc(), User.id.asc())

    users = query.limit(limit + 1).all()
    has_more = len(users) > limit
    if has_more:
        users = users[:limit]

    next_cursor = None
    if has_more and users:
        last_user = users[-1]
        sort_value = getattr(last_user, sort_by.value)
        next_cursor = encode_cursor(last_user.id, sort_by.value, sort_value)

    return {
        "data": users,
        "next_cursor": next_cursor,
        "has_more": has_more
    }

Cursor Invalidation

When using cursors with filtering/sorting, changing filter or sort parameters invalidates previous cursors. Either:

Detect and reject mismatched cursors (shown above)
Include filter/sort params in cursor encoding (makes cursors larger)
Use stateful sessions to track filter context

Best Practices

Always add ID as tie-breaker: Ensures stable, deterministic sorting
Create composite indexes: Index on (filter_column, sort_column, id)
Validate cursor context: Reject cursors if filters/sort changed
Limit sort options: Only allow sorting on indexed columns
Document behavior: Make cursor invalidation clear in API docs

Relay-Style Connection Pagination

The Relay GraphQL specification popularized a standardized pagination pattern using "connections", "edges", and "nodes". This pattern works well for REST APIs too.

Connection Pattern Structure

{
  "edges": [
    {
      "node": { /* The actual record */ },
      "cursor": "opaque-cursor-string"
    }
  ],
  "pageInfo": {
    "hasNextPage": true,
    "hasPreviousPage": false,
    "startCursor": "first-cursor",
    "endCursor": "last-cursor"
  },
  "totalCount": 1543  // Optional
}

Pydantic Models

from typing import List, Optional, Generic, TypeVar
from pydantic import BaseModel

T = TypeVar('T')

class PageInfo(BaseModel):
    """Pagination metadata"""
    has_next_page: bool
    has_previous_page: bool
    start_cursor: Optional[str] = None
    end_cursor: Optional[str] = None

class Edge(BaseModel, Generic[T]):
    """Edge wraps a node with its cursor"""
    node: T
    cursor: str

class Connection(BaseModel, Generic[T]):
    """Connection wrapper for paginated results"""
    edges: List[Edge[T]]
    page_info: PageInfo
    total_count: Optional[int] = None  # Expensive, make optional

# Usage with User model
class UserConnection(Connection[User]):
    pass

FastAPI Implementation

@app.get("/users", response_model=UserConnection)
async def get_users_connection(
    first: Optional[int] = Query(None, ge=1, le=100, description="First N items"),
    after: Optional[str] = Query(None, description="Cursor to start after"),
    last: Optional[int] = Query(None, ge=1, le=100, description="Last N items"),
    before: Optional[str] = Query(None, description="Cursor to end before"),
    include_total: bool = Query(False, description="Include expensive total count")
):
    # Validate: can't use first+last or after+before together
    if (first and last) or (after and before):
        raise HTTPException(400, "Use either first+after OR last+before")

    # Determine direction
    is_forward = first is not None
    limit = first if is_forward else (last if last else 20)

    query = db.query(User).order_by(User.created_at.desc(), User.id.desc())

    # Apply cursor filter
    if after:
        cursor_data = decode_cursor(after)
        query = query.filter(
            or_(
                User.created_at < cursor_data["created_at"],
                and_(
                    User.created_at == cursor_data["created_at"],
                    User.id < cursor_data["id"]
                )
            )
        )
    elif before:
        cursor_data = decode_cursor(before)
        query = query.filter(
            or_(
                User.created_at > cursor_data["created_at"],
                and_(
                    User.created_at == cursor_data["created_at"],
                    User.id > cursor_data["id"]
                )
            )
        )

    # Fetch limit + 1 to detect has_more
    users = query.limit(limit + 1).all()

    # Determine if more pages exist
    has_more = len(users) > limit
    if has_more:
        users = users[:limit]

    # If backward pagination, reverse results
    if not is_forward:
        users = list(reversed(users))

    # Build edges with cursors
    edges = [
        Edge(
            node=user,
            cursor=encode_cursor(user.id, user.created_at)
        )
        for user in users
    ]

    # Build page info
    page_info = PageInfo(
        has_next_page=has_more if is_forward else (before is not None),
        has_previous_page=(after is not None) if is_forward else has_more,
        start_cursor=edges[0].cursor if edges else None,
        end_cursor=edges[-1].cursor if edges else None
    )

    # Optional total count
    total_count = None
    if include_total:
        total_count = db.query(User).count()

    return UserConnection(
        edges=edges,
        page_info=page_info,
        total_count=total_count
    )

Benefits of Connection Pattern

Standardized: Well-known pattern from GraphQL ecosystem
Bidirectional: Supports forward (first/after) and backward (last/before) pagination
Cursor per item: Each edge has its own cursor for precise positioning
Rich metadata: PageInfo provides clear navigation state
Framework support: Many GraphQL/REST libraries support this pattern

When to Use Connection Pattern

Relay-style connections add overhead (cursor per item, nested structure). Use when you need bidirectional pagination or want GraphQL compatibility. For simple forward-only pagination, simpler formats suffice.

Performance Implications & Database Indexes

Pagination performance depends heavily on database indexes. Without proper indexes, even keyset pagination becomes slow.

Index Strategy

1. Single-Column Pagination

-- For: ORDER BY created_at DESC LIMIT 20 OFFSET 100
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- For: WHERE id > 12345 ORDER BY id LIMIT 20
CREATE INDEX idx_users_id ON users(id);  -- Usually auto-created as primary key

2. Multi-Column Pagination with Tie-Breaker

-- For: ORDER BY created_at DESC, id DESC
CREATE INDEX idx_users_created_at_id ON users(created_at DESC, id DESC);

-- Database can use this index for:
-- WHERE created_at < '2024-01-15' OR (created_at = '2024-01-15' AND id < 100)

3. Filtered Pagination

-- For: WHERE role = 'admin' ORDER BY created_at DESC, id DESC
CREATE INDEX idx_users_role_created_at_id ON users(role, created_at DESC, id DESC);

-- For: WHERE status IN ('active', 'pending') ORDER BY updated_at DESC
CREATE INDEX idx_users_status_updated_at ON users(status, updated_at DESC);

4. Search + Pagination

-- For ILIKE searches: WHERE username ILIKE '%search%'
-- Use full-text search indexes instead of ILIKE

-- PostgreSQL: GIN index for full-text search
CREATE INDEX idx_users_username_gin ON users USING gin(username gin_trgm_ops);

-- Or use dedicated search column with tsvector
ALTER TABLE users ADD COLUMN search_vector tsvector;
CREATE INDEX idx_users_search ON users USING gin(search_vector);

Query Performance Analysis

Query Pattern	Without Index	With Index	Index Type
`OFFSET 100000`	~3s (full scan)	~1.2s (still scans offset)	B-tree on ORDER BY column
`WHERE id > 100000`	~2.5s (full scan)	~0.003s	Primary key (automatic)
`WHERE created_at < X`	~2.8s	~0.005s	B-tree on created_at
`COUNT(*)` on 10M rows	~5s	~1s (seq scan)	No index helps COUNT(*)
`WHERE role = 'X' AND ...`	~3.5s	~0.01s	Composite (role, sort_col)

COUNT(*) Optimization Strategies

Total count queries are expensive on large tables. Strategies to handle this:

# 1. Make total count optional
GET /api/users?page=1&page_size=20&include_total=false

# 2. Cache count with short TTL
@cache(ttl=60)  # Cache for 60 seconds
def get_total_users():
    return db.query(User).count()

# 3. Use approximate counts for large tables
# PostgreSQL: reltuples estimate
SELECT reltuples::bigint FROM pg_class WHERE relname = 'users';

# 4. Pre-compute counts in background job
# Update users_count table every 5 minutes via Celery/cron

# 5. Show "10,000+" instead of exact count after threshold
total = min(db.query(User).limit(10001).count(), 10000)
display = f"{total}+" if total == 10000 else str(total)

Best Practice: Avoid Total Counts

For large datasets, avoid showing total counts entirely. Use "Load More" buttons or infinite scroll instead of "Page X of Y". Twitter, Instagram, and LinkedIn all use cursor pagination without total counts for performance reasons.

Caching Strategies

Cache first page: Most users only see page 1 (70-80% of traffic)
Short TTL: 30-60 second cache prevents overwhelming DB on high traffic
Cursor-based cache keys: Cache by cursor + filters, not by page number
Invalidate on writes: Clear cache when new records added (if needed)

Pagination Strategy Comparison

Feature	Offset/Page	Cursor	Keyset
Implementation Complexity	⭐ Simple	⭐⭐ Moderate	⭐⭐ Moderate
Performance (Small Dataset)	Fast (<1ms)	Fast (<1ms)	Fast (<1ms)
Performance (Large Dataset)	Slow (seconds at high offsets)	Fast (<5ms)	Fast (<5ms)
Consistency (Data Changes)	Duplicates/skips possible	Consistent	Consistent
Jump to Arbitrary Page	✅ Yes	❌ No	❌ No
Total Count Available	✅ Yes	❌ No	❌ No
Backward Pagination	✅ Easy	⚠️ Complex	⚠️ Complex
Dynamic Sorting	✅ Easy	⚠️ Complex	⚠️ Requires planning
Database Load (Page 1000)	High (scans 100k rows)	Low (index seek)	Low (index seek)
Best Use Case	Small datasets, admin panels, simple reports	Infinite scroll, social feeds, real-time data	Large datasets, analytics, performance-critical APIs

Best Practices & Recommendations

Decision Tree: Which Pagination to Use?

Use Offset/Page if:

Dataset is small (<10k records)
Users need "page X of Y" display
Need to jump to arbitrary pages
Admin panels, reports, or internal tools
Simple implementation is priority

Use Cursor if:

Building infinite scroll or "load more" UIs
Real-time feeds (social media, activity streams)
Data changes frequently
GraphQL API or need Relay compatibility
Large datasets but don't need page jumping

Use Keyset if:

Dataset is large (>100k records)
Performance is critical
Sort order is stable/predictable
Analytics, exports, or data processing pipelines
Forward-only navigation is acceptable

Implementation Checklist

✅ Always:

Validate and limit page_size/limit (max 100)
Add ID as tie-breaker for stable sorting
Create database indexes on sort/filter columns
Document cursor invalidation behavior
Return consistent response format
Include metadata (has_more, next_cursor, etc.)

⚠️ Avoid:

Unbounded queries without pagination
COUNT(*) on every request for large tables
Exposing internal IDs if security concern
Complex cursor logic without validation
Pagination without ordering (non-deterministic)

Common Mistakes

❌ No Tie-Breaker

ORDER BY created_at

Records with same timestamp have non-deterministic order. Always add ID as tie-breaker.

✅ With Tie-Breaker

ORDER BY created_at DESC, id DESC

Stable, deterministic sorting even for records with identical timestamps.

❌ No Index on Filter

WHERE role='admin'
ORDER BY created_at

Full table scan on every query. Slow on large tables.

✅ Composite Index

CREATE INDEX idx_role_created
ON users(role, created_at DESC, id)

Index covers filter + sort. Fast seeks, no table scans.

Testing Strategy

Edge cases: Empty results, single record, exact page size
Boundary conditions: First page, last page, middle pages
Concurrent writes: Test pagination while data is being inserted/deleted
Performance tests: Measure query time at various offsets/cursors
Load tests: Simulate high traffic to first page (cache effectiveness)

Practice Exercise

Build a paginated blog posts API with the following requirements:

Implement both offset and cursor pagination endpoints
Support filtering by category and author
Allow sorting by: published_date, view_count, or title
Add search functionality (title + content)
Create appropriate database indexes
Include metadata (total, has_more, cursors)
Add validation for all query parameters
Write tests for edge cases and performance

Bonus: Implement a hybrid approach that uses offset for page 1-10 (for UX with page numbers), then switches to cursor for deeper pages (for performance).

Next Steps & Production Checklist

When to Use Each Pagination Strategy:

Offset/Page: Small datasets (<10k), admin panels, need "page X of Y" display
Cursor: Infinite scroll, real-time feeds, GraphQL APIs, frequently changing data
Keyset: Large datasets (>100k), performance-critical APIs, analytics pipelines

Production Readiness Checklist:

✅ Page size limited and validated (max 100 items)
✅ ID included as tie-breaker for stable sorting
✅ Database indexes created on sort/filter columns
✅ Cursor invalidation documented in API docs
✅ Metadata included (has_more, total, cursors)
✅ COUNT(*) queries cached or made optional
✅ First page cached with 30-60s TTL
✅ Error handling for invalid cursors (400 Bad Request)
✅ Monitoring for slow pagination queries (>100ms)
✅ Load tests conducted for deep pagination scenarios

Additional Resources:

Integration Review: This lesson combines patterns from Lesson 3 (basic pagination design), Lesson 6 (error handling for invalid params), Lesson 8 (caching strategies), Lesson 13 (testing pagination edge cases), and Lesson 20 (database indexing). You now have the complete toolkit for building production-ready paginated APIs at scale. Next: apply everything in the Capstone Project!

Back to "File Upload & Download Patterns"

Web APIsLesson 23

Next: "Capstone Project"