Keyset pagination continues after the last ordering key returned to a client. Instead of skipping a number of rows from the current beginning, it compares each candidate with a concrete boundary. This prevents a new item at the top of a feed from shifting every later page by one position. It does not freeze the dataset or promise that every future request sees the same records.

When it breaks

A timestamp alone usually fails to define a unique boundary. Several rows can share the same creation time, even when timestamps have fine precision. A strict comparison skips the remaining ties; an inclusive comparison can return already displayed rows again. Add a unique tiebreaker and use the complete ordering key consistently in both the sort and the continuation predicate.

Mutable ordering keys create a different failure. A row already displayed can move behind the cursor and appear again. An unseen row can move ahead of the cursor and disappear from the remaining traversal. Deletions and changes to filter membership also affect what a later request can return. A stable comparison boundary cannot make changing data behave like an immutable snapshot.

Offset pagination has its own useful contract: numbered positions in the current query result. It supports page numbers conveniently, but an insertion before the next offset can repeat a row and a deletion can skip one. Neither approach supplies a cross-request snapshot merely because an API calls its response a page.

Check it quickly

Use one psql session connected to a disposable PostgreSQL database. Ensure article_cursor is unused and keep autocommit enabled. The recorded run used PostgreSQL 18.6. This exercise deliberately places two rows at each timestamp, saves the first returned page, inserts another row, and finally changes two ordering keys:

CREATE SCHEMA article_cursor;
SET search_path=article_cursor,public;
SET TIME ZONE 'UTC';
CREATE TABLE feed (id bigint PRIMARY KEY,published_at timestamptz NOT NULL);
INSERT INTO feed VALUES
 (1,'2026-01-01 10:00:00+00'),(2,'2026-01-01 10:00:00+00'),
 (3,'2026-01-01 11:00:00+00'),(4,'2026-01-01 11:00:00+00'),
 (5,'2026-01-01 12:00:00+00'),(6,'2026-01-01 12:00:00+00');
CREATE INDEX feed_order ON feed (published_at DESC,id DESC);
CREATE TEMP TABLE first_page AS
  SELECT id,published_at FROM feed ORDER BY published_at DESC,id DESC LIMIT 3;
SELECT array_agg(id ORDER BY published_at DESC,id DESC) FROM first_page;
SELECT published_at AS cursor_time,id AS cursor_id FROM first_page
ORDER BY published_at,id LIMIT 1 \gset

SELECT array_agg(id ORDER BY published_at DESC,id DESC)
FROM feed WHERE published_at<:'cursor_time'::timestamptz;

PREPARE after_cursor(timestamptz,bigint) AS
SELECT array_agg(id ORDER BY published_at DESC,id DESC) FROM (
  SELECT id,published_at FROM feed
  WHERE (published_at,id)<($1,$2)
  ORDER BY published_at DESC,id DESC LIMIT 3
) AS page;
INSERT INTO feed VALUES (7,'2026-01-01 13:00:00+00');
SELECT array_agg(id ORDER BY published_at DESC,id DESC) FROM (
  SELECT id,published_at FROM feed
  ORDER BY published_at DESC,id DESC LIMIT 3 OFFSET 3
) AS page;
EXECUTE after_cursor(:'cursor_time'::timestamptz,:cursor_id);

UPDATE feed SET published_at='2026-01-01 09:00:00+00' WHERE id=6;
UPDATE feed SET published_at='2026-01-01 14:00:00+00' WHERE id=3;
EXECUTE after_cursor(:'cursor_time'::timestamptz,:cursor_id);
DEALLOCATE after_cursor;
DROP TABLE first_page;
DROP SCHEMA article_cursor CASCADE;

The first array is {6,5,4}. The cursor therefore contains row four’s timestamp and identifier. It is extracted from the materialized first page, not from a second lookup against an independently changing feed. That distinction matters when requests overlap with writes.

The timestamp-only comparison returns {2,1} and incorrectly omits row three, which shares the boundary timestamp. After inserting row seven at the top, the offset query returns {4,3,2}: row four has appeared on both pages. Using the full tuple instead returns {3,2,1}, the correct continuation for the unchanged original ordering keys.

The final updates move previously displayed row six behind the boundary and unseen row three ahead of it. Reusing the same cursor now returns {2,1,6}. Row six repeats, and row three is absent from the remaining traversal. These exact arrays demonstrate both the benefit and the limitation; they do not justify claiming that cursor pagination never repeats or misses an item.

Carry the whole query contract

For descending timestamp and descending identifier order, the continuation predicate uses a strict tuple comparison with <. An ascending traversal needs the corresponding opposite direction. Mixed sort directions require a predicate that follows that actual order; copying one tuple comparison without checking directions is unsafe. The example excludes null timestamps, because null comparison behavior needs an explicit pagination policy.

A cursor should preserve the values exactly, including timestamp precision. Associate it with the relevant filter and sorting contract so a client cannot silently apply a boundary from one search to a different search. Encoding values into an opaque string helps API evolution, but encoding alone provides neither tamper protection nor authorization. Validate input and still enforce resource access on every page.

A matching index can support locating the boundary and reading nearby entries without discarding a deep offset. The tiny fixture does not establish that the optimizer chooses this index or measure a speedup. Examine representative plans, filtering selectivity, and page depth on realistic data before making a performance claim.

If the product requires a fixed export, specify that stronger guarantee separately: a bounded database snapshot or a materialized result set has retention and resource costs. A live feed can instead document that records may change between requests. Choose the contract deliberately so users know whether they are traversing current data or a preserved result.

Sources