Millisecond Cursors Repeat Pages: timestamptz Precision and Composite Keyset Pagination

Operators pull transaction data from the datafeed API in the game provider prototype I built in September. The API uses a timestamp cursor to page forwards. Duplicate rows showed up at page boundaries in testing because of a precision mismatch: PostgreSQL’s timestamptz keeps microseconds, while the cursor kept milliseconds. Moving the cursor to UnixMicro got the tests passing.

I always saw that as a stopgap. Any cursor built from the timestamp alone breaks once two rows have exactly the same value, and in PostgreSQL the quickest way to produce that is a multi-row insert in one transaction with DEFAULT now(). The fix I’d keep is a composite (created_at, id) keyset. I ran all three approaches on PostgreSQL 18.6.

A millisecond cursor serves the same page again

Here is a cut-down table with six rows. The first five all fall within millisecond .123, and r3 and r4 share the same microsecond too:

CREATE TABLE feed (
  id         bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  payload    text NOT NULL
);

INSERT INTO feed (created_at, payload) VALUES
  ('2026-10-09 04:00:00.123401+00', 'r1'),
  ('2026-10-09 04:00:00.123402+00', 'r2'),
  ('2026-10-09 04:00:00.123403+00', 'r3'),
  ('2026-10-09 04:00:00.123403+00', 'r4'),
  ('2026-10-09 04:00:00.123900+00', 'r5'),
  ('2026-10-09 04:00:00.124100+00', 'r6');

At two rows per page, page one stops at r2. Here is its timestamp in milliseconds and microseconds:

 id |          created_at           | payload |  unix_milli   |    unix_micro    
----+-------------------------------+---------+---------------+------------------
  1 | 2026-10-09 04:00:00.123401+00 | r1      | 1791518400123 | 1791518400123401
  2 | 2026-10-09 04:00:00.123402+00 | r2      | 1791518400123 | 1791518400123402

Page two, using the millisecond value with created_at >:

SELECT id, payload FROM feed
WHERE created_at > to_timestamp(1791518400123 / 1000.0)
ORDER BY created_at, id LIMIT 2;

 id | payload 
----+---------
  1 | r1
  2 | r2

Once truncated, the cursor is .123000, which sorts before r1 and r2, so page two repeats them. When an entire page fits inside one millisecond the client keeps receiving that page. Go’s time.Time carries nanoseconds, so precision is lost when building the cursor: UnixMilli() and JavaScript’s Date stop at milliseconds, as do many JSON time formats, and the last three digits are lost wherever data crosses from one system to the next.

A microsecond cursor skips or repeats equal timestamps

Microseconds fix most cases, but a pair of rows with exactly the same timestamp can still straddle a page boundary. With three rows per page, page one holds r1, r2 and r3, and the cursor is r3’s microsecond value:

-- page 2 with > (cursor = r3 micro)
 id | payload 
----+---------
  5 | r5
  6 | r6

-- page 2 with >= (cursor = r3 micro)
 id | payload 
----+---------
  3 | r3
  4 | r4
  5 | r5

> drops r4, which has the same microsecond as r3, and >= hands r3 back a second time. Neither is acceptable. A missing row tends to go unnoticed for longer; a repeated one can at least be handled by downstream idempotency.

Equal timestamps come up more than you might think. According to the PostgreSQL documentation, now() is transaction_timestamp(), the moment the current transaction began, and it stays fixed for the whole transaction. On a fresh, empty table, insert three rows in one transaction:

BEGIN;
INSERT INTO feed (payload) VALUES ('tx-1'), ('tx-2'), ('tx-3');
COMMIT;
SELECT count(*) AS rows, count(DISTINCT created_at) AS distinct_ts FROM feed;

 rows | distinct_ts 
------+-------------
    3 |           1

All three rows got the same timestamp. Bets and transactions are usually written in batches, so a microsecond cursor on such a table will run into this at a page break sooner or later. clock_timestamp() would give each row a different value, but that makes correct pagination depend on how rows are written, and two connections writing at once can still land on the same microsecond. I wouldn’t rely on it.

Composite keyset on (created_at, id)

Markus Winand’s Use The Index, Luke calls this the seek method. If the sort column can repeat, add a unique column so that every row has a fixed position, and put both values in the cursor. PostgreSQL compares row values directly, so the condition stays a single expression:

SELECT id, payload FROM feed
WHERE (created_at, id) > ('2026-10-09 04:00:00.123403+00', 3)
ORDER BY created_at, id LIMIT 3;

 id | payload 
----+---------
  4 | r4
  5 | r5
  6 | r6

This returns the remaining rows without skips or repeats. The ORDER BY must list the same columns in the same order as the comparison, and the index goes on that pair. After loading 200,000 rows, the plan is:

CREATE INDEX feed_created_id ON feed (created_at, id);
EXPLAIN (COSTS OFF)
SELECT id, payload FROM feed
WHERE (created_at, id) > ('2026-10-09 05:01:00+00', 60000)
ORDER BY created_at, id LIMIT 100;

                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Limit
   ->  Index Scan using feed_created_id on feed
         Index Cond: (ROW(created_at, id) > ROW('2026-10-09 05:01:00+00'::timestamp with time zone, 60000))

The row comparison goes straight into the index condition, so the scan begins at the cursor and only reads forwards, instead of stepping over and throwing away earlier rows the way OFFSET does. Winand notes that Oracle, MySQL and SQL Server 2017 all have significant limitations with row values, and on those you fall back to an approximation such as created_at > ? OR (created_at = ? AND id > ?). PostgreSQL doesn’t need that.

The API can return both values in an opaque string, for instance base64 of micro:id. Clients needn’t inspect it, so the underlying columns can change without an API change.

Rows that commit late can still be missed

A keyset gives a stable order but can still miss data. Say transaction A begins at 10:00:00 and commits at 10:00:05. Its row carries created_at 10:00:00. A client that pages at 10:00:03 doesn’t see that row, moves its cursor past 10:00:00, and never comes back for it. The other side uses the datafeed for reconciliation, so missed rows need attention.

It has to be handled separately from the keyset. A common approach is to let the cursor go no further than “now minus a safety margin”, so clients only see rows that are at least a few seconds old. The margin must exceed your longest write transaction, so measure how long they take. Alternatively, page by a sequence that follows commit order, for example numbers assigned in turn by a single writer.

Notes

timestamptz has microsecond precision. A millisecond cursor is truncated to before the last row on the page, so the next page returns the same rows.
With a microsecond cursor, identical timestamps on a page boundary are skipped with > and repeated with >=.
now() is the transaction start time, so every row inserted in one transaction gets the same timestamp. Tables written in batches hit this first.
A (created_at, id) keyset makes the order deterministic, and PostgreSQL’s row value comparison uses a (created_at, id) index directly.
Keyset pagination doesn’t cover rows that commit late. Keep the cursor behind a safety margin, or use a sequence whose order follows commits.