TL;DR: Keyset pagination gives a client a position in an ordering; it does not automatically preserve the result set that existed when the first page loaded. A mutable feed needs an explicit contract for inserted, deleted, and reordered items. Choose a stable total order, bind the cursor to the query, and decide whether users are browsing a live collection or a frozen snapshot before promising that every item will appear exactly once.
The following page-turn transcript follows a fictional product called Fieldnotes. Its users browse reports submitted by several offices. Each report has an immutable identifier, a creation time, an editable priority, and a visibility status. The engineering team wants to replace numbered pages with cursors because users keep seeing reports twice. That change is sensible, but it cannot answer every question the interface currently leaves open.
The database examples use PostgreSQL 16 semantics, pinned here to avoid relying on an unspecified current release. Its documentation explains why LIMIT needs a predictable ordering, how row comparisons work, and what a transaction can expect under different isolation levels. The invented records and product contracts below are original worked examples.
Page one: A through E
At the first request, Fieldnotes has ten eligible reports in descending creation order. For clarity, call them A through J. The client requests five and receives A, B, C, D, and E. It now asks for the next page. If nothing changes, almost any reasonable implementation can return F through J. That quiet case explains why pagination defects often survive ordinary demonstrations.
The useful question is what “next” means after a mutation. Does it mean skip the first five rows in the current collection? Continue after the last item previously delivered? Return the next five members of the original result set? Those are three different contracts, even though the first page cannot reveal which one the server implements.
The product designer initially says users simply want to continue reading. A support analyst adds that some users treat the feed as a work queue and expect to review every eligible report. A compliance export, meanwhile, needs a reproducible membership set. One endpoint might support several modes, but it cannot satisfy all three expectations by returning an opaque string alone.
Fieldnotes separates ordinary browsing from review sessions and exports. The live feed offers continuity through a stable creation order. A review session tracks explicit work items. An export captures a reproducible dataset. The cursor design follows these promises instead of attempting to create them retroactively.
A new report arrives before the second request
Between requests, a new report X appears ahead of A. With offset pagination, skipping five rows in the new ordering skips X, A, B, C, and D. The next page begins with E, which the user has already seen. The database has correctly executed the request. The request itself used a position that no longer described the same boundary.
A keyset cursor can instead identify E's ordering key. For descending creation order, the next query asks for reports whose keys come after E in that order. X lies before the boundary, so it does not displace E into the second page. The user receives F through J, assuming those rows still qualify and their ordering keys remain unchanged.
This behavior solves a particular continuity problem. It does not show X during the current downward traversal. Fieldnotes therefore displays a modest “new reports available” control at the top rather than injecting X into the middle of the reader's current position. The user can refresh deliberately when ready.
The difference matters for product language. “Continue from where you left off” is consistent with this behavior. “You have seen every report” would require additional evidence about arrivals during the session. The latter claim cannot be inferred from reaching an empty page after E.
D disappears before page two
Now remove D after the first page. An offset query that skips five current rows passes A, B, C, E, and F. It begins with G, so F is missed. Again, the SQL engine has obeyed the query. The client carried an ordinal count across two different collections.
A cursor after E avoids this particular skip. The second query can return F onward even though D no longer exists. Crucially, the cursor must contain or resolve the ordering values needed to describe the boundary. Requiring the server to look up E and failing if E was deleted makes an otherwise useful continuation depend on the continued existence of one row.
Fieldnotes encodes the relevant boundary values in the cursor together with query context. The representation is opaque to clients because its schema is an implementation detail. Opacity does not make the cursor authoritative: the server still applies current access rules and validates every field it decodes.
Deletion also affects displayed totals. If the header says ten reports while the current collection contains nine, the product must decide whether that count describes the initial view or the live database. Recomputing it on every page can produce a moving denominator. Fieldnotes avoids presenting a completion percentage for ordinary browsing because it has not established a fixed membership set.
Two reports have the same timestamp
The team next orders only by creation time. Two reports, E and F, share the same timestamp at the precision stored in the database. A cursor containing only that time cannot distinguish “after E but before F.” A strict inequality may skip F; a non-strict inequality may return E again.
The remedy is a total ordering, such as creation time followed by a unique immutable identifier. The tie-breaker must appear in both the ORDER BY expression and the continuation predicate. A cursor that remembers two fields while the query orders by one has not actually fixed the ambiguity.
Suppose E and F both have time 12:00, with identifiers 105 and 104, and the feed sorts both fields descending. After E, F is eligible because its time is equal and its identifier is lower. A later report with an earlier time remains eligible regardless of whether its identifier is numerically larger. Comparing only identifiers would accidentally substitute a different ordering.
PostgreSQL documents lexicographic row comparisons for compatible operators. That can express a composite boundary compactly, but the developer must verify sort direction, null handling, and data types. Mixed ascending and descending components often require a carefully expanded predicate. Copying a two-column example without matching the actual order is an easy way to introduce a silent gap.
The score changes after the reader passes it
Fieldnotes also offers a “highest priority” sort. Priority is editable. After page one, report H receives a higher score and moves above E. A cursor after E in the current priority order will never show H during this traversal, even though H existed before the session began and was not previously displayed.
The opposite movement creates duplicates. A report already shown on page one can fall below the stored boundary after its priority is reduced. The next page may return it again. A stable tie-breaker resolves equal scores, but it cannot prevent changes in the primary sort key from moving records across the cursor.
This is the limit the team must explain honestly. Keyset pagination works most predictably when the ordering key is stable. For a mutable ranking, the product needs either a frozen ranking snapshot, explicit tolerance for movement, or a session-level record of displayed membership. Each option has different storage and freshness costs.
Fieldnotes keeps the live priority view visibly live and avoids an exactly-once promise. Its review workflow instead captures the selected report identifiers at the start of a session. Users can see new arrivals separately. The resulting review queue is an application object with a lifecycle, not merely a cursor with a more elaborate encoding.
A filter changes without changing the cursor
The client reaches page three of reports from the eastern office, then switches the filter to the western office while retaining the old cursor. The ordering values may still be syntactically valid. Semantically, however, they describe a boundary in a different query.
Fieldnotes binds each cursor to the normalized filter, sort mode, tenant scope, and relevant query version. A request whose parameters do not match receives a recoverable invalid-cursor response and starts a fresh traversal. It does not silently continue from an arbitrary position that happens to share the same timestamp range.
Normalization deserves deliberate treatment. Two equivalent filter representations should not accidentally create different identities, while two materially different filters must not collapse into one. A list of selected offices may be sorted before hashing; a date interval must retain its inclusive or exclusive boundary semantics. The cursor contract should name those decisions rather than leaving them to whichever serializer is convenient.
The server must also recalculate authorization on every request. A cursor issued when a user could view a report does not grant permanent access to that report. Query binding preserves meaning; it is not a substitute for access control. If permissions narrow during a session, shorter pages or missing items may be the correct result under the current policy.
An empty page is not proof of a complete scan
Imagine that the cursor follows creation time while the eligibility filter is “unreviewed.” Another employee marks several reports reviewed as the user scrolls. Some future pages become shorter. Meanwhile, an old report is returned to unreviewed status after the cursor has already passed its creation time.
An empty page now means there are no currently eligible rows after the boundary. It does not mean the user has reviewed every report that was ever eligible during the session. The collection changes in both membership and order-relative position even though creation time itself remains immutable.
Fieldnotes makes work completion explicit. A review session records the items assigned to that session and their dispositions. Newly eligible reports enter a separate queue or a later session according to business rules. This avoids treating visual traversal as evidence of operational completion.
For a simple news feed, such machinery would be unnecessary. A reader can tolerate missing an old item that becomes visible later and can refresh when desired. The lesson is to align the guarantee with the actual task. An interface used to sign off a backlog needs a stronger model than one used to browse interesting updates.
The database snapshot lasts longer than one query
PostgreSQL's Read Committed isolation gives a statement a view of data appropriate to that statement; later statements can observe later committed changes. Wrapping each page request in its own transaction therefore does not freeze a multi-request browsing session. The word “transaction” alone does not specify the lifetime of the snapshot.
Repeatable Read can provide a stable view within a transaction, but holding an ordinary transaction open while a human browses for an hour has operational implications. Connection usage, retained row versions, failures, and session expiration all become part of the design. A web cursor should not casually imply that an indefinitely lived database snapshot exists behind it.
For a reproducible export, Fieldnotes runs a bounded server-side job that reads under an appropriate consistency policy and writes an output artifact. The export has an identifier, a creation time, a query description, and an explicit completion state. Downloading chunks from that artifact is different from repeatedly querying the live reports table.
A materialized list of identifiers can also freeze membership while allowing selected fields to remain live. That hybrid requires a clear contract: the list may represent reports selected yesterday, but their titles or statuses may be today's values. “Snapshot” should specify whether it freezes membership, ordering, values, or all three.
The upper-bound trick has a narrow promise
A common design stores the maximum creation key from page one and restricts later requests to keys at or below it. This prevents newly created reports with later keys from entering the traversal. Combined with a stable lower cursor boundary, it can provide a useful bounded browsing window.
It does not freeze deletions, edits to filter fields, or mutable ranking scores. It also depends on what creation time means. If imports can backdate creation timestamps, a newly inserted record may fall inside the captured window. An ingestion sequence can provide a different boundary, but it must be defined and stored consistently.
Fieldnotes distinguishes the report's event time from any ingestion ordering key. An imported report may describe an old event while arriving today. A normal PostgreSQL sequence is allocation-ordered, not commit-ordered: a transaction can reserve a low value, remain uncommitted when the cutoff is captured, and commit afterward. A sequence cutoff alone therefore cannot establish the promise “records present when the job began.” For that export, Fieldnotes uses a bounded database snapshot or materializes membership from one; an alternative would require an explicitly commit-aware ingestion protocol with its own proven guarantees. The live chronological feed can still sort by event time when that serves readers.
That distinction prevents a misleading claim that a timestamp filter creates a snapshot. It creates a condition. The team should state which kinds of mutation that condition excludes and which remain possible. A small written matrix of insertions, deletions, field changes, and imports is often enough to expose an overbroad guarantee before code is written.
The cursor is a contract with an expiration date
An opaque cursor may contain a version, boundary keys, query identity, and snapshot or session reference. Fieldnotes validates those fields and caps their size. If it signs the payload to detect modification, the signature protects the payload's integrity; it does not establish that its referenced data remains available forever.
Cursors expire when the product can no longer honor their interpretation. A snapshot artifact may have a retention period. A sort implementation may change. A field's collation or normalization rules may be revised. The server should distinguish an expired cursor from malformed input and give the client a clear restart path.
The interface preserves the user's place as far as possible without pretending that an invalid continuation succeeded. It can reopen around a known report or offer a refreshed result set with an explanation. Returning page one silently risks duplicate work because the user believes they are continuing from page seven.
Backward navigation needs its own semantics. Reversing a forward cursor is not always equivalent to reconstructing the page the user saw earlier. If exact visual backtracking matters, the client can retain loaded pages or the server can retain a session snapshot. If freshness matters more, the interface should make it clear that earlier results may have changed.
Performance follows the predicate, not the word cursor
A cursor endpoint can still be slow. The useful index must support the filter and ordering pattern the query actually uses. PostgreSQL's index ordering documentation explains when a matching B-tree order can avoid a separate sort. That is a mechanism to evaluate, not a blanket promise that every keyset query is constant time.
Fieldnotes inspects representative query plans for common and uncommon filters. A tenant with millions of reports may behave differently from a small office. A selective status condition can change the work needed to locate the next five eligible rows. The team measures rows examined and latency across realistic boundaries rather than benchmarking only the first page of a clean dataset.
Large offsets often require work to pass over skipped rows, which is one reason keyset continuation can help. But an expensive join, unindexed expression, or poorly aligned predicate can dominate the cost after the offset disappears. The performance review should preserve semantic correctness while improving the access path.
Page size is also a product and resource decision. A server cap prevents a client from turning a browsing endpoint into an uncontrolled export. A “has more” indicator can be obtained by requesting one extra eligible row, but it describes the moment of that query. The next request may still find that the collection has changed.
What the test transcript must include
Fieldnotes builds tests as sequences of requests and mutations. The first sequence inserts X before the boundary and verifies no repeated E. The second deletes D and verifies F is not skipped. The third creates equal timestamps and checks the identifier tie-breaker. These fixtures establish the narrow benefits of the chosen keyset contract.
Other tests deliberately demonstrate its limits. A mutable priority crosses the boundary; a report becomes eligible behind the cursor; access is revoked; an imported record carries an old event time. The expected outcomes are documented product choices. A test suite that insists on impossible exactly-once behavior without a snapshot would pressure developers into hidden state or misleading filtering.
Cursor tests cover altered query parameters, expired versions, missing referenced snapshots, and invalid encodings. The client is tested for a visible restart rather than silent repetition. Export tests verify a reproducible membership list and the exact meaning of field values included in the artifact.
The final review uses a human-readable transcript containing the initial collection, each mutation, each request, and each returned identifier. That is easier to reason about than a pile of opaque tokens. Reviewers can point to the precise moment a guarantee is supposed to hold and ask whether the data model can support it.
A retry can repeat delivery without repeating membership
A user requests the next page, the server returns it, and the network drops before the client records success. Retrying the same cursor is reasonable. The client may receive the same identifiers again, especially when the underlying collection is unchanged. That is not the same defect as E reappearing because an insertion shifted an offset.
Fieldnotes assigns each loaded item its stable report identity and merges a retried response accordingly. It does not use the response's array position as the rendered identity. Otherwise a harmless retry can duplicate cards or attach an old local selection state to a different report. The presentation layer must preserve the distinction between repeated transport and newly discovered membership.
Concurrent requests create another race. A slow response for an old filter can arrive after the user has switched offices. The client associates responses with a query generation and discards results that belong to an obsolete generation. Server-side cursor validation cannot prevent an already valid response from becoming irrelevant by the time it reaches the screen.
The loading control therefore allows one continuation request per active traversal, while a filter change starts a new traversal with its own identity. A failed request can be retried without advancing the stored cursor. The application advances only after it accepts the matching response and integrates its items.
These rules make a useful boundary for debugging. If the server returned each eligible identifier once for a stable sequence but the screen displays duplicates, the defect is in response handling. If the server's own transcript skips F, the query contract needs attention. Recording request generation, incoming cursor, outgoing cursor, and returned identifiers makes those two investigations separable.
The promise Fieldnotes ultimately makes
The live feed now says that it continues after the last delivered creation key and shows newer arrivals on refresh. It uses a unique immutable tie-breaker, validates query identity, and reapplies current visibility rules. It does not claim that an uninterrupted scroll proves exhaustive review of a changing collection.
The review feature maintains an explicit assigned set and records decisions against that set. The export feature produces a bounded artifact with documented snapshot semantics. All three can use cursors for efficient delivery, but each has a different definition of membership and completion.
This separation makes the product easier to explain. A user asking why a newly imported old report did not appear during yesterday's scroll receives an answer grounded in the feed's stated ordering and arrival behavior. An auditor asking which reports were included in an export receives a reproducible artifact rather than a story about how quickly someone clicked Next.
A cursor is valuable because it gives continuation a precise boundary. Its value increases when the surrounding contract is equally precise. Once the team names what may change between requests, pagination stops being a mysterious interface defect and becomes a set of explicit, testable choices about time, membership, and order.