SQL Querying
Ordering and Pagination
ORDER BY is the only query clause that guarantees result sequence. Pagination must use a total, stable ordering; offset pages become slower and can shift under concurrent changes, while keyset pagination resumes after the last seen key.- Without
ORDER BY, row order is unspecified even if a plan or index happens to produce a repeatable sequence today. - A deterministic order needs a unique tie-breaker.
- Offset pagination still processes or skips earlier rows and its cost usually grows with deeper pages.
- Offset pages are unstable under writes.
- Keyset pagination compares the ordered key tuple with the last row from the previous page and is well suited to forward navigation.
- Collation and null placement are part of ordering semantics and vary by database unless made explicit or avoided in cursor keys.
Stable pages start with a total order
| Property | Offset | Keyset / seek |
|---|---|---|
| Boundary | Row position | Last ordered key values |
| Deep-page work | Often scans/skips prior rows | Can seek near the boundary with a matching index |
| Concurrent insert before boundary | Shifts later offsets | Does not move the saved key boundary |
| Random page number | Natural | Not natural without stored boundaries |
| Required ordering | Deterministic total order | Deterministic total order matching comparison |
SELECT order_id, placed_at, status
FROM orders
ORDER BY placed_at DESC, order_id DESC
LIMIT 25 OFFSET 0;SELECT order_id, placed_at, status
FROM orders
WHERE (placed_at, order_id) < ($1, $2)
ORDER BY placed_at DESC, order_id DESC
LIMIT 25;