Cursor pagination

A workspace activity feed shows three events at a time, oldest first. The client asks for the next page whenever the reader continues. One approach numbers the pages and skips the rows returned earlier.

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

The database still has to work through the skipped rows before returning three more. An index can supply the ordering, but a large offset still asks the database to skip a growing prefix. For sequential browsing, we already know where the previous page ended. We can use that position instead.

Offset pagination can be reasonable for small lists or when numbered-page navigation matters. Cursor pagination is useful when clients mostly move forward through a large ordered result.

For this feed, filter by workspace and sort by event time, then event ID. Event IDs are unique within a workspace, and both ordering fields remain fixed. Two events with the same timestamp still have a definite order.

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 workspace selects a part of the index; the remaining fields order that workspace’s events. The database can locate the beginning of the range and read forward. The ID breaks timestamp ties, so a page cannot end at an ambiguous position.

This example uses ascending, non-null fields. A descending feed needs the corresponding reversed comparison and ordering. Mixed sort directions or nullable keys need an explicitly defined continuation rule.

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

Page 1 ends at 12:00:02 and e103. Filtering only on a later timestamp would lose e104. Include both values so the next query starts after the exact row, including any remaining events with the same timestamp.

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;

With a suitable index, the database can seek past that boundary and continue. It does not need to revisit all preceding pages. Extra filters can still cause it to inspect more rows than it returns; check the execution plan and measure later pages as well as the first one.

In a sharded system, apply the same ordering and boundary to each relevant shard, then merge the ordered results into one limited page. A single boundary works when those fields define a unique order across all participating shards; more complex routing may need additional continuation state.

The API returns an opaque cursor alongside the page. The client sends it back unchanged. The server decodes and validates it, restores the last ordering key, and runs another query. In this design, no database connection or server-side pointer stays open between requests.

{
  "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"
}

Sign the token so changes can be detected, validate its format and expiry, and bind it to the original filters, sort order, and allowed page size. Encoding alone does not stop tampering, and a signature does not hide the contents. Encrypt the token or use server-side state if its contents must be private.

Authorize the caller on every request. A valid cursor does not grant access to another workspace. Reject an altered, expired, or mismatched cursor instead of silently continuing a different query.

A database cursor that keeps a transaction or result set open is a different implementation. It can be useful for controlled exports, but it requires managing server resources and lifetime. The API bookmark here is stateless continuation.

Each page in this example reads the data available when that request runs. A newly inserted event after the bookmark may appear on a later page. A late event inserted before the bookmark is missed by that continuation; the reader must refresh to find it.

Deleting earlier events does not shift a key-based boundary the way it shifts an offset. Changing a row’s ordering fields can still move it across the boundary, causing a repeat or omission. Prefer immutable ordering fields when that matches the product.

If an export must reproduce exactly one dataset across every page, choose a snapshot or versioned-read contract and define its lifetime. Adding a cursor alone does not provide that guarantee.

For a large sequential result, say which fields filter and order it, how the index supports that read, and that the response returns a limited page with a cursor. Clarify whether results are live or must remain fixed. Token structure and the full continuation query belong in this database prep material.

In the telemetry design, the bookmark uses measurement time plus source and observation IDs. The main design discussion stays on locating the relevant measurements and combining shard results.

• PostgreSQL: LIMIT and OFFSET
• PostgreSQL: multicolumn indexes
• PostgreSQL: row comparisons