Pagination Strategies (Cursor vs Offset)
Offset pagination: LIMIT N OFFSET M — client requests page number. Cursor pagination: the server returns an opaque cursor representing the current position; the client passes it back to get the next page.
Offset pagination = telling a librarian 'skip the first 10,000 books, then give me the next 20' (she has to walk past all 10,000). Cursor pagination = using a bookmark (open right where you left off).
Offset pagination is simple to implement and supports random page access, but it has two problems at scale: (1) OFFSET N forces the database to scan and discard N rows before returning results — O(N) cost; (2) it is unstable under concurrent writes — if a row is inserted before the current offset, a row from the previous page appears in the next page, or a row is skipped. Cursor pagination is O(1) — the cursor encodes the sort key of the last row seen (e.g., created_at + id), and the next query uses a WHERE clause: WHERE (created_at, id) > (cursor_created_at, cursor_id). It is stable under writes but cannot support random page access.
Cursor encoding: never expose raw database values as cursors. Encode them as opaque tokens (base64-encoded JSON or encrypted values) to prevent clients from constructing synthetic cursors and to allow changing the underlying sort key without a breaking change. Keyset pagination (cursor based on sort keys) requires that the sort keys be indexed. If the sort is complex (multiple columns, with NULLs), the index design is non-trivial. For bi-directional pagination (next and previous), store both the current cursor and the previous cursor, or use relay-style connection spec (pageInfo with hasNextPage/hasPreviousPage/startCursor/endCursor).
I default to cursor-based pagination for all high-volume list APIs. The cursor is an opaque base64-encoded token containing the last-seen sort key (e.g., `{created_at: '2024-01-01T00:00:00Z', id: 'abc123'}`). The query uses a compound WHERE clause on the indexed columns. I expose offset pagination only for use cases where random access is required (e.g., a paginated admin table where the user can jump to page 50) and document its instability under writes.
Using OFFSET pagination on a table with millions of rows without warning. OFFSET 1000000 causes a full index scan of 1M rows before returning results — query time grows linearly with the offset value.