REST API Pagination Patterns

What and Why

Returning a million rows in one response is a terrible idea. Pagination splits results into pages so clients can fetch them incrementally. Picking the right pagination strategy affects both performance and the user experience.

Mental Model

The three common patterns trade off simplicity and scale.

• Offset pagination uses limit and offset. Easy, but slow on big tables and unstable under writes.
• Cursor pagination returns an opaque token pointing to the next batch. Stable and fast.
• Keyset pagination uses a known column like id or created_at as the cursor. Even faster, less opaque.

Offset:   ?limit=20&offset=10000  -> DB scans 10020 rows
Keyset:   ?limit=20&after=12345    -> DB seeks to id > 12345 (index range)
Cursor:   ?limit=20&cursor=eyJpZCI6MTIzfQ -> server decodes to keyset query

Hands-on Example

Offset pagination is simple but does not scale.

from fastapi import FastAPI, Query

app = FastAPI()

@app.get("/posts")
def list_posts(limit: int = Query(20, le=100), offset: int = 0):
    rows = db.execute(
        "SELECT id, title FROM posts ORDER BY id LIMIT :limit OFFSET :offset",
        {"limit": limit, "offset": offset},
    ).all()
    total = db.execute("SELECT COUNT(*) FROM posts").scalar()
    return {"items": rows, "total": total, "limit": limit, "offset": offset}

Keyset pagination scales much better.

@app.get("/posts/keyset")
def list_posts_keyset(limit: int = Query(20, le=100), after_id: int = 0):
    rows = db.execute(
        "SELECT id, title FROM posts WHERE id > :after ORDER BY id LIMIT :limit",
        {"after": after_id, "limit": limit},
    ).all()
    next_id = rows[-1]["id"] if rows else None
    return {"items": rows, "next_after_id": next_id}

Cursor pagination hides the cursor format from clients, letting you change it later.

import base64, json

def encode_cursor(payload: dict) -> str:
    return base64.urlsafe_b64encode(json.dumps(payload).encode()).decode()

def decode_cursor(s: str) -> dict:
    return json.loads(base64.urlsafe_b64decode(s.encode()))

@app.get("/posts/cursor")
def list_posts_cursor(limit: int = Query(20, le=100), cursor: str | None = None):
    after_id = decode_cursor(cursor)["id"] if cursor else 0
    rows = db.execute(
        "SELECT id, title FROM posts WHERE id > :after ORDER BY id LIMIT :limit",
        {"after": after_id, "limit": limit},
    ).all()
    next_cursor = encode_cursor({"id": rows[-1]["id"]}) if rows else None
    return {"items": rows, "next_cursor": next_cursor}

A common response envelope keeps clients consistent.

{
  "items": [{"id": 1}, {"id": 2}],
  "page_info": {
    "next_cursor": "eyJpZCI6Mn0",
    "has_next": true
  }
}

Returning hypermedia links is also nice.

{
  "items": [],
  "links": {
    "next": "/posts?cursor=eyJpZCI6Mn0",
    "self": "/posts"
  }
}

Common Pitfalls

• Using offset on tables with millions of rows. Performance falls off a cliff.
• Sorting on a non-unique column without a tiebreaker. Pages can duplicate or skip rows.
• Returning a total count on huge tables. COUNT(*) can be expensive; consider exact-only-when-small.
• Letting clients pick unbounded limit. Always cap it server-side.
• Coupling pagination state to mutable state. If rows get reordered between calls, clients see chaos.

Practical Tips

• Default to cursor or keyset pagination for new APIs. Reserve offset for admin-style endpoints where it is fine.
• Always sort by a stable, indexed column. Compose (created_at, id) if you need both time and uniqueness.
• Document limit defaults and maximums explicitly.
• Treat cursors as opaque. Clients should never inspect or build them.
• Combine pagination with filtering and sorting in a single endpoint to keep the contract simple.

Wrap-up

Pagination is a small piece of the API surface that has outsized impact on performance and client UX. Offset is fine for tiny datasets. For anything that will grow, reach for keyset or opaque cursors backed by indexed columns. Cap limits, stabilize sort orders, and document the contract clearly, and your endpoints will hold up as your data scales.