When should you NOT use API Pagination Patterns?

Avoid offset pagination on large tables — OFFSET 10000 causes a full index scan of skipped rows, degrading with depth. Do not return unbounded collections — missing pagination on a collection that grows is a latency and memory time bomb. Avoid using timestamps alone as cursors — duplicate timestamps in the same millisecond create gaps or duplicates in pages.

When is API Pagination Patterns the right choice?

Use cursor/keyset pagination for feeds and lists that change frequently — it stays consistent under inserts and deletes. Use offset pagination only for small, stable datasets where users need to jump to arbitrary page numbers (e.g. admin tables). Always enforce a maximum page size server-side — never trust a client-supplied per_page value without a cap.

← Back to glossary

API Pagination Patterns

API Design Intermediate

debt(d6/e5/b6/t5)

d6 Detectability Operational debt — how invisible misuse is to your safety net

Closest to 'specialist tool catches it' (d5), +1. Tools like laravel-debugbar and mysql-slow-query-log can surface slow queries from deep offset pagination, and code patterns like LIMIT OFFSET on large tables can be flagged. However, the performance degradation is often only visible at scale (page 500 vs page 1), and inconsistent pagination across endpoints requires careful review, pushing it slightly beyond d5 toward runtime observation.

e5 Effort Remediation debt — work required to fix once spotted

Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix suggests switching from offset to cursor pagination, but this requires changing the query logic, API response structure (adding next_cursor/next_url), updating client consumption patterns, and potentially modifying multiple API endpoints. It's not a one-line fix — it's a significant refactor within the API layer, often touching multiple controllers/resources and client code.

b6 Burden Structural debt — long-term weight of choosing wrong

Closest to 'persistent productivity tax' (b5), +1. Pagination is a cross-cutting API design decision that affects every list endpoint. Once offset pagination is baked into an API with public consumers, changing it requires versioning or backward-compatible additions. The choice shapes response formats, client integration patterns, and database query strategies across all paginated endpoints. It applies to web and API contexts broadly, creating moderate gravitational pull.

t5 Trap Cognitive debt — how counter-intuitive correct behaviour is

Closest to 'notable trap (a documented gotcha most devs eventually learn)' (t5). The misconception that total_count is required with every paginated response is a well-known performance trap — COUNT(*) on large tables is expensive. Additionally, many developers default to offset pagination because it maps intuitively to page numbers, not realizing it degrades linearly with depth. These are documented gotchas that experienced API developers learn, but they catch intermediates off guard.

About DEBT scoring → scored by claude-opus-4-6 · 2026-05-06 · reviewed by human

Also Known As

cursor pagination page-based pagination infinite scroll API

TL;DR

Strategies for returning large collections in manageable chunks — offset/page-based, cursor/keyset, and hybrid approaches each suit different use cases.

Explanation

Three main patterns: page-based (?page=5&per_page=20) — simple but slow on large datasets; cursor-based (?cursor=eyJpZCI6MTIzfQ) — fast and consistent but cannot jump to arbitrary pages; keyset (?after_id=123&limit=20) — fast like cursor but uses a specific field. API responses should include a next_cursor or next_page_url rather than requiring clients to construct pagination parameters. Always enforce a maximum page size to prevent ?per_page=999999.

Diagram

flowchart TD
    subgraph Offset Pagination
        OFF[LIMIT 20 OFFSET 200<br/>page=11 per_page=20]
        OFF -->|problem| SKIP[Scans 220 rows<br/>slow on large tables<br/>items drift on inserts]
    end
    subgraph Keyset Pagination
        KEY[WHERE id > last_seen_id<br/>LIMIT 20]
        KEY -->|benefit| FAST[Always O of log n<br/>stable on inserts<br/>no drift]
    end
    subgraph Cursor Pagination
        CUR[Opaque cursor<br/>encodes position]
        CUR -->|benefit| API2[Forward only<br/>stable - good for feeds]
    end
    style SKIP fill:#f85149,color:#fff
    style FAST fill:#238636,color:#fff

Watch Out

⚠ Offset pagination on a live feed is unstable — new rows inserted between page requests shift subsequent offsets, causing rows to appear twice or be skipped entirely.

Common Misconception

✗ Returning total_count with every paginated response is required — total count requires a COUNT(*) query that can be slow on large tables; omit it for performance-critical feeds.

Why It Matters

Page 500 of an offset-paginated API takes 10× longer than page 1 on large tables — cursor pagination makes every page equally fast, critical for infinite scroll and data exports.

Common Mistakes

No maximum page size — ?per_page=99999 loads the entire table in one request.
Offset pagination on tables with millions of rows — performance degrades linearly with page depth.
Exposing raw database IDs as cursors — cursors should be opaque (base64 encoded) to decouple API from storage.
Not including a next_cursor or next_url in the response — clients must reverse-engineer the pagination scheme.

Avoid When

Avoid offset pagination on large tables — OFFSET 10000 causes a full index scan of skipped rows, degrading with depth.
Do not return unbounded collections — missing pagination on a collection that grows is a latency and memory time bomb.
Avoid using timestamps alone as cursors — duplicate timestamps in the same millisecond create gaps or duplicates in pages.

When To Use

Use cursor/keyset pagination for feeds and lists that change frequently — it stays consistent under inserts and deletes.
Use offset pagination only for small, stable datasets where users need to jump to arbitrary page numbers (e.g. admin tables).
Always enforce a maximum page size server-side — never trust a client-supplied per_page value without a cap.

Code Examples

💡 NoteThe bad request uses page 500 with no size limit, scanning and discarding 499 pages of rows; the cursor approach tracks position by opaque token and reads only the next N rows regardless of depth.

✗ Vulnerable

// Offset pagination — slow deep pages, no max size:
GET /api/users?page=500&per_page=9999
// Scans and discards 500*9999 = 4.9M rows before returning results
// Response:
{"data": [...], "page": 500, "per_page": 9999}

✓ Fixed

// Cursor pagination — consistent performance:
GET /api/users?limit=20
// Response:
{
  "data": [...20 users...],
  "meta": {
    "next_cursor": "eyJpZCI6MjB9",  // Opaque cursor
    "has_more": true
  }
}
// Next page:
GET /api/users?cursor=eyJpZCI6MjB9&limit=20

References

https://www.sitepoint.com/paginating-real-time-data-cursor-based-pagination/