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 clock_timestamp(),
n int
);
Then I filled it two ways:
-
Concurrent inserts:
pgbenchwith 16 clients, oneINSERTper autocommit transaction, for 20 seconds (about 29,000 inserts a second). -
One bulk insert:
INSERT ... SELECTof 1,000,000 rows, like an import or a backfill.
How many timestamps collided
| Table | Rows | Distinct created_at
|
Rows sharing a timestamp |
|---|---|---|---|
| Concurrent inserts | 583,817 | 572,945 | 10,872 |
| Bulk insert | 1,000,000 | 869,925 | 130,075 |
timestamptz stores microseconds. Two rows written in the same microsecond get the same value, and on a busy table that happens thousands of times. clock_timestamp() makes it rarer than now(), which is fixed for the whole transaction, but it does not make it unique.
What it does to keyset pagination
Then I walked each table newest-first, 20 rows a page, the way an infinite-scroll API does, with two cursors:
-- cursor on the timestamp only
WHERE created_at < $last_created_at
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- cursor on (timestamp, id)
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
| Table | Cursor | Rows returned by walking every page | Rows lost |
|---|---|---|---|
| Concurrent inserts |
created_at only |
583,230 of 583,817 | 587 |
| Concurrent inserts | (created_at, id) |
583,817 of 583,817 | 0 |
| Bulk insert |
created_at only |
993,090 of 1,000,000 | 6,910 |
| Bulk insert | (created_at, id) |
1,000,000 of 1,000,000 | 0 |
Why rows disappear
When a page ends on a row whose timestamp is shared, the other rows with that timestamp are on the far side of the cursor. The next request asks for created_at < last_created_at, strictly less, so they are skipped. No error, no duplicate, just a user who never sees a few posts in their feed.
Using <= instead doesn't fix it: then the rows that were already shown come back on the next page as duplicates, and if more than a page shares one timestamp, the walk never advances.
The id makes the sort key unique. (created_at, id) < ($1, $2) is a row comparison: "strictly before this exact row", so every row is returned once.
The fix
CREATE INDEX CONCURRENTLY idx_posts_created_id
ON posts (created_at DESC, id DESC);
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;
- Put both columns in the cursor you hand to the client, and both in the
ORDER BY. - Keep the row comparison as one tuple; Postgres can use the composite index for it directly.
- Any unique, stable column works as the tiebreaker. The primary key is the obvious one.
How to check your own table
SELECT count(*) - count(DISTINCT created_at) AS rows_sharing_a_timestamp
FROM posts;
If that's above zero and your cursor holds only the timestamp, some users are missing rows today.
Checklist
- Cursors for keyset pagination encode the timestamp and a unique tiebreaker.
-
WHEREuses the row comparison(created_at, id) < (...), nevercreated_at <alone. -
ORDER BYand the index list the same columns in the same direction. - A test walks every page of a table with duplicate timestamps and checks no row is missing or repeated.
Making Claude Code write it this way
Ask for "cursor pagination" and you often get a timestamp-only cursor, because it looks complete and passes a small test. The cursor-pagination skill in my Claude Code skills pack for TypeScript + GraphQL + Postgres backends always uses a unique tiebreaker, and the pack's starter backend tests a full page walk in CI.
- Two skills are free (GraphQL N+1 fixes and safe Postgres migrations): github.com/adnan909/free-sample
- The full pack, with the cursor-pagination skill, 6 more skills and the starter backend: Backend Skills Pack on Gumroad
Top comments (0)