DEV Community

Cover image for A created_at-only cursor lost 587 rows, even with clock_timestamp() and microseconds
Muhammad Adnan
Muhammad Adnan

Posted on

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 clock_timestamp(),
  n int
);
Enter fullscreen mode Exit fullscreen mode

Then I filled it two ways:

  1. Concurrent inserts: pgbench with 16 clients, one INSERT per autocommit transaction, for 20 seconds (about 29,000 inserts a second).
  2. One bulk insert: INSERT ... SELECT of 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;
Enter fullscreen mode Exit fullscreen mode
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;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode

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.
  • WHERE uses the row comparison (created_at, id) < (...), never created_at < alone.
  • ORDER BY and 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.

Top comments (0)