Pagination (offset vs cursor-based)

Pagination returns large result sets in chunks (pages). Offset-based uses LIMIT n OFFSET m (page 1, 2, 3…). Cursor-based uses a stable pointer (e.g. last item’s ID or timestamp) and fetches “next page after this cursor,” which avoids skipped/duplicate rows when data changes.

Offset-based: page number

flowchart LR R[Request page 3] --> Q[SELECT ... LIMIT 10 OFFSET 20] Q --> DB[(DB)] DB --> Skip[Skip 20 rows, return 10] Note[Problem: if rows inserted/deleted, offset shifts]

Cursor-based: next after this key

flowchart LR R[Request after cursor id=42] --> Q[SELECT ... WHERE id > 42 ORDER BY id LIMIT 10] Q --> DB[(DB)] DB --> Stable[Stable: same rows even if data changes]

Aspect	Offset	Cursor
API	`?page=3&limit=10`	`?after=id_xyz&limit=10`
Jump to page	Yes (e.g. page 5)	No (sequential next only)
Consistency	Can skip/duplicate if data changes	Stable across inserts/deletes
Performance	OFFSET 10000 = scan & skip 10000	Index seek on cursor key

Use offset for simple UIs where “page 2” is enough and data is relatively static. Use cursor-based for feeds, infinite scroll, and large tables where consistency and performance matter.