LEARN. PRACTICE. GROW.The first decision is ordering
A task list ordered only by time can have ties. If the application divides results into pages, it needs a total order, such as occurred_at plus a unique id. Having an index does not remove the need for ORDER BY in the query delivering the page. Sorting afterward in the browser also does not repair which rows the server selected for each page. Write a dataset with three tasks at the same time and check that the tie-break is explicit. This guarantee applies to the observed set: well-defined order does not automatically freeze data across two HTTP requests. Discuss the consistency contract separately.
Position and continuation differ
OFFSET counts positions in the current query result. If the first page of 10,20,30,40 delivers 10,20 and 5 is then inserted, OFFSET 2 can return 20,30. If 10 is deleted instead, the same offset can skip 30. Continuation using id>20 uses the last delivered value, avoiding those prefix shifts while the key remains unchanged. It does not mean every subsequent change will be seen: a new id=15 lies before the cursor. Choose between a dynamic list, fixed export, and historical query before promising no repetition or complete coverage. Each product may need a different policy.
The predicate must reproduce ordering
For t ASC,id ASC without NULLs, comparison (t,id)>(last_t,last_id) describes lexicographic continuation. If t ties, id decides. For mixed ordering score DESC,id ASC, use a condition equivalent to lower score or equal score with larger id. A simple comparison of both fields in one direction does not represent that order. NULL values need additional care: placing them last with NULLS LAST does not make an unknown comparison true. Explicitly define the NULL group and how to continue within and beyond it, or choose a suitable required key. The cursor must preserve needed values without losing precision or changing representation.
Cursor, filters, and authorization
A cursor from a list filtered to tenant A should not silently be reused as continuation for tenant B. Define cursor context: relevant filters, ordering direction, and the last displayed key. The application must check compatibility and authorization on each request. Even a signed cursor does not prove that the current user remains authorized; the signature can only protect integrity under the adopted design. This is an application-design rule, not an automatic SQL comparison property. To detect another page, request N+1 rows and display N. The next cursor should use the last displayed row, preserving the extra row for the next read.
Laboratory: break ties without losing rows
The example uses synthetic integer timestamps and four tasks. Cursor (10,2) was already displayed; predict that the next page contains (10,3) and (11,1). Change the predicate to t>10 and observe the lost tied task. Then use >= in the composite comparison and observe repetition of the cursor. These isolated trials expose boundary errors without depending on a web application. In another variant, insert a row before the cursor between two reads and compare continuation with OFFSET. Results are executed in a portable SQLite subset. They do not establish production PostgreSQL plans, cost, isolation, or performance, which require separate trials.
Summary: define the observed set
When a user reports repeated rows, collect ordering, filters, cursors, and changes between requests. A mutable key can move an already delivered task beyond the cursor, making it appear again. For a fixed-set export, consider a consistent observation or a materialized result with a defined lifecycle and resource budget. Do not hold a transaction open indefinitely merely to simplify the interface. In PostgreSQL, isolation level and transaction boundaries influence observation; separate transactions do not automatically share a snapshot. Connect this lesson to reconciliation, isolation, and indexes: selection correctness and execution efficiency require different evidence.
WITH tasks(t,id) AS (
VALUES (10,1), (10,2), (10,3), (11,1)
)
SELECT t,id
FROM tasks
WHERE (t,id) > (10,2)
ORDER BY t,id
LIMIT 2A row inserted before the previous position can repeat OFFSET results; the continuation key avoids that specific shift.
Common pitfalls
Do not omit tie-breaks, use the extra row as cursor, handle NULL only in sorting, or promise a snapshot from a cursor alone.
Related topics: Count and group carefully · Indexes and execution plans · Reconciliation, metrics, and query diagnosis
Ordering, predicate, and cursor must agree; stability of the set requires a separate consistency contract.
Reference: Pagination and limits · PostgreSQL 18 reference semantics; DR SQL 2026.4; synthetic plan metrics and portable SQLite 3.51.2 examples