Pagination
Returning large result sets in pages rather than all at once, bounding response size and DB work. Keyset (cursor) pagination is stable and fast; offset pagination is simple but slow and skips rows on inserts. Choose by data and access pattern.
- Keyset for infinite scroll and large/live tables.
- Offset for admin UIs jumping to a page on small data.
- Offset on large or frequently-changing tables.
- Keyset when you need arbitrary page jumps.
Definition
Pagination splits a collection into pages via a limit and a cursor/offset. Offset uses LIMIT/OFFSET (skips N rows); keyset uses a WHERE on an indexed column (WHERE id > last). Cursor pagination encodes the position opaquely.
Why it exists
Returning everything blows up memory, latency, and bandwidth. Pagination bounds each response and lets clients fetch incrementally.
How it works
Offset: page=N, limit=L → SQL OFFSET N*L. Keyset: WHERE id > last_id ORDER BY id LIMIT L. Cursor APIs return a next_cursor token. Keyset is O(L) using an index; offset is O(N+L) because it scans and discards.
Scaling limits
Offset pagination degrades badly on deep pages (OFFSET 1000000 scans a million rows) and skips/duplicates rows if data changes between pages. Keyset is stable and fast but can't jump to an arbitrary page.
Cost shape
Offset is cheap to implement but expensive at depth. Keyset needs an indexed, monotonic ordering column. Total counts are expensive on large tables.
Alternatives
- Offset pagination — Simple, jumpable, but slow at depth and unstable under writes.
- Keyset/cursor — Fast and stable, but no random access and needs a unique order.
Failure modes
- Offset skipping new rows during pagination.
- Cursor not encoding ordering, causing duplicates.