API pagination patterns

When a collection is too big to return at once, you paginate. There are two main patterns and choosing wrong causes real pain at scale.

Offset pagination

The classic approach: skip N rows, take the next page.

GET /api/posts?limit=20&offset=40

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;

Pros: simple, lets you jump to any page number.

Cons: two real problems.

• Slow on large tables, because the database still walks past every skipped row.
• Unstable, if a new row is inserted while the user pages, items shift and they see duplicates or gaps.

Cursor (keyset) pagination

Instead of an offset, remember the last item seen and ask for rows after it.

GET /api/posts?limit=20&after=2026-05-01T10:00:00Z

SELECT * FROM posts
WHERE created_at < '2026-05-01T10:00:00Z'
ORDER BY created_at DESC
LIMIT 20;

Pros: fast at any depth (it uses the index), and stable under inserts.

Cons: no jumping to an arbitrary page, and the cursor column must be unique and ordered. In practice you often use a composite cursor like (created_at, id) to break ties.

A typical response shape

{
  "data": [ { "id": 101 } ],
  "page": {
    "nextCursor": "eyJpZCI6MTAxfQ==",
    "hasMore": true
  }
}