API Pagination Patterns: Cursor vs Offset vs Keyset

Every API that returns lists needs pagination. Without it, listing 1 million records returns 1 million records. The three main patterns — offset, cursor, and keyset — have different performance characteristics, consistency guarantees, and client experience.

Offset Pagination

Request: GET /users?limit=20&offset=40

Response:

{
  "data": [...],
  "pagination": {
    "total": 1500,
    "limit": 20,
    "offset": 40,
    "has_more": true
  }
}

How it works: SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40

Pros

• Simple to implement and understand
• Random access — jump to any page (offset = (page - 1) * limit)
• Can show total count and "page X of Y"
• Works with any sorting

Cons

• Slow at deep offsets — OFFSET 100000 scans and discards 100K rows
• Inconsistent during writes — if a record is inserted/deleted between pages, clients skip or duplicate records
• Performance degrades linearly with offset value
• Total count query (COUNT(*)) is expensive on large tables

Performance

Offset	Query Time (1M rows)
0	~1ms
10,000	~15ms
100,000	~150ms
500,000	~800ms
900,000	~1.5s

Use when: Small datasets (<100K records), admin panels, or when "page X of Y" UI is required.

Cursor Pagination

Request: GET /users?limit=20&cursor=eyJpZCI6MTIzfQ==

Response:

{
  "data": [...],
  "pagination": {
    "has_more": true,
    "next_cursor": "eyJpZCI6MTQzfQ==",
    "prev_cursor": "eyJpZCI6MTI0fQ=="
  }
}

How it works: The cursor encodes the position (usually the last record's ID). The query uses WHERE id > 123 LIMIT 20 instead of OFFSET.

Pros

• Constant time — same performance regardless of position in the dataset
• Consistent during writes — cursor is a pointer to a specific record, not a position
• Scales to any dataset size — page 1 and page 50,000 have the same query time
• Opaque cursor hides implementation details

Cons

• No random access — can't jump to page 5
• No total count (or expensive to compute)
• Can't show "page X of Y"
• Cursor becomes invalid if the pointed-to record is deleted
• Slightly more complex to implement

Performance

Position	Query Time (1M rows)
Beginning	~1ms
Middle	~1ms
End	~1ms

Use when: Large datasets, real-time data, infinite scroll, mobile apps, any API where deep pagination is possible.

Keyset Pagination

Request: GET /users?limit=20&after_id=123&after_created=2026-03-08T12:00:00Z

Response:

{
  "data": [...],
  "pagination": {
    "has_more": true,
    "last_id": 143,
    "last_created": "2026-03-08T14:30:00Z"
  }
}

How it works: Like cursor pagination but the "cursor" is the actual column values. WHERE (created_at, id) > ('2026-03-08T12:00:00Z', 123) ORDER BY created_at, id LIMIT 20

Pros

• Same O(1) performance as cursor pagination
• Transparent — client can see and construct the pagination values
• Works with composite sorts (sort by created_at, then by id)
• Debuggable — no opaque base64 cursors

Cons

• Exposes internal column names/values
• Sort order is fixed (can't easily change sort without invalidating pagination)
• Multi-column sorting with keyset requires complex WHERE clauses
• Client needs to understand the sort order to construct valid requests

Use when: Internal APIs, when pagination transparency matters, APIs with fixed sort orders.

Comparison Table

Feature	Offset	Cursor	Keyset
Random access	✅ Yes	❌ No	❌ No
Total count	✅ Yes	❌ No	❌ No
Deep page performance	❌ O(n)	✅ O(1)	✅ O(1)
Consistency during writes	❌ Inconsistent	✅ Consistent	✅ Consistent
Implementation complexity	✅ Simple	⚠️ Medium	⚠️ Medium
Infinite scroll	❌ Poor	✅ Best	✅ Good
"Page X of Y"	✅ Yes	❌ No	❌ No

Real-World Usage

API	Pattern	Implementation
Stripe	Cursor	starting_after=obj_123, ending_before=obj_456
GitHub	Cursor (GraphQL)	after: "cursor", first: 20
Slack	Cursor	cursor=dXNlcl9pZA==, limit=20
Twitter/X	Cursor	pagination_token=abc123
Shopify	Cursor	page_info=abc123 (Link header)
Google	Page token	pageToken=abc123, pageSize=20

Best Practice: Cursor Pagination

For most APIs, cursor pagination is the right default:

1. Encode the cursor — base64 encode the position to make it opaque
2. Include has_more — boolean indicating more results exist
3. Provide next_cursor and prev_cursor — for forward and backward navigation
4. Default limit — set a sensible default (20) and maximum (100)
5. Don't include total count — it's expensive and rarely needed for infinite scroll

{
  "data": [...],
  "pagination": {
    "has_more": true,
    "next_cursor": "eyJpZCI6MTQzfQ==",
    "limit": 20
  }
}

If you need total count for UI, provide it as a separate endpoint or optional parameter (?include_count=true) so the expensive query only runs when needed.

---

Designing API pagination? Explore API design patterns and tools on APIScout — architecture guides, comparisons, and developer resources.