API Pagination: Offset vs Cursor (Keyset) and When Each Breaks

AI-generated list endpoints often start as SELECT * FROM orders WHERE user_id = $1 returned in full. With 30 rows in development, that's fine. With 300,000 rows in production, it's a slow query, a multi-megabyte response, and a browser struggling to render it. Every list endpoint needs pagination; the question is which kind.

Offset pagination

The familiar version: skip N rows, take M.

GET /api/orders?page=3&per_page=50

SELECT * FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 50 OFFSET 100;

Pros: trivial to implement; supports "jump to page 17"; pairs naturally with page numbers in the UI.

Two problems:

1. It gets slower the deeper you go

OFFSET 100000 doesn't magically skip rows — the database still produces and discards the first 100,000 of the ordered result. Deep pages get progressively slower, and crawlers or scripts that walk every page make the problem acute.

2. It skips and duplicates rows when data changes

User loads page 1 (rows 1–50). A new order is inserted at the top. User loads page 2 with OFFSET 50 — which now starts at what was row 50. They see it twice. Deletions cause the opposite: a row is silently skipped. For feeds, inboxes, and anything actively written to, that's a visible bug.

Cursor (keyset) pagination

Instead of "skip N rows," say "give me rows after this one":

GET /api/orders?limit=50
GET /api/orders?limit=50&after=eyJjIjoiMjAyNi0wOS0yNFQxMDowMDowMFoiLCJpIjo5ODc2fQ

SELECT * FROM orders
WHERE user_id = $1
  AND (created_at, id) < ($2, $3)        -- the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 51;                                -- one extra to know if there's a next page

Pros:

Constant speed at any depth — with the right index, the database seeks directly to the position.
Stable under inserts and deletes — new rows at the top don't shift your position.

Cons: no "jump to page 17"; "previous page" needs the reverse query; the cursor must encode every sort column.

Getting keyset pagination right

Always include a unique tie-breaker

created_at alone isn't unique; two rows with the same timestamp can be skipped or duplicated at a page boundary. Sort by (created_at, id) and compare the tuple.

Index for the exact query

CREATE INDEX orders_user_created_id_idx
  ON orders (user_id, created_at DESC, id DESC);

Equality columns first (user_id), then the sort columns in order. EXPLAIN ANALYZE should show an index scan with no sort step. (Database Indexes.)

Row-value comparison and NULLs

PostgreSQL supports the tuple comparison (a, b) < ($1, $2) directly and can use a matching index for it. Sort columns must be NOT NULL (or handled explicitly) — NULLs break the ordering logic.

Make cursors opaque

Encode the sort values into a base64 string rather than exposing raw parameters:

const encode = (row) =>
  Buffer.from(JSON.stringify({ c: row.createdAt, i: row.id })).toString("base64url")
const decode = (s) => JSON.parse(Buffer.from(s, "base64url").toString())

Opaque cursors let you change the sort internals later without breaking clients. Validate decoded cursors like any input, and never interpolate them into SQL. (SQL Injection Explained.) Sign them (HMAC) if tampering would matter.

Fetch limit + 1

Request one more row than the page size. If you get it, there's a next page; drop it from the response and use the last returned row as the cursor.

Total counts

"Showing 1–50 of 312,948" requires COUNT(*), which in PostgreSQL scans the matching rows — expensive on large tables, on every request. Options:

Don't show totals — "Load more" and infinite scroll don't need them.
Cap it: count up to 1,001 and display "1,000+".
Estimate from planner statistics for unfiltered tables.
Cache the count, if it can be approximate.

Filtering and sorting parameters

User-selectable sort needs an allowlist of sortable fields (never a raw column name from the query string), each backed by an index, and the cursor must record which sort it belongs to. Changing sort or filters resets pagination.

A response shape

{
  "data": [ /* up to limit items */ ],
  "page": {
    "next_cursor": "eyJjIjoi…",
    "has_more": true
  }
}

Cap limit server-side (say, max 100) regardless of what the client asks for. Document that cursors are opaque and may expire. (Designing a REST API.)

Which to use

Use case	Choose
Admin tables with page numbers, small-to-medium data	Offset (with a max page depth)
Feeds, timelines, infinite scroll, activity logs	Cursor
Public APIs, sync/export endpoints, large tables	Cursor
Data that changes while users page through it	Cursor

GraphQL APIs commonly use the Relay connection convention (edges, pageInfo, endCursor), which is keyset pagination with a standard shape. (GraphQL vs REST.)

EasySpawn gives each project a managed Postgres alongside your app, so Claude Code can seed realistic volumes, run EXPLAIN ANALYZE, and prove a pagination query stays fast at depth before it ships. See how it works or join the waitlist.