Every API that returns a list eventually needs pagination. Without it, a single request can return millions of rows, crush your database, and blow up your client’s memory. The question isn’t whether to paginate — it’s how.

There are three dominant patterns: offset-based, cursor-based, and keyset-based. Each makes different tradeoffs between simplicity, reliability, and performance. This guide breaks down all three with SQL queries, API response examples, and a comparison table so you can pick the right one for your use case.

Why pagination matters

Returning unbounded result sets is one of the most common API design mistakes. Pagination solves three problems at once:

• Database load — Fetching 10 rows instead of 10 million keeps queries fast and memory usage predictable.
• Network efficiency — Smaller payloads mean faster responses and lower bandwidth costs.
• Client stability — Mobile apps and browsers can’t render a million-row table without crashing.

The pattern you choose affects how stable your pages are when data changes, how fast deep pages load, and how complex your implementation gets.

1. Offset-based pagination (LIMIT / OFFSET)

The most intuitive approach. You tell the database to skip N rows and return the next page.

SQL example

-- Page 1 (items 1–20)
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 2 (items 21–40)
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 20;

-- Page 50 (items 981–1000)
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 980;

API response example

{
  "data": [
    { "id": 101, "name": "Widget A", "created_at": "2026-05-20T10:00:00Z" },
    { "id": 102, "name": "Widget B", "created_at": "2026-05-20T09:30:00Z" }
  ],
  "pagination": {
    "page": 2,
    "per_page": 20,
    "total_count": 4350,
    "total_pages": 218
  }
}

Pros

• Dead simple to implement — one page parameter is all you need.
• Clients can jump to any page directly (page 1, page 50, page 200).
• Easy to show “Page X of Y” in the UI.

Cons

• Breaks on inserts and deletes. If a row is added to page 1 while a user is on page 2, they’ll see a duplicate. If a row is deleted, they’ll skip one. This is the fundamental flaw.
• Slow on large offsets. OFFSET 100000 forces the database to scan and discard 100,000 rows before returning your 20. Even with proper indexes, this gets expensive. In PostgreSQL, OFFSET performance degrades linearly with depth.
• Not suitable for real-time feeds or datasets that change frequently.

Offset pagination works fine for admin dashboards, internal tools, and small datasets where users rarely go past page 10.

2. Cursor-based pagination

Instead of a page number, the server returns an opaque token (the cursor) that points to the last item on the current page. The client sends this cursor back to fetch the next page. This is the pattern used by Stripe, Slack, GitHub, and most modern APIs.

The cursor is typically a Base64-encoded string containing the sort key and ID of the last returned item.

SQL example

-- Decode cursor to get: created_at = '2026-05-20T09:30:00Z', id = 102

SELECT * FROM products
WHERE (created_at, id) < ('2026-05-20T09:30:00Z', 102)
ORDER BY created_at DESC, id DESC
LIMIT 20;

API response example

{
  "data": [
    { "id": 103, "name": "Widget C", "created_at": "2026-05-20T09:00:00Z" },
    { "id": 104, "name": "Widget D", "created_at": "2026-05-19T18:45:00Z" }
  ],
  "pagination": {
    "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0xOVQxODo0NTowMFoiLCJpZCI6MTA0fQ==",
    "has_more": true
  }
}

The cursor eyJjcm... decodes to {"created_at":"2026-05-19T18:45:00Z","id":104}, but clients should treat it as opaque — never parse or construct cursors manually.

Pros

• Stable under mutations. Inserts and deletes don’t cause duplicates or skipped items because the cursor anchors to a specific row, not a position.
• Consistent performance. No matter how deep you paginate, the query uses an index seek instead of scanning and discarding rows.
• Server controls the sort order and can change cursor internals without breaking clients.

Cons

• No random access — you can’t jump to page 50. Navigation is strictly forward (and optionally backward).
• Can’t display “Page X of Y” without a separate count query.
• Slightly more complex to implement — you need cursor encoding/decoding and proper error handling for invalid or expired cursors.

Cursor-based pagination is the best default for public APIs, mobile apps, infinite scroll UIs, and any dataset that changes frequently.

3. Keyset-based pagination

Keyset pagination (also called “seek method”) is the engine under the hood of cursor-based pagination, but exposed directly. Instead of an opaque token, the client passes the last seen sort value explicitly.

SQL example

-- First page
SELECT * FROM products ORDER BY id ASC LIMIT 20;

-- Next page: pass the last id from previous response
SELECT * FROM products
WHERE id > 1042
ORDER BY id ASC
LIMIT 20;

For compound sort keys:

-- Sort by created_at, then id as tiebreaker
SELECT * FROM products
WHERE (created_at, id) > ('2026-05-19T18:45:00Z', 104)
ORDER BY created_at ASC, id ASC
LIMIT 20;

API response example

{
  "data": [
    { "id": 1043, "name": "Widget E" },
    { "id": 1044, "name": "Widget F" }
  ],
  "pagination": {
    "last_id": 1044,
    "has_more": true
  }
}

Pros

• Fastest option. The WHERE id > X clause uses an index seek directly — no scanning, no offset overhead. Performance is constant regardless of how deep you go.
• Simple to understand and debug — the parameters are visible, not encoded.
• Works naturally with PostgreSQL B-tree indexes.

Cons

• Requires a sortable, unique column (or combination). If your sort column has duplicates and no tiebreaker, you’ll skip or duplicate rows.
• No random page access — same as cursor-based.
• Exposes internal sort keys to the client, which couples your API to your database schema. Changing the sort column becomes a breaking change.
• Harder to implement for multi-column sorts or descending order.

Keyset pagination is ideal for internal services, data pipelines, log ingestion, and anywhere you control both client and server.

Comparison table

Feature	Offset	Cursor	Keyset
Random page access	✅ Yes	❌ No	❌ No
Stable under mutations	❌ No	✅ Yes	✅ Yes
Deep page performance	❌ Degrades	✅ Constant	✅ Constant
Implementation complexity	Low	Medium	Low–Medium
”Page X of Y” display	✅ Easy	❌ Needs count query	❌ Needs count query
Sort flexibility	✅ Any	✅ Any (server-controlled)	⚠️ Needs indexed column
Schema coupling	Low	Low (opaque)	High (exposes keys)
Used by	Admin panels, small datasets	Stripe, Slack, GitHub	Internal services, pipelines

When to use which

Choose offset when you have a small, mostly-static dataset and need random page access — think admin tables, CMS content lists, or search results where users expect page numbers.

Choose cursor when you’re building a public API or any client-facing interface with infinite scroll. It’s the safest default. If you’re unsure, start here.

Choose keyset when you control both ends of the connection and need raw speed — data exports, event streaming, syncing between microservices, or paginating through millions of log entries.

In practice, cursor-based pagination is keyset pagination with an abstraction layer on top. Many teams start with keyset internally and wrap it in cursors for their public API. That gives you the performance of keyset with the flexibility and safety of cursors.

Whatever pattern you pick, always set a maximum page size server-side, return consistent metadata in your API responses, and handle edge cases like empty pages and invalid parameters gracefully.