Postgres keyset cursors: preserve timestamp precision

If Postgres keyset pagination repeats or skips rows near a timestamp boundary, inspect the cursor before changing the index. The database may order by microseconds while the API sends back only milliseconds. The next query then resumes from a different position. A valid JSON token and a unique ID do not repair that mismatch. ^[1][2][5]

A tie-breaker cannot repair a timestamp that moved during serialization.

Consider a fictional activity feed. Each activity has an immutable creation instant and a unique integer ID. The endpoint orders oldest first and returns a continuation token after a short page. The first page looks correct. The second returns a record the client already saw. There is no SQL error because both the rounded timestamp and the comparison are legal inputs.

This is a correctness problem before it is a performance problem. An export that silently repeats an activity might later deduplicate it; an export that silently skips one may never notice. The useful question is whether the API's encoded boundary denotes the same tuple the database just returned. Follow that tuple through every conversion.

Why can a valid cursor repeat a row?#

The example below deliberately places activities inside the same millisecond and gives them IDs whose numeric order differs from timestamp order. This keeps the timestamp comparison visible. The SQL creates a temporary table for a disposable session. It does not alter an application schema or represent measured production data.

CREATE TEMP TABLE activity_cursor_demo (
  id bigint PRIMARY KEY,
  created_at timestamptz NOT NULL
);
INSERT INTO activity_cursor_demo VALUES
  (90, '2026-10-01T12:00:00.123100Z'),
  (10, '2026-10-01T12:00:00.123900Z'),
  (20, '2026-10-01T12:00:00.124000Z');

SELECT id, created_at
FROM activity_cursor_demo
ORDER BY created_at ASC, id ASC
LIMIT 1;

The expected first ID is 90. The timestamp belongs to that exact row. If an API converts it to a JavaScript Date and serializes the Date, the fractional part becomes .123Z. That value precedes the original .123100Z boundary. A next-page predicate using the shortened timestamp can therefore match the first row again. The node-postgres documentation explicitly describes this microsecond truncation. ^[5]

-- Wrong boundary after a millisecond-only round-trip:
SELECT id FROM activity_cursor_demo
WHERE (created_at, id) >
  ('2026-10-01T12:00:00.123Z'::timestamptz, 90)
ORDER BY created_at ASC, id ASC;

-- Exact boundary from the last returned row:
SELECT id FROM activity_cursor_demo
WHERE (created_at, id) >
  ('2026-10-01T12:00:00.123100Z'::timestamptz, 90)
ORDER BY created_at ASC, id ASC;

The intended comparison results are 90,10,20 for the rounded boundary and 10,20 for the exact one. Those are deductions from the fixture, not benchmark observations. PostgreSQL compares row elements left to right. The ID is consulted only when the timestamp elements are equal; it cannot rescue a timestamp that is already greater than the cursor timestamp. ^[1]

Carry the exact sort tuple through the API#

PostgreSQL timestamps support microsecond resolution. A useful cursor serialization therefore preserves the full fractional value, even if the interface displays a shorter date. Choose a canonical representation with a stated time zone. Avoid relying on session-dependent timestamp formatting when the same token may be consumed by another application instance. ^[2]

For this feed, select an extra string field expressly for cursor construction. The following projection normalizes the instant to UTC and formats the fractional seconds without passing through the client's date parser. The ordinary created_at field can still use the application's existing display representation. The cursor-only field should remain separate in the data-access layer. The formatting template uses US for microseconds and quoted literal time separators. ^[6]

