LEARN. PRACTICE. GROW.Design a verifiable sequence
The teaching dataset starts with six rows and two ordering keys: sort_key and id. Ids 1 and 2 both have sort_key=10. The query orders by both fields and returns two ids per page. Before introducing changes, the script confirms [1,2] and [3,4]. It then uses a second session to change data between queries, with controlled command ordering. This is neither a scheduler-dependent race nor HTTP traffic. It is an actual SQL sequence that attributes each result change to a known mutation. Preserve that ordering when reproducing an incident before discussing a fix.
Observe offset displacement
After the first page, the second session inserts id 7 with sort_key=5. OFFSET 2 now returns [2,3]: id 2 was already seen. In a separate experiment, deleting id 1 after page one makes the same query return [4,5], skipping the unseen id 3. LIMIT remains correct in both cases because each response contains at most two rows. The problem lies in positional meaning when the population changes. Client-side id deduplication can hide repetition but does not recover an omitted row. The contract must state whether it accepts this instability or requires another traversal method.
Retain the complete boundary
The example cursor retains (sort_key,id). After [1,2], the predicate (sort_key,id)>(10,2) returns [3,4], even after inserting a row before that boundary. Another group reduces page size to one. Retaining only sort_key=10 and requesting sort_key>10 returns id 3 and omits id 2, which shares the key. With boundary (10,1), the correct next result is id 2. Adding id to ORDER BY is insufficient if the cursor loses it. Comparison and ordering must represent the same sequence. Both fields are non-null in this laboratory; a separate expression shows why NULL needs its own contract.
Test keys that can change
A cursor does not make its key immutable. The script changes id 1 sort_key to 60 after that id appeared. The query following (10,2) finds it again at the end. In a separate case, unseen id 3 moves to sort_key=5 and disappears from the remaining traversal. These are two effects of the same freedom to update. If a listing orders by last modification, this behavior may belong to its intended experience. A complete export requires a different contract. Assess a stable key, retained view or materialized result according to the requirement, without turning keyset into a universal promise of omission-free reading.
Separate progress from a snapshot
The experiment stores max(id)=6 as an upper boundary and then inserts id 7. The boundary excludes the new id, but changing id 4 units from 400 to 999 remains visible. The report shows both properties together. A watermark can help define a population but does not automatically preserve that population values. It also does not prove that id order matches commit order in a real system. In an ADR, state which field marks progress, how it is assigned and which changes are allowed. If consumers need values from a common instant, include a consistent-view strategy and validate it separately.
Apply this to APS diagnosis
In a fictional incident, users export operations across multiple pages and report repeated ids. Obtain effective ordering, page size, predicate, received cursor and a mutation timeline. Distinguish server-produced repetition from a client retry of the same page. Do not conclude that removing duplicates establishes completeness. Also define how to resume an interrupted export and which population the result represents. The laboratory offers small sequences for discussing these hypotheses with development and business teams. The real application still needs authorization, transport-error, contract-evolution and representative-load validation.
python3 content/labs/design-pagination/run.py --postgres-prefix /path/to/postgresql-18.6 --output /tmp/dr-pages-new.json
# Choose a new output path. The script creates and stops its own private cluster.An insertion before the first page makes OFFSET repeat id 2; deleting an earlier row makes it skip id 3.
Common pitfalls
Ordering without a tie-breaker, timestamp-only cursors, mutable ordering keys and snapshot promises without retaining one view.
Related topics: Data and consistency · API contracts
A cursor defines a search boundary. Identity, ordering stability and the data view determine what appears next.
Reference: LIMIT and OFFSET · System design patterns; PostgreSQL18 scoped examples; primary guidance consulted 2026-09-30