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.