LIMIT/OFFSET pagination is easy until tables grow and rows shift while users page. Keyset pagination (cursor-based) stays stable and faster for deep pages.
What goes wrong with OFFSET
- Large offsets force the database to scan and discard many rows.
- If rows are inserted or deleted while paging, clients can skip or see duplicates across requests.
Keyset pattern
Order by a unique, indexed column (often (created_at DESC, id DESC)). Client sends the last seen tuple as cursor; next query uses WHERE (created_at, id) < (?, ?) (tuple comparison) instead of OFFSET.
Encoding cursors
Opaque-base64 JSON or signed payloads prevent clients from forging invalid states. Do not expose raw internal ids if policy forbids it — use UUIDs or encrypted tokens.
When OFFSET is fine
Small admin tables, rare paging, or read-mostly snapshots where consistency is relaxed.
Total count
Exact counts on huge tables are expensive; return has_more or approximate counts if product allows.