A created_at-only cursor lost 587 rows, even with clock_timestamp() and microseconds
A reader on one of my Postgres posts asked a fair question: why put id in the pagination index at all? With clock_timestamp() instead of now() , it's hard to create two rows at exactly the same time. So I measured it. The setup PostgreSQL 16, a 4-vCPU machine. The table is the one every app has: CREATE TABLE concurrent ( id bigserial PRIMARY KEY , created_at timestamptz NOT NULL DEFAULT…
A reader inquired about the need for an `id` column in a pagination index. The author measured the effect of using `clock_timestamp()` instead of `now()` in a PostgreSQL 16 environment. The table was filled with concurrent inserts and a bulk insert. The author found that using only the timestamp column for pagination resulted in rows being lost.
This was because when the cursor ended on a timestamp shared by other rows, those other rows were skipped on the next request. Adding the `id` column to the cursor and ordering by both timestamp and `id` resolved the issue, as the tuple comparison `(created_at, id)` ensured each row was returned uniquely. The author suggests encoding the timestamp and a unique tiebreaker in the cursor, and testing for any missing rows in the table.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
