Pagination¶
Pagination returns a large ordered result set in bounded pages. It is a data consistency contract as well as a response-shaping technique. Every approach requires deterministic ordering, normally with a unique tie-breaker.
Offset, keyset, and cursor pagination¶
| Approach | Request | Strength | Trade-off |
|---|---|---|---|
| Offset | limit plus offset or page number |
Random page access and simple UI | Deep scans and shifts under concurrent writes |
| Keyset | Last ordered values | Efficient continuation over an index | No arbitrary jump; predicate follows sort order |
| Opaque cursor | Server-defined continuation token | Can hide compound state and evolve | Token integrity, expiry, and compatibility are required |
For order (created_at DESC, id DESC), the keyset continuation predicate is
conceptually:
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size
The exact tuple syntax and index support depend on the database. Without the
unique id tie-breaker, equal timestamps can cause duplicates or omissions.
Consistency under change¶
Offset pagination refers to positions, so inserts and deletes before the next offset shift results. Keyset pagination continues relative to values and is usually more stable, but updates to ordering fields can still move records. A true point-in-time traversal requires snapshot or version semantics that may be expensive to retain.
A cursor should be opaque to clients, integrity-protected if it carries state, bound to the filters and sort order, and given an expiry policy. Never embed unprotected sensitive data in it.
API and database rules¶
- cap page size and reject invalid limits;
- document default order and continuation behavior;
- perform filtering and pagination in the data store, not after loading all rows;
- select only needed columns and verify an index supports filter plus order;
- avoid promising an exact total count when computing it is prohibitively costly;
- represent the next link or cursor independently from internal entity identity.
Test equal sort values, empty and final pages, deleted anchors, concurrent inserts, changed filters, malformed cursors, and maximum page size. Inspect the query plan and latency at deep positions rather than judging only the first page.