Why must a keyset pagination ORDER BY end with a unique column?

## The requirement: a total order Keyset pagination works by turning "where was I?" into a value comparison. For that to be sound, the sort key must define a **total order** over the rows: for any two distinct rows, one must come strictly before the other, with no ties. If the sort key is not unique, some rows are tied, and a tied value cannot serve as a boundary because it does not identify where inside the block of tied rows the previous page stopped. ## What goes wrong without a tie-breaker Take a feed of 1,000 posts where 40 of them were bulk-imported and share the timestamp `2024-05-01 10:00:00`. Pages are 20 rows and you order only by `created_at DESC`. Page 3 ends somewhere in the middle of that block, and its last row carries the anchor `2024-05-01 10:00:00`. Now write the next page: ```sql -- Strict: skips rows WHERE created_at < TIMESTAMP '2024-05-01 10:00:00' -- loses the ~20 tied rows not yet shown -- Non-strict: repeats rows WHERE created_at <= TIMESTAMP '2024-05-01 10:00:00' -- re-returns the ~20 tied rows already shown ``` The strict form silently drops the remainder of the tied block — rows that exist, match the filter, and are never shown to anyone. The non-strict form duplicates. There is no third operator that fixes it, because the information needed (which of the tied rows were already emitted) is simply not in the anchor. Worse, ties are not even stably ordered. Nothing in SQL requires two executions of the same query to arrange tied rows the same way; a different plan, a parallel scan, or a data change can reshuffle them. So the block you "already showed" may not even be the same block on the next request. ## The fix Append a unique column and carry it through both the `ORDER BY` and the predicate: ```sql SELECT id, created_at, title FROM posts WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20; ``` Now `(created_at, id)` is unique per row, so the anchor names exactly one row and lexicographic comparison partitions everything else cleanly into before and after. Within the tied timestamp block, `id` decides, and the boundary lands between two specific rows. ## Choosing the tie-breaker Any column that is `UNIQUE` and `NOT NULL` works; in practice it is the primary key, because it is unique by definition and cheap to compare. Requirements worth stating explicitly: - **Unique.** A "nearly unique" column is not enough — one duplicate is one paging bug. - **NOT NULL.** A comparison involving NULL yields UNKNOWN, so the row is not returned. A nullable tie-breaker truncates the traversal at the first NULL. The same applies to every column in the seek key, not just the last one. - **Immutable during a traversal.** If the value can change while the user pages, the boundary moves under them. The tie-breaker must appear in *both* places. Putting `id` in the `ORDER BY` but leaving it out of the predicate leaves the boundary just as ambiguous as before; putting it in the predicate but not the `ORDER BY` makes the filter inconsistent with the sequence the rows come back in, which loses rows in a different way. ## Direction discipline The tie-breaker must sort in a direction consistent with the comparison you write. With `ORDER BY created_at DESC, id DESC`, the seek is `(created_at, id) < (...)`. If you were to write `ORDER BY created_at DESC, id ASC`, the compact row-value comparison can no longer express the boundary — lexicographic comparison runs in one direction only. You would need the expanded form with a different operator per column: ```sql WHERE created_at < :last_created_at OR (created_at = :last_created_at AND id > :last_id) ``` That is correct but harder to read, harder for an index to serve, and buys nothing. Keep every column of the seek key sorting in the same direction unless a product requirement genuinely forces otherwise. ## The cheap-insurance framing Adding the primary key as a final sort column costs nothing a user can perceive: it only decides the order of rows that were tied and therefore interchangeable. It removes an entire class of "a customer says a record disappeared from the list" bugs that are painful to reproduce, because they appear only when a page boundary happens to land inside a block of duplicates. Make it a habit for every paginated query — offset-based ones included, where ties across pages cause the same duplication and skipping.