Pagination looks trivial until your dataset grows past a million rows, users complain about duplicate or missing items, and your database CPU pins at 100%.
Pagination is one of the most consequential API design decisions and one of the most reflexively defaulted. Most APIs start with `?page=1&limit=50` because it's what the ORM makes easy, then discover a year later that page 500 takes 8 seconds, duplicate items show up when data is being inserted, and any change to sort order silently breaks pagination for every client. The three main approaches — offset, keyset, and opaque cursor — have very different performance and correctness properties. Choosing deliberately at API design time avoids expensive migrations later.
`SELECT * FROM items ORDER BY created_at LIMIT 50 OFFSET 10000` reads and discards 10,000 rows before returning 50. Cost scales linearly with page number. On a well-indexed table with 10M rows, page 200,000 can take seconds. Worse, it's not stable: if rows are inserted or deleted during pagination, users see duplicate items on one page and missing items on the next. Acceptable when: dataset is small (< 10K rows), users rarely paginate deep, dataset is nearly static. Not acceptable for feeds, activity logs, or anything where clients paginate to the end.
Instead of `OFFSET`, use the last-seen key as a filter: `WHERE (created_at, id) > (:last_created_at, :last_id) ORDER BY created_at, id LIMIT 50`. With an index on (created_at, id), each page is O(log n) regardless of depth. Requires: a sort key that is unique and monotonic (or a tiebreaker like id when the primary sort isn't unique). Trade-off: no random-access to page N; clients must page sequentially. Best for infinite scroll, activity feeds, and anything ordered by time or id.
Return a `next_cursor` string in each response; client sends it back to get the next page. Internally the cursor encodes whatever the server needs (keyset values, filters, sort direction, schema version). This gives you: (a) freedom to change the underlying pagination strategy without breaking clients, (b) filter+sort stability (the cursor pins them), (c) ability to detect and reject stale cursors after a schema change. Cursors should be opaque to clients (base64-encoded JSON is fine; do not document the internal structure). This is the recommended default for new REST APIs.
(1) Total counts — computing `total` on every page request kills performance on large tables. Return approximate counts (`total_estimate` from the query planner) or drop total entirely for feed-style endpoints. (2) 'Has next page' — return a boolean or return N+1 items and slice, rather than requiring the client to guess. (3) Filter changes mid-pagination — with opaque cursors, either encode the filter into the cursor (safe) or return an error when it changes (safe); silently continuing with the new filter produces bugs clients cannot diagnose. (4) Deletes during pagination — keyset naturally skips deleted items; offset silently reorders. (5) Ordering by non-unique fields — always add a unique tiebreaker (usually id) or duplicate items will appear at page boundaries.
The Relay Cursor Connections spec formalized opaque cursor pagination for GraphQL: `first`/`after` and `last`/`before` arguments, a `pageInfo` object with `hasNextPage`/`hasPreviousPage`/`startCursor`/`endCursor`, and edges wrapping nodes. It's verbose but battle-tested; most GraphQL clients (Apollo, Relay, urql) support it natively. Adopt it wholesale for GraphQL APIs rather than inventing your own. For REST, mimic the same shape: `{ data: [...], page_info: { next_cursor, has_more } }` translates cleanly.
Investor directory · Fundraising library · Articles A–Z · Company funding database