Cursor vs Offset Pagination: Stable Ordering Without False Guarantees

Offset is simple, but its position can move

LIMIT 20 OFFSET 40 skips forty rows and returns up to twenty. PostgreSQL still processes skipped rows, so deeper offsets can become expensive. Always specify a unique order.

Our original feed example starts with identifiers 4, 3, 2, 1 in descending order. Page one returns 4 and 3. If 5 arrives before the next request, OFFSET 2 returns 3 and 2: a duplicate appears because the position moved. Deleting a row before the boundary can instead cause a skip.

• PostgreSQL 17: LIMIT and OFFSET

A timestamp needs a tie-breaker

Order an article feed by created_at descending, then unique id descending. PostgreSQL uses later sort expressions to resolve ties. Both fields in this example are non-null, and their values remain unchanged after publication. A cursor containing only the timestamp would lose the position within a group published at the same instant.

For articles 106, 105, and 104 sharing a timestamp, a page ending at 105 must continue with 104. Its cursor therefore contains both the exact timestamp and id 105, preserving timestamp precision.

• PostgreSQL 17: Sorting Rows

Continue strictly after the last returned row

Run this standalone fixture in one PostgreSQL 17 session. It creates forty-five articles, with 104, 105, and 106 sharing noon UTC. The prepared query compares fields from left to right. Both sort directions are descending, so continuation uses less-than. Mixed directions require a different predicate. The shown boundary at 105 returns 104 next.

For a page size of twenty, fetch twenty-one rows to detect another page. Return twenty and build the next cursor from the twentieth returned row, not the extra row. The first page uses the same ordering without the cursor predicate.

CREATE TEMP TABLE articles (
  id bigint PRIMARY KEY,
  created_at timestamptz NOT NULL,
  title text NOT NULL
);

INSERT INTO articles
SELECT 100 + g,
       TIMESTAMPTZ '2026-10-01 12:00:00+00'
         + (((g - 1) / 3) - 1) * INTERVAL '1 second',
       'Article ' || g
FROM generate_series(1, 45) AS g;

CREATE INDEX articles_order_idx
ON articles (created_at DESC, id DESC);

PREPARE next_articles(timestamptz, bigint) AS
SELECT id, created_at, title
FROM articles
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 21;

EXECUTE next_articles('2026-10-01 12:00:00+00', 105);

• PostgreSQL 17: Row and Array Comparisons

Choose for the navigation users need

The cursor example uses keyset pagination. An opaque token could also hide an offset, so the word cursor does not establish a database strategy. Decide from the navigation and workload, then measure its actual query plan.

A cursor is not a snapshot or permission

A new article inserted ahead of this keyset boundary does not shift the next page's starting value. But changing sort keys or filters can move rows across the boundary. At Read Committed, separate queries can observe newly committed data. A frozen export needs a separately designed snapshot or materialized result; a stable order alone is insufficient.

Bind the token to its sort definition and filters, version it, and validate its types. Encode it opaquely and protect integrity if clients must not alter it. Continue enforcing tenant access on every query. Base64 encoding is neither authorization nor tamper protection.

• PostgreSQL 17: Transaction Isolation

Test ties and mutations before shipping

Seed forty-five articles with repeated timestamps. Traverse with twenty-row pages and compare the concatenated identifiers with one fully ordered query. In our isolated PostgreSQL 17.9 exercise, pages contained 20, 20, and 5 distinct rows, with no missing baseline identifiers. This verifies the fixture, not every concurrent editing scenario.

• Insert a newer article between requests and compare offset with keyset.
• Delete an earlier row, then test a sort-key change separately.
• Reject altered filters, malformed tokens, and unauthorized tenant scopes.
• Compare deep-page plans with a matching composite index.

• RecallDeck: SQL window-function practice
• RecallDeck: HTTP and REST API interview preparation
• RecallDeck: PostgreSQL internals and optimization preparation

Quick answers

Frequently asked questions