Compare offset pagination and cursor (keyset) pagination.

Summary

Offset pagination (?page=3&size=20 → OFFSET 40 LIMIT 20) is simple and allows jumping to pages, but gets slow on deep pages and can skip or duplicate items when data changes. Cursor pagination returns an opaque cursor encoding the last seen sort key (WHERE (created_at, id) < (...) LIMIT 20), which is fast and stable but only supports next/previous.

Longer Answer

Use cursors for feeds, large tables and APIs consumed by machines; offsets for small admin lists that need page numbers. Always cap page size, use a deterministic sort with a unique tiebreaker, and return next_cursor (or Link headers).

Code Example

-- keyset pagination: next page after (created_at, id) of the last item
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Key Points

• Offset: simple, slow when deep, unstable
• Cursor: fast, stable, sequential
• Deterministic sort + tiebreaker
• Cap page sizes