Cursor pagination

The activity feed filters events to one workspace. A reader gets three events, then asks for the next three. An offset implementation can say “skip three thousand matching events and return three.” Although the response is small, the database still computes the skipped rows. Later pages can therefore require substantially more work than early ones.

SELECT occurred_at, event_id, message
FROM events
WHERE workspace_id = :workspace
ORDER BY occurred_at, event_id
LIMIT 3 OFFSET 3000;

An index can put those events in order, but a large offset still describes how many preceding rows to pass over. For sequential browsing, the previous response already gives us a better starting point: the exact position of its last row. The next request can ask for rows after that position instead of counting from the beginning.

The page limit also protects the response path. Three short event messages and three very large attachments are different payloads, even with the same row count. This feed returns bounded event fields; large associated objects can be fetched separately. A cursor identifies where to read, while row and byte limits control how much the API returns.

That bookmark is the central idea of cursor, or keyset, pagination. It is useful for continuing through a large ordered result. It does not naturally jump straight to an arbitrary numbered page because the client may not have that page’s boundary. Keep offset pagination when small lists or numbered navigation make its cost acceptable. Choose from the product’s navigation pattern rather than treating a cursor as mandatory for every list.

This example orders each workspace’s events by occurred_at, then event_id, ascending. IDs are unique within a workspace, both fields are non-null, and their values stay fixed. If several events share a timestamp, the ID still tells us exactly which one comes next. A timestamp alone would leave a page ending at that time ambiguous.

CREATE INDEX events_workspace_time
ON events (workspace_id, occurred_at, event_id);

SELECT occurred_at, event_id, message
FROM events
WHERE workspace_id = :workspace
ORDER BY occurred_at, event_id
LIMIT 3;

The illustrated index starts with workspace_id, followed by the two ordering fields. Filtering to a workspace selects its relevant index range. Within that range, time and ID provide the feed order. The database can locate a boundary and read forward through it rather than sort the entire workspace for every request, when the planner chooses this suitable path.

Keep all three choices aligned. Changing the feed to newest first changes the order and continuation comparison; the next page must continue in that direction. Nullable fields or mixed directions need a more explicit rule. For this first mechanism, use the stated ascending, non-null order. The important property is a stable, unique position within this query, not that every feed must use these particular field names.

The first page returns e101 at 12:00:01, then e102 and e103 at 12:00:02. Its final position is therefore 12:00:02 together with e103. The next event, e104, also happened at 12:00:02. A query asking only for a later timestamp would skip it.

Ordered events:
12:00:01  e101
12:00:02  e102
12:00:02  e103  <- last row on page 1
12:00:02  e104  <- first row on page 2
12:00:03  e105
12:00:04  e106

SELECT occurred_at, event_id, message
FROM events
WHERE workspace_id = :workspace
  AND (occurred_at, event_id) > (:last_time, :last_id)
ORDER BY occurred_at, event_id
LIMIT 3;

The continuation uses the complete pair. In the shown ascending PostgreSQL query, a pair is greater when its time is later, or its time is equal and its ID is greater. That includes e104, followed by e105 and e106, without returning e103 again. The workspace filter and the ordering stay the same as on page one.

With a suitable index, this boundary gives the database a place to seek. It avoids repeatedly traversing the entire preceding prefix. It does not promise that exactly three rows are inspected: additional filters can discard candidates, and the planner and data distribution matter. The API returns the last row actually delivered as the next bookmark. It also needs a documented end-of-results signal; for this feed, no next cursor means there were no later matching rows at that read.

If the request fails before the client receives a page, it can retry its previous bookmark. No server-side read position advanced merely because a query ran. The live data may have changed, so the retry is another read after that position rather than a guarantee of identical page contents. The client adopts the next bookmark only from a successful response.

The API sends an opaque token alongside the returned events. The client stores it and sends it back unchanged when requesting the next page. The server validates that token, recovers the last time and ID, and issues another independent query. In this design, there is no open database connection waiting for the reader to scroll.

{
  "version": 1,
  "workspace_id": "W9",
  "filters": { "kind": "all" },
  "sort": "occurred_at,event_id:asc",
  "page_size": 3,
  "last": ["2026-10-10T12:00:02Z", "e103"],
  "expires_at": "2026-10-10T13:00:00Z"
}

The illustrated token describes the workspace, filters, sort order, page size, final row, format version, and expiry. These are internal fields, not a format clients should edit or depend on. The server signs or authenticates the payload so altered contents can be detected. Encoding is not tamper protection, and a signature does not conceal the fields. Sensitive token contents need encryption or a server-held record referenced by a random handle.

Validate the supported format, integrity, expiry, and query match. Apply a bounded page-size policy, and authorize the caller’s workspace access on every request. Possessing a valid bookmark grants no extra access. An expired or mismatched cursor should produce a clear restart response. A SQL database cursor that retains session or result state is another design with a lifetime to manage; it is not required for this stateless API bookmark.

Each page in this feed reads the data available to that query. If a new event is inserted after the bookmark, a later page may include it. If an old event arrives late with a position before the bookmark, forward continuation will not go back to find it. The reader can refresh to start another pass through the live feed.

Deletion also differs from an offset. Deleting an earlier row does not move our stored time-and-ID boundary. Deleting an unseen row means it will not appear. If ordering fields can change, however, an event may move across the boundary: a previously returned event moved forward may repeat, while an unseen event moved behind it may be omitted. Keeping the ordering fields immutable avoids that particular movement problem.

A reproducible export makes a stronger promise than a live feed. It must read one defined dataset across all pages, using a snapshot, retained version, or stored export result with a managed lifetime. A cursor alone cannot supply that property. Decide which experience the product needs before describing the result as complete or fixed.

For the workspace feed, the complete explanation is that we filter by workspace, order by immutable time and a unique ID, use an aligned index, and return a bounded page with a token for its final position. The next query applies the same scope and resumes after that pair. Results are live, and each request is authorized independently.

That is enough to establish the mechanism before discussing the system’s larger bottleneck. If asked why timestamps alone are insufficient, use e103 and e104. If asked why later pages stay efficient, show the indexed boundary rather than claiming that a small response makes every query cheap. If asked about an export, change the read contract explicitly instead of implying that the existing bookmark already freezes the feed.

The same pattern can use other stable ordering fields. In telemetry, a bookmark may combine measurement time with source and observation IDs. Preserve a unique order for that query and state what changing data can do. Database work, network time, and the cost of a stronger snapshot promise remain separate decisions; they become relevant when the product’s scale or consistency requirement makes them matter.