I had a test that asserted orders.first().id == 9917. It passed locally for months. Then it failed in CI, twice in a row, then passed again on a rerun. Nobody had touched the query.
The query was this:
SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 1;
The test seeded three orders for one customer inside a single transaction. All three got created_at = now(). now() is transaction_timestamp() — it does not advance during a transaction. So three rows shared the exact same timestamp to the microsecond.
ORDER BY created_at DESC gives one value. Three rows tie on it. Postgres picks whichever it happens to read first. That order depends on the plan, the physical row order, the visibility map, and whether it chose an index scan or a seq scan. Local Postgres and CI Postgres do not make the same choice.
The standard does not promise you anything here
SQL does not define which row comes back when the sort key ties. The ORDER BY clause establishes a partial order, not a total one. Two rows with equal keys are "equal" for sorting purposes, and the engine may emit them in any order. No standard text says otherwise.
This is not a Postgres bug. It is Postgres behaving correctly. The bug is in the query, and then in the test that trusted it.
LIMIT 1 makes it worse. Without the limit you would see the instability — the row order would visibly shuffle between runs. LIMIT 1 hides it and turns it into a coin flip that lands on heads in your terminal.
Pagination is where this stops being a test problem
I hit this again in a feed endpoint. Keyset pagination over created_at alone:
SELECT id, created_at, body
FROM posts
WHERE created_at < $1
ORDER BY created_at DESC
LIMIT 20;
A backfill wrote a chunk of posts with the same created_at, all from one job running now() in one transaction. The first page ended at created_at = '2026-09-14 10:03:22.481913+00'. The next page asked for everything strictly before that value. Every post sharing that timestamp got skipped. Users saw a gap in the feed and I spent an afternoon blaming the client.
Flip the comparison to <= and you get the mirror failure: the boundary rows repeat on every page. Offset pagination has the same shape. OFFSET 20 counts rows in an order the database never promised, so a row inserted or reordered between requests can push another row across the page boundary.
Add a unique column to the sort key
The fix is one line and it is not optional. Every ORDER BY that feeds a LIMIT, an OFFSET, or a cursor needs a unique tiebreaker. The primary key is right there.
ORDER BY created_at DESC, id DESC
Now the sort key is unique. (created_at, id) is a total order over the table. Ties cannot exist, so the engine has no freedom left to exercise. LIMIT 1 returns exactly one row, deterministically.
Pagination needs the same treatment in the predicate, not just the sort:
SELECT id, created_at, body
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Row-value comparison does the lexicographic work in one expression. Pass the last row's created_at and id as the cursor. Nothing is skipped, nothing repeats.
For the index, an index on (created_at DESC, id DESC) matches the sort directly. Check with EXPLAIN (ANALYZE, BUFFERS) — if you see a Sort node above the scan, the index is not matching your order and you are paying for it on every page.
Make it a rule
A tiebreaker is not a stylistic preference. It belongs in review. Two habits make it stick for me:
- If I write
ORDER BY, I write the unique column in the same keystroke.ORDER BY created_at DESCalone looks unfinished now. - Any test that reads
.first()or.last()from a query must sort on a unique key, or it is not testing the query — it is testing the plan.
I also stopped seeding multiple rows with now() when the test cares about their order. clock_timestamp() advances; now() does not. Better yet, set explicit timestamps in the fixture so the data has the order the test claims to assert.
The failure mode is quiet. Nothing errors, no constraint fires, no log line appears. You get a wrong row, a missing row, or a green test that owes you a red one later. Add the tiebreaker.
I write about production failures in Postgres, queues, and distributed systems.
