Return a slice of a result set instead of all of it. Page through real data, then discover the two traps: pagination without ORDER BY, and why large OFFSETs get slower and slower.
A query without ORDER BY has no guaranteed result order. If rows can tie on the sort key, add a unique tie-breaker so that repeated requests produce a stable sequence.
This requests the third page of twenty rows. The unique order_id resolves ties between orders created at the same time. Without it, rows may move between pages even if the data changes little.
With a large offset, the engine may still need to find and skip all preceding rows before returning the page. The work can grow as a user moves deeper into a large result set. Concurrent inserts and deletes can also shift offsets, causing repeated or skipped rows across page requests.
For feeds with a stable key, keyset pagination can instead ask for rows after the last item from the previous page:
Tuple comparison and NULL behavior differ by engine; the equivalent can be written as explicit conditions. Keyset pagination is efficient for sequential browsing but does not directly jump to an arbitrary page number.
Decide whether users need a total count, a stable snapshot, random page access, or just “load more.” Exact counts can be expensive and may change while paging. An index matching the filter and order can help; inspect a plan and benchmark. See indexes and ORDER BY.
Suppose page one ends with item 20. Before page two is requested, five new records are inserted at the front. OFFSET 20 now starts at a different item, so the user may see repeats. A keyset cursor anchored on the last ordering key avoids this shift for forward navigation, though updates to sort keys can still move rows. If pages must represent one consistent snapshot, keep a transaction or use a snapshot/version token where supported; long-lived snapshots have operational costs.
A keyset cursor must contain every expression needed to define a unique sort position. If results are ordered by score descending and then ID ascending, the cursor must carry both score and ID, and the predicate must respect the opposite direction for the ID tie-breaker. Nullable sort keys need explicit handling. Treat the cursor as opaque to the client or validate it before use. This prevents malformed cursor values from changing query logic and keeps pagination behavior consistent across requests.
LIMIT and OFFSET.About to run: set OFFSET (rows skipped) to its maximum (20). Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “A page is an ordered slice”?
What does this module say about “Deep offsets have a cost”?
What does this module say about “Define what “page” means”?
Page through a result set with LIMIT and OFFSET, and hit the two traps: missing ORDER BY and the linear scan at high page numbers.