--- # === IDENTITY === id: software/patterns/rest-pagination/2026 canonical_question: "What are the REST API pagination patterns (cursor, offset, keyset)?" aliases: - "REST API pagination" - "cursor pagination vs offset pagination" - "keyset pagination REST API" - "API pagination best practices" - "paginate REST endpoint" - "cursor-based pagination implementation" - "offset limit pagination" - "seek method pagination" entity_type: software_reference domain: software > patterns > rest_pagination region: global jurisdiction: global temporal_scope: 2020-2026 # === VERIFICATION === last_verified: 2026-02-24 confidence: 0.92 version: 1.0 first_published: 2026-02-24 # === TEMPORAL VALIDITY === temporal_validity: status: stable last_breaking_change: null next_review: 2026-08-23 change_sensitivity: low # === CONSTRAINTS === constraints: - "Always enforce a maximum page size server-side (e.g., max 100) — never trust client-supplied limits" - "Offset pagination performance degrades linearly with offset size — avoid on tables exceeding 100K rows" - "Cursor tokens must be opaque and tamper-resistant — never expose raw database IDs as cursors" - "Keyset/seek pagination requires a stable, unique sort key (composite if needed) backed by an index" - "All pagination responses must include consistent metadata (has_more, cursors, total if cached) so clients can navigate reliably" # === SKIP CONDITIONS === skip_this_unit_if: - condition: "Need to paginate GraphQL queries (relay-style connections)" use_instead: "software/patterns/graphql-schema-patterns/2026" - condition: "Need to paginate streaming/real-time data (WebSocket, SSE)" use_instead: "Use event-driven streaming with backpressure — not request/response pagination" # === AGENT HINTS === inputs_needed: - key: "dataset_size" question: "How large is the dataset you need to paginate?" type: choice options: ["small (<10K rows)", "medium (10K-1M rows)", "large (>1M rows)"] - key: "consistency_needs" question: "Do you need strong consistency (no missed/duplicate items) or is eventual consistency acceptable?" type: choice options: ["eventual", "strong"] - key: "client_type" question: "What type of client will consume this API?" type: choice options: ["web (supports page jumps)", "mobile (infinite scroll)", "API/machine (sequential processing)"] # === DISTRIBUTION === canonical_source: "https://knowledgelib.io/software/patterns/rest-pagination/2026" suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-02-24)" # === RELATED UNITS === related_kos: related_to: - id: "software/patterns/api-rate-limiting/2026" label: "API Rate Limiting" - id: "software/patterns/api-versioning/2026" label: "API Versioning" solves: - id: "software/patterns/graphql-schema-patterns/2026" label: "GraphQL Schema Patterns" alternative_to: - id: "software/patterns/graphql-schema-patterns/2026" label: "GraphQL Connections — Relay-style cursor pagination for GraphQL" often_confused_with: - id: "software/patterns/webhook-implementation/2026" label: "Webhook Implementation — push-based delivery, not pull-based pagination" # === SOURCES (8 authoritative sources) === # Types: official_docs, technical_blog, rfc_spec, community_resource, industry_report # Reliability: high, moderate_high, moderate sources: - id: src1 title: "How to Implement REST API Pagination: Offset, Cursor, Keyset" author: Stainless url: https://www.stainless.com/sdk-api-best-practices/how-to-implement-rest-api-pagination-offset-cursor-keyset type: technical_blog published: 2025-06-15 reliability: high - id: src2 title: "SQL Seek Method or Keyset Pagination" author: Vlad Mihalcea url: https://vladmihalcea.com/sql-seek-keyset-pagination/ type: technical_blog published: 2024-03-10 reliability: high - id: src3 title: "A Developer's Guide to API Pagination: Offset vs. Cursor-Based" author: Gusto Engineering url: https://embedded.gusto.com/blog/api-pagination/ type: technical_blog published: 2025-01-20 reliability: moderate_high - id: src4 title: "Understanding Cursor Pagination and Why It's So Fast" author: Milan Jovanovic url: https://www.milanjovanovic.tech/blog/understanding-cursor-pagination-and-why-its-so-fast-deep-dive type: technical_blog published: 2024-09-15 reliability: high - id: src5 title: "Pagination Best Practices in REST API Design" author: Speakeasy url: https://www.speakeasy.com/api-design/pagination type: technical_blog published: 2025-03-10 reliability: moderate_high - id: src6 title: "Comparing Cursor Pagination and Offset Pagination" author: Zendesk url: https://developer.zendesk.com/documentation/api-basics/pagination/comparing-cursor-pagination-and-offset-pagination/ type: official_docs published: 2024-06-01 reliability: high - id: src7 title: "A Practical Guide to Scalable Pagination: Offset, Keyset and Beyond" author: Halodoc Engineering url: https://blogs.halodoc.io/a-practical-guide-to-scalable-pagination/ type: technical_blog published: 2024-11-20 reliability: moderate_high - id: src8 title: "Limit/Offset Pagination vs. Cursor Pagination in MySQL" author: PingCAP url: https://www.pingcap.com/article/limit-offset-pagination-vs-cursor-pagination-in-mysql/ type: technical_blog published: 2024-08-05 reliability: moderate_high --- # REST API Pagination Patterns: Complete Reference ## TL;DR - **Bottom line**: Use cursor-based pagination for large or real-time datasets; use offset/limit for small, sortable datasets where page-jump is needed. - **Key tool/command**: `WHERE (sort_key, id) > (:last_sort_key, :last_id) ORDER BY sort_key, id LIMIT :page_size` (keyset/seek method) - **Watch out for**: Offset pagination on large tables silently degrades -- at OFFSET 100,000 the database scans and discards 100K rows per request. - **Works with**: Any REST framework (Express, FastAPI, Go net/http, Spring Boot) and any SQL database (PostgreSQL, MySQL, SQL Server, SQLite). ## Constraints - Always enforce a maximum page size server-side (e.g., max 100) -- never trust client-supplied limits - Offset pagination performance degrades linearly with offset size -- avoid on tables exceeding 100K rows - Cursor tokens must be opaque and tamper-resistant -- never expose raw database IDs as cursors - Keyset/seek pagination requires a stable, unique sort key (composite if needed) backed by a database index - All pagination responses must include consistent metadata (`has_more`, cursors, `total` if cached) so clients can navigate reliably - Never paginate without an explicit `ORDER BY` clause -- results will be non-deterministic across requests ## Quick Reference | Pattern | Performance at Scale | Consistency | Jump to Page | Implementation | Best For | |---|---|---|---|---|---| | **Offset/Limit** | Poor (O(offset+limit) scan) | Weak (page drift on inserts) | Yes | `?offset=20&limit=10` | Small datasets (<10K), admin UIs | | **Cursor (opaque token)** | Excellent (O(limit) constant) | Strong (stable position) | No | `?cursor=eyJpZCI6MTB9&limit=10` | Large datasets, real-time feeds, mobile infinite scroll | | **Keyset/Seek** | Excellent (O(limit) constant) | Strong (stable position) | No | `?after_id=42&after_date=2026-01-15&limit=10` | Large sorted datasets, time-series, logs | | **Page Number** | Poor (offset in disguise) | Weak (same as offset) | Yes | `?page=3&per_page=25` | Simple UIs, small static datasets | | **Time-Based** | Good (index on timestamp) | Moderate (clock skew risk) | No | `?since=2026-01-01T00:00:00Z&limit=50` | Event streams, audit logs, sync APIs | | **Hybrid (cursor + page count)** | Good (cursor + cached count) | Strong for data, eventual for count | Limited (first/last only) | `?cursor=abc&limit=10` + `total_count` header | APIs needing both efficiency and total counts | ## Decision Tree ``` START |-- Dataset size > 1M rows? | |-- YES --> Use Cursor or Keyset pagination | | |-- Need to expose sort values in URL (transparent)? | | | |-- YES --> Keyset/Seek (after_id + after_sort_key) | | | +-- NO --> Cursor with opaque base64 token | +-- NO | |-- Dataset size 10K-1M rows? | |-- YES --> Do clients need page-jump (go to page N)? | | |-- YES --> Offset/Limit with cached total count | | +-- NO --> Cursor pagination (simpler, more consistent) | +-- NO | |-- Dataset < 10K rows? | |-- YES --> Do clients need page numbers in UI? | | |-- YES --> Page Number (?page=N&per_page=M) | | +-- NO --> Offset/Limit (simple, good enough) +-- Is data time-series or event log? |-- YES --> Time-Based (?since=timestamp&limit=N) +-- NO --> DEFAULT: Cursor pagination (safest general choice) ``` ## Step-by-Step Guide ### 1. Choose your pagination pattern Evaluate your dataset size, consistency needs, and client requirements using the decision tree above. For most modern APIs serving mobile or SPA clients, cursor-based pagination is the recommended default. [src1] **Verify**: Answer these three questions: (1) How many rows? (2) Do clients need page-jump? (3) Is data frequently inserted/deleted? ### 2. Design the response envelope Every paginated endpoint should return a consistent response shape. Include the data array, pagination metadata, and navigation links. [src5] ```json { "data": [...], "pagination": { "has_more": true, "next_cursor": "eyJpZCI6NDIsImNyZWF0ZWRfYXQiOiIyMDI2LTAxLTE1VDEwOjMwOjAwWiJ9", "prev_cursor": "eyJpZCI6MzMsImNyZWF0ZWRfYXQiOiIyMDI2LTAxLTE0VDA4OjAwOjAwWiJ9", "limit": 25 }, "links": { "next": "/api/v1/items?cursor=eyJpZCI6NDJ9&limit=25", "prev": "/api/v1/items?cursor=eyJpZCI6MzN9&limit=25&direction=prev" } } ``` **Verify**: Ensure `has_more` is `false` on the last page and cursors are `null` when no more pages exist. ### 3. Create the database index Keyset and cursor pagination only perform well if the sort columns are indexed. Create a composite index matching your ORDER BY clause. [src2] ```sql -- For pagination sorted by created_at DESC, id DESC CREATE INDEX idx_items_pagination ON items (created_at DESC, id DESC); -- For pagination sorted by a custom sort key CREATE INDEX idx_items_sort ON items (sort_key ASC, id ASC); ``` **Verify**: `EXPLAIN ANALYZE SELECT * FROM items WHERE (created_at, id) < ('2026-01-15', 42) ORDER BY created_at DESC, id DESC LIMIT 25;` -- should show Index Scan, not Seq Scan. ### 4. Implement cursor encoding/decoding Encode the last row's sort values into an opaque, base64-encoded cursor token. Decode on the server to build the WHERE clause. [src4] ```javascript // Encode cursor function encodeCursor(row) { return Buffer.from(JSON.stringify({ id: row.id, created_at: row.created_at })).toString('base64url'); } // Decode cursor function decodeCursor(cursor) { return JSON.parse(Buffer.from(cursor, 'base64url').toString()); } ``` **Verify**: `decodeCursor(encodeCursor({ id: 42, created_at: '2026-01-15' }))` returns `{ id: 42, created_at: '2026-01-15' }`. ### 5. Build the query with keyset filtering Use row-value comparison or compound WHERE clauses to seek to the correct position. [src2] ```sql -- PostgreSQL: row-value comparison (cleaner) SELECT id, title, created_at FROM items WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT :page_size; -- MySQL: compound WHERE (row-value comparison unreliable in older versions) SELECT id, title, created_at FROM items WHERE created_at < :last_created_at OR (created_at = :last_created_at AND id < :last_id) ORDER BY created_at DESC, id DESC LIMIT :page_size; ``` **Verify**: Run with `EXPLAIN` -- the plan should use the composite index from Step 3. ### 6. Add server-side limit enforcement Never trust client-provided limits. Clamp to a maximum and provide a sensible default. [src5] ```javascript const DEFAULT_LIMIT = 25; const MAX_LIMIT = 100; function sanitizeLimit(requested) { const limit = parseInt(requested, 10) || DEFAULT_LIMIT; return Math.min(Math.max(1, limit), MAX_LIMIT); } ``` **Verify**: `sanitizeLimit(500)` returns `100`, `sanitizeLimit(-1)` returns `1`, `sanitizeLimit(undefined)` returns `25`. ## Code Examples ### Node.js/Express: All Three Pagination Styles > Full script: [node-js-express-all-three-pagination-styles.js](scripts/node-js-express-all-three-pagination-styles.js) (62 lines) ```javascript // Input: GET /api/items?mode=cursor&cursor=abc&limit=25 // GET /api/items?mode=offset&offset=0&limit=25 // GET /api/items?mode=keyset&after_id=42&after_date=2026-01-15&limit=25 // Output: { data: [...], pagination: { has_more, next_cursor|next_offset, limit } } const express = require('express'); // ^4.18 # ... (see full script) ``` ### Python/FastAPI: Cursor Pagination with SQLAlchemy > Full script: [python-fastapi-cursor-pagination-with-sqlalchemy.py](scripts/python-fastapi-cursor-pagination-with-sqlalchemy.py) (39 lines) ```python # Input: GET /items?cursor=eyJpZCI6NDJ9&limit=25 # Output: { "data": [...], "pagination": { "has_more": true, "next_cursor": "...", "limit": 25 } } import base64, json from fastapi import FastAPI, Query # fastapi>=0.109 from sqlalchemy import select, and_ # sqlalchemy>=2.0 # ... (see full script) ``` ### Go: Keyset Pagination with database/sql > Full script: [go-keyset-pagination-with-database-sql.go](scripts/go-keyset-pagination-with-database-sql.go) (72 lines) ```go // Input: GET /items?after_id=42&after_ts=2026-01-15T10:30:00Z&limit=25 // Output: JSON { "data": [...], "pagination": { "has_more": true, ... } } package main import ( "database/sql" // standard library # ... (see full script) ``` ## Anti-Patterns ### Wrong: Using OFFSET on large tables without limit ```sql -- BAD -- database scans and discards 1M rows, returns 25 SELECT * FROM items ORDER BY created_at DESC LIMIT 25 OFFSET 1000000; -- At offset 1M: PostgreSQL reads ~1,000,025 rows from the index -- Response time: 2-5 seconds (vs. 2ms for first page) ``` ### Correct: Use keyset/seek to jump directly to position ```sql -- GOOD -- database seeks directly via index, reads only 25 rows SELECT * FROM items WHERE (created_at, id) < ('2026-01-15T10:30:00Z', 42) ORDER BY created_at DESC, id DESC LIMIT 25; -- Constant ~2ms regardless of position in dataset ``` ### Wrong: Exposing raw database IDs as cursor tokens ``` GET /api/items?cursor=42 ``` ```javascript // BAD -- leaks internal IDs, allows enumeration, breaks if ID scheme changes app.get('/api/items', (req, res) => { const cursor = parseInt(req.query.cursor); db.query('SELECT * FROM items WHERE id < $1 ORDER BY id DESC LIMIT 25', [cursor]); }); ``` ### Correct: Use opaque, encoded cursor tokens ``` GET /api/items?cursor=eyJpZCI6NDIsImNyZWF0ZWRfYXQiOiIyMDI2LTAxLTE1In0 ``` ```javascript // GOOD -- opaque token hides internals, can evolve without breaking clients app.get('/api/items', (req, res) => { const { id, created_at } = JSON.parse( Buffer.from(req.query.cursor, 'base64url').toString() ); db.query( 'SELECT * FROM items WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 25', [created_at, id] ); }); ``` ### Wrong: No server-side limit enforcement ```javascript // BAD -- client can request limit=999999 and crash your server app.get('/api/items', (req, res) => { const limit = parseInt(req.query.limit); // unbounded! db.query('SELECT * FROM items LIMIT $1', [limit]); }); ``` ### Correct: Clamp limit to a safe maximum ```javascript // GOOD -- enforce bounds server-side const MAX_LIMIT = 100; const DEFAULT_LIMIT = 25; app.get('/api/items', (req, res) => { const limit = Math.min(Math.max(1, parseInt(req.query.limit) || DEFAULT_LIMIT), MAX_LIMIT); db.query('SELECT * FROM items ORDER BY id DESC LIMIT $1', [limit]); }); ``` ### Wrong: Computing COUNT(*) on every paginated request ```javascript // BAD -- full table scan on every request just for total_count app.get('/api/items', async (req, res) => { const [items] = await db.query('SELECT * FROM items LIMIT 25 OFFSET $1', [offset]); const [{ count }] = await db.query('SELECT COUNT(*) FROM items'); // slow on large tables res.json({ data: items, total: count }); }); ``` ### Correct: Cache total count or use has_more flag ```javascript // GOOD -- fetch limit+1 rows to detect has_more; cache count separately if needed app.get('/api/items', async (req, res) => { const rows = await db.query('SELECT * FROM items ORDER BY id DESC LIMIT $1', [limit + 1]); const hasMore = rows.length > limit; const data = hasMore ? rows.slice(0, limit) : rows; // Cache total_count with 60s TTL if UI requires it const total = await cache.getOrSet('items_count', () => db.query('SELECT COUNT(*)...'), 60); res.json({ data, pagination: { has_more: hasMore }, total_count: total }); }); ``` ## Common Pitfalls - **Page drift with offset pagination**: When rows are inserted or deleted while a client paginates, items shift between pages causing duplicates or missed records. Fix: Switch to cursor/keyset pagination for datasets with concurrent writes. [src3] - **Missing ORDER BY clause**: Without explicit ordering, databases return rows in arbitrary order that can change between requests, producing duplicate or missing items. Fix: Always add `ORDER BY` on indexed column(s) to every paginated query. [src5] - **No composite index for keyset pagination**: Using `WHERE created_at < X AND id < Y` without a matching composite index forces a sequential scan. Fix: `CREATE INDEX idx ON items (created_at DESC, id DESC)` matching the exact ORDER BY direction. [src2] - **Cursor invalidation on schema changes**: If you rename or remove columns used in cursor encoding, all outstanding cursors break. Fix: Version your cursor format (e.g., `v1:base64payload`) and support decoding old versions during migration. [src1] - **Off-by-one in has_more detection**: Fetching exactly `limit` rows means you cannot tell if more exist. Fix: Always fetch `limit + 1` rows, return only `limit`, and set `has_more = rows.length > limit`. [src4] - **Inconsistent response shapes**: Some endpoints return `{ items: [...] }`, others `{ data: [...], meta: {...} }`. Fix: Standardize a single pagination envelope across your entire API. [src5] - **Not handling empty cursors**: Clients send `cursor=` (empty string) which is truthy in JavaScript but should be treated as no cursor. Fix: Check `if (cursor && cursor.length > 0)` before decoding. [src7] ## Diagnostic Commands ```bash # Check if your pagination query uses an index (PostgreSQL) EXPLAIN ANALYZE SELECT * FROM items WHERE (created_at, id) < ('2026-01-15', 42) ORDER BY created_at DESC, id DESC LIMIT 25; -- Look for: "Index Scan" or "Index Only Scan" (good) -- Watch for: "Seq Scan" or "Sort" (bad -- missing index) # Compare offset vs keyset performance (PostgreSQL) EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM items ORDER BY created_at DESC LIMIT 25 OFFSET 100000; -- Note the "Buffers: shared hit" count and execution time EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM items WHERE (created_at, id) < ('2025-06-01', 500000) ORDER BY created_at DESC, id DESC LIMIT 25; -- Compare: keyset should show far fewer buffer hits and lower execution time # Check index existence for pagination columns SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'items' AND indexdef LIKE '%created_at%'; # Monitor slow paginated queries (PostgreSQL) SELECT query, calls, mean_exec_time, rows FROM pg_stat_statements WHERE query LIKE '%OFFSET%' AND mean_exec_time > 100 ORDER BY mean_exec_time DESC LIMIT 10; # Test cursor encoding/decoding (Node.js one-liner) node -e "const c=Buffer.from(JSON.stringify({id:42,ts:'2026-01-15'})).toString('base64url'); console.log('cursor:', c); console.log('decoded:', JSON.parse(Buffer.from(c,'base64url')))" ``` ## When to Use / When Not to Use | Use When | Don't Use When | Use Instead | |---|---|---| | Dataset > 100K rows and growing | Dataset is small and static (<1K rows) | Return all results in a single response | | Clients consume data sequentially (infinite scroll, feed) | Clients need random page access (go to page 47) | Offset/Limit with cached total count | | Data changes frequently (real-time feeds, logs) | Data is immutable and append-only with known total | Simple offset pagination | | Building a public API consumed by third parties | Internal admin dashboard with known small datasets | Page number pagination for simplicity | | Need consistent results during concurrent writes | Eventual consistency is acceptable and dataset is small | Offset is fine | | Mobile app with infinite scroll UX | GraphQL API (use Relay-style connections) | Relay cursor connections specification | ## Important Caveats - Cursor pagination eliminates page-jump capability -- if your UI requires "go to page N", you need offset/limit (with the performance trade-off) or a hybrid approach that caches page boundaries. - The `LIMIT + 1` pattern for has_more detection means you always over-fetch by one row. For very large page sizes this is negligible, but be aware it exists in your query plans. - Base64 cursor tokens are not encrypted -- they are trivially decodable. Do not include sensitive data in cursors. For tamper resistance, consider HMAC-signing the cursor payload. - PostgreSQL row-value comparisons (`WHERE (a, b) < (x, y)`) are well-optimized, but MySQL's optimizer handles them poorly in versions before 8.0.33. Use compound WHERE clauses as shown in the MySQL example. - Time-based pagination (`?since=timestamp`) is vulnerable to clock skew in distributed systems. Always use server-generated timestamps, never client-provided ones. - When switching from offset to cursor pagination on an existing API, maintain backward compatibility by supporting both parameters during a deprecation period. ## Related Units - [API Rate Limiting](/software/patterns/api-rate-limiting/2026) - [API Versioning](/software/patterns/api-versioning/2026) - [GraphQL Schema Patterns](/software/patterns/graphql-schema-patterns/2026) - [Webhook Implementation](/software/patterns/webhook-implementation/2026)