Sorting: Determinism or Drift

SQL makes a promise weaker than intuition expects: ORDER BY created_at DESC constrains *only* the relative order of rows with different created_at values. Fifty orders imported in the same batch share a timestamp, and the database may return them in any order — a different order per execution, because plans, parallelism and page layout all legally influence it. Unpaginated, this is invisible. Paginated, it is corruption: page 1 ends inside the tie with rows {A, B}, the next request re-executes the query, the tie shuffles, and page 2 begins with B again while C was never returned at all. No writes occurred; the API simply never promised a total order, and pagination silently required one.

The fix costs six characters: a unique tiebreaker. ORDER BY created_at DESC, id DESC makes every comparison decidable, every ordering total, every page boundary a fixed point. The rule is absolute in a way little in API design is: every ordering the API exposes ends in a unique key, and the tiebreaker is part of the contract (and part of the cursor — a keyset cursor is built from exactly these columns, see Cursor Pagination: An Opaque Bookmark, Not a Position). This bug is a unit-test escape artist — test fixtures rarely produce ties, production batch imports always do — which is why the rule must live in the contract and the code review checklist, not in the test suite.

Determinism has a second, softer half: stated defaults. An endpoint with no documented default order still returns rows in *some* order, and clients bind to it — the classic case is "the API returns insertion order" (actually: whatever the storage engine did) breaking the day a migration rewrites the table (see What an API Contract Actually Is on accidental promises). Every list endpoint documents its default ordering, and the default obeys the same tiebreaker rule as everything else.

A sortable column is a promise that the database can produce the collection in that order affordably — which at scale means an index whose order matches, because the alternative is sorting the whole result set on every request just to return its first page. ?sort=-total on 40 million orders without an index on total is a top-N-over-everything operation per call. Like Filtering: An Allowlist With an Index Bill, the honest design is a short allowlist: each entry in ?sort= maps to an index decision someone made on purpose, and unknown sort fields are rejected with a field-level error, not served with a filesort.

The bill compounds where sorting meets filtering, because the index that serves status=paid ORDER BY created_at DESC is the composite (status, created_at) — neither single-column index does the job alone. Every (filter-combination × sort-option) cell you promise is a query plan; leftmost-prefix and range-then-sort rules decide which cells one composite index covers. This is why mature APIs offer two or three sort options on their busiest collections, not fifteen: each option multiplies across the filter surface, and the write amplification of the index family is the real constraint (see API Performance: The Levers You Actually Own for where this cost surfaces).

Two semantic clauses complete the contract. Direction: pick one syntax (?sort=-created_at or ?sort=created_at&order=desc), use it everywhere (One Vocabulary: Naming and Consistency — clients write one sort-param builder). Edge semantics: where do NULLs sort, and how is text compared? ORDER BY discount with NULLs, or case- and locale-sensitive name sorting, differ across databases and even collation configs — if a consumer can observe it at a page boundary, it is contract, and one documented sentence beats a cross-database surprise during your next storage migration.

Deterministic ordering makes page boundaries stable; it does not freeze the world. Sorting by a *mutable* field (?sort=-updated_at, or by status) means rows legitimately teleport across pages as they change — a row updated mid-traversal moves to the front the traverser already passed, and a paginating client never sees it. That is not a bug in the ordering; it is a property the contract should name, and a reason completeness-critical consumers should traverse by an immutable ordering (created_at, id) even when the UI displays something else (see Consistency as a Contract Clause). Immutable-key traversal is also what makes long-running exports coherent.

Some orderings are computed rather than stored — "most relevant", "recommended", "trending". These break the machinery quietly: there may be no index to walk, scores can change between page requests (drift again, by another door), and a keyset cursor needs the score *in* the key to seek. The honest options are to materialize the score so it behaves like a column, snapshot the ranked result per traversal (a search-style scroll context), or declare the ordering shallow — top-100 only, no deep pagination, which for "trending" is usually the true requirement anyway. Relevance ordering is enough of its own contract that it gets its own lesson (see Search Is a Different Contract Than Filtering).