Offset vs Cursor Pagination When Your Data Keeps Changing

Offset pagination continues from a position in the current result set. Keyset pagination continues after a remembered ordering value. When rows are inserted or deleted between requests, positions can move even though the ordering rule has not changed. A keyset boundary avoids that particular shift, but it does not freeze the data.

An opaque cursor is a client-facing token, not a consistency guarantee. It can encode an offset, a keyset boundary, or a server-side snapshot identifier. Decide which behavior the API promises before choosing what to put in the token.

Start with a complete ordering rule

Consider a synthetic activity list ordered from oldest to newest by (created_at, id). The ID breaks timestamp ties. The first page contains two records:

(10, a), (10, b), (20, c), (30, d)
page 1: a, b

The timestamps are small invented integers to keep the example visible. IDs are unique, and neither field is null. Real code must define timestamp precision, tie-breaking, null handling, and collation consistently between the query and the cursor.

Without a unique ordering, asking for the next two rows is already underspecified before concurrent changes enter the picture. PostgreSQL 18's LIMIT and OFFSET documentation explicitly calls for a predictable unique order and notes that rows skipped by an offset still require server work. That is a reason to inspect query plans for large offsets, not evidence of a speedup measured here.

Watch a row move across the page number

After page 1, insert (5, x) at the beginning. The current order is now:

x, a, b, c, d

OFFSET 2 LIMIT 2 skips x and a, returning b and c. The client sees b twice. The query did exactly what the offset requested; the meaning of position two changed.

Try a separate scenario from the original list: delete a after page 1. The order becomes b, c, d. Skipping two rows returns only d, so the client never sees c in this traversal.

A keyset boundary retains the last returned tuple, (10, b), and asks for rows strictly after it. In both scenarios it returns c, d. The boundary survives deletion of the boundary row itself if the query compares the stored values rather than looking up that row again.

We executed these scenarios as a sorted-list model in Python 3.14.2:

The assertions passed. This is a deterministic model of selection rules, not a PostgreSQL execution, HTTP integration test, concurrent workload, or performance benchmark.

Make the query and cursor agree

For ascending order, the intended SQL shape is:

SELECT id, created_at, summary
FROM activity
WHERE tenant_id = :tenant_id
  AND (
    created_at > :last_created_at
    OR (created_at = :last_created_at AND id > :last_id)
  )
ORDER BY created_at ASC, id ASC
LIMIT :page_size;

The placeholders are explanatory; bind them through the actual driver's parameter API. Apply authorization independently on every request. A cursor identifying another tenant's row does not authorize reading that tenant's data.

Both ordering fields belong in the boundary. Using only created_at > 10 would skip another record sharing timestamp 10 after b. Using only the ID would be incorrect when the timestamp is the primary sort key. Descending order requires the corresponding reversed comparisons, not merely a reversed display of the returned page.

One proposed cursor payload is a version, the two boundary values, and an identifier for the filter and sort specification. Clients should treat it as opaque. Validate its structure and its relationship to the requested query; use integrity protection when modification could violate the intended protocol. Encoding as base64 only changes representation and does not create integrity or secrecy.

GraphQL's pagination guidance describes opaque cursors and a connection model with page information. It also allows different backend pagination strategies beneath that interface. Returning a field named endCursor therefore does not tell a client whether the implementation uses the keyset rule above.

A moving sort key can still repeat a record

Now change a from timestamp 10 to timestamp 40 after it has appeared on page 1. The next keyset page still returns c, d. A subsequent page after (30, d) returns a again.

Our executed model verified that result too. The ID stayed the same; its position in the ordering changed. This is why an activity feed ordered by immutable creation time has different traversal properties from a dashboard ordered by a mutable score or last-update timestamp.

Keyset traversal also does not recover a newly inserted row that sorts before the cursor. That can be acceptable for a feed whose next page means “continue toward older items,” but unacceptable for an export that claims to contain every row at a particular time. State what additions, deletions, and updates the traversal may miss or repeat.

Choose snapshot semantics separately

An audit export may need a consistent historical set. A browsing UI may prefer current data on every page. Those are different products even if they share a pagination envelope.

PostgreSQL's transaction-isolation documentation distinguishes statement snapshots at Read Committed from the stable snapshot used by Repeatable Read within a transaction. Separate API requests do not acquire one shared snapshot merely because their cursors match. Holding database state across a long user traversal has resource and lifecycle costs that need explicit design.

Other designs include materializing an export or returning a reference to a prepared result set. An upper timestamp bound can exclude later arrivals, but by itself does not freeze changes or deletions to earlier rows. Avoid calling it a snapshot unless the full data contract supports that claim.

Offset remains reasonable for a small, stable result set or an interface that genuinely needs arbitrary page jumps and accepts changing membership. Keyset is often a better fit for sequential traversal with suitable ordering fields. Neither choice removes the need to document consistency and expiration.

Define what a client should do with a malformed, expired, or incompatible cursor. Give it a stable error contract and a clear way to restart; our API-error guide explains how to make that response actionable. Test insertions, deletions, ties, changed filters, and moved sort keys around the page boundary before describing pagination as stable.

Documentation was checked October 28, 2026 against PostgreSQL 18 and the GraphQL pagination guide. Only the Python ordered-list fixture was executed.