SELECT id::text AS cursor_id,
       created_at,
       to_char(created_at AT TIME ZONE 'UTC',
               'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS cursor_time
FROM activity_cursor_demo
ORDER BY created_at ASC, id ASC
LIMIT 2;

Put the exact database timestamp string and the last returned ID in the continuation token.

Treat the cursor as a versioned API input. For this illustrative design, its decoded data contains an order identifier, an exact timestamp string, an ID string and the filter scope. Encoding makes the token convenient to transport; it does not make it trustworthy. Validate its structure and allowed order, and bind its values as query parameters rather than interpolating them into SQL.

Keep integer IDs as strings across JavaScript boundaries when their database range can exceed safe integer representation. That is a separate loss-of-information problem with the same consequence: the tuple received by the continuation query must equal the tuple selected by the prior page. Do not fix timestamps while letting the ID serializer change the other half.

An endpoint can reject cursors created under an obsolete ordering contract. That is often clearer than guessing whether an old millisecond token originally represented a timestamp with additional digits. Document token expiry or version behavior for clients. If you support a transition period, test the compatibility branch as a separate contract rather than silently treating both formats as equivalent.

What belongs in the continuation query?#

For a real tenant-scoped activity table, carry the same authorization and filter predicates into every page. The cursor is a position within that result set, not permission to read another result set. Reapply the authenticated tenant restriction from server context; a tenant identifier in the token is only a consistency check.

-- Illustrative parameterized application query.
SELECT id::text AS cursor_id, created_at,
       to_char(created_at AT TIME ZONE 'UTC',
               'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS cursor_time
FROM activity
WHERE tenant_id = $1
  AND (created_at, id) > ($2::timestamptz, $3::bigint)
ORDER BY created_at ASC, id ASC
LIMIT $4;

The order is total because the non-null timestamp is followed by a unique ID. The predicate uses the same ascending tuple. For descending traversal, both ordering directions and the comparison must change together. Mixed ascending and descending columns require an explicitly derived predicate; copying this row comparison into that case can change which rows qualify. ^[1][3]

A plausible index begins with an equality-filtered tenant and continues with the ordering columns. Whether it is the right index depends on the real query, selectivity, joins and projected columns. Inspect both the initial query and a continuation query with representative data. A fast first page is not evidence that the next-page predicate has a good access path. ^[4]

Keep nullable sort values out of this small contract. If the product needs them, define their placement and encode whether the cursor is in the null region. A row comparison involving a null can yield an unknown result. Merely writing NULLS LAST in the order does not automatically derive the correct continuation condition. ^[1]

Which tempting repairs change the order?#

Truncating the database column to milliseconds can be a valid design, but it creates a different ordering contract. Apply the same expression to ordering, cursor extraction and continuation, and assess the expression's index requirements. Rounding only the token gives the application a boundary that does not belong to the database's actual order. ^[1][4]

Another tempting repair is an overlap window plus client deduplication. That may be appropriate in a designed synchronization protocol with a bounded lag and durable deduplication, but it is not proof that an ordinary feed cursor is correct. It can hide repetitions while leaving omissions undetected. Establish the exact boundary first, then design any separate sync behavior.

Similarly, adding a maximum timestamp to a token does not freeze transaction visibility. A write can commit after the walk begins while carrying a sort key within the nominal bound. If a complete export matters, define its consistency model separately. This article's fixture proves precision and ordering properties on a fixed dataset; it does not establish a change-data-capture protocol.

How do you verify the entire walk?#

Start with the complete ordered fixture as the reference sequence. Walk the actual HTTP endpoint until it reports no continuation. Compare collected IDs in order, assert that none repeat and assert that none are missing. Exercise page boundaries inside a millisecond, exact timestamp ties, both supported directions and each allowed filter. Keep the dataset fixed for these contract tests.

Walk the fixture at several page sizes and compare the collected IDs with one fully ordered baseline query.

Capture the decoded token and bound database parameters in a test harness, with sensitive fields omitted. Assert equality of the full timestamp string before and after token transport. Then change the serializer to a Date round-trip as a deliberate negative control. The fixture should fail. A test that stays green under that change is not observing the precision contract.

Add a second set of tests for mutations between requests. Delete a previously returned row, insert a new earlier activity and change a sortable field if the real schema permits it. Specify which results the live feed promises. A stable ordering on existing rows does not guarantee a fixed snapshot across independent requests; the test's expected result must reflect the endpoint's stated behavior.

Finally, review the response-building step. If the query fetches an extra row to detect another page, construct the next cursor from the last row actually delivered, not the extra row. Keep the cursor's filter and direction tied to that response. A perfectly precise timestamp still skips data when the application advances past a record it never sent.

The repair is complete when the wire token, query predicate and displayed continuation agree about the last delivered row. Performance tuning can then work on a correct query. Until that boundary is faithful, a faster index only makes the incorrect walk finish sooner.

Inspect the cursor at the boundary#

In the fixture, compare the timestamp produced by the database projection with the decoded next token before issuing another request. Preserve it as text throughout that path. A log that prints only the human-facing date can hide the exact conversion you need to diagnose. Use the cursor field in the test assertion, even if it is omitted from the public item representation.

Keep the test IDs deliberately out of timestamp order. If both orders happen to agree, a coarser comparison can accidentally produce the expected sequence and leave the defect invisible. Add exact timestamp ties as another fixture: those establish that the ID tie-breaker actually participates when it should. The microsecond and tie fixtures test different parts of the tuple contract.

There is also a boundary between a database test and an API test. A SQL-only test never exercises the driver parser, JSON serializer or token decoder. An HTTP-only test with timestamps already rounded in its seed data never exercises sub-millisecond behavior. Use a database-created full-precision fixture and traverse the same response-building path clients use. Preserve that distinction in the release evidence when only one layer was tested.

A precision contract also needs an input boundary. Reject unsupported timestamp forms before querying, keep the permitted finite date range explicit and bind IDs using their declared database type. An opaque token can still be malformed or belong to another ordering version. A clear invalid-cursor response is more useful than silently restarting the walk.

Frequently asked questions#

Is adding an ID to ORDER BY enough?

Only if the continuation predicate includes the same ID and the timestamp survives unchanged. A unique tie-breaker resolves equal timestamp values. It cannot fix a timestamp rounded to a different value before the next query reaches the database. ^[1][3]

Can the displayed date remain a JavaScript Date?

Yes, if display serialization and cursor serialization are separate. The page can show a human-friendly date while the opaque cursor carries an exact timestamp string from the database. Never reconstruct the continuation timestamp from the displayed field. ^[5]

Should I use timestamp with time zone?

For an activity instant shared across time zones, a time-zone-aware timestamp is usually the appropriate model. Precision still needs its own contract: choosing timestamptz does not prevent a client library from converting the returned value to a millisecond JavaScript Date. ^[2][5]

Does a correct cursor create a consistent export snapshot?

No. A faithful cursor preserves a position in the selected order. It does not freeze changes made between requests. Define whether the endpoint is a live feed or an export with a fixed dataset, and choose the corresponding snapshot or change-capture design.