API pagination: cursor vs offset comparison

Need to choose between cursor-based and offset-based pagination for an API.

Offset pagination:

GET /api/items?page=3&per_page=20

SELECT * FROM items ORDER BY id LIMIT 20 OFFSET 40;

Pros: Simple, jump to any page
Cons: Slow for large offsets (scans skipped rows), inconsistent with concurrent writes

Cursor pagination (recommended):

GET /api/items?cursor=eyJpZCI6MTAwfQ&limit=20

-- Cursor is the last seen item's sort key
SELECT * FROM items WHERE id > 100 ORDER BY id LIMIT 21;
-- Fetch limit+1 to determine hasMore

Response:

{
  "items": [...],
  "pagination": {
    "next_cursor": "eyJpZCI6MTIwfQ",
    "has_more": true
  }
}

Cursor is typically base64-encoded sort key:

import base64, json
def encode_cursor(item):
    return base64.b64encode(json.dumps({'id': item['id']}).encode()).decode()
def decode_cursor(cursor):
    return json.loads(base64.b64decode(cursor))

Use cursor pagination when: large datasets, real-time data, infinite scroll.
Use offset pagination when: small datasets, need page numbers.

Offset pagination is O(offset) in the database - page 10000 scans 200,000 rows. Cursor pagination is always O(limit) using an index seek.

Designing API endpoints that return paginated lists