Paginate PostgreSQL results with stable keyset cursors

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

Offset pagination asks the database to walk past an increasing number of rows. It also identifies a page by position in a changing result set. Inserts or deletes before that position can make users see duplicates or miss rows between requests.

Keyset pagination identifies the boundary by values from the last row returned. It can keep the work near the requested page when an appropriate index exists. It does not freeze the dataset, and it is only correct when the cursor reproduces the complete ordering contract.

Define a total order

For a PostgreSQL 18 lab, create a feed with a non-null timestamp and a unique ID. Timestamps alone are not a total order: several events can occur at the same instant.

CREATE TABLE cursor_event (
  id bigint PRIMARY KEY,
  occurred_at timestamptz NOT NULL,
  title text NOT NULL
);
INSERT INTO cursor_event VALUES
  (1, '2026-01-01 10:00:00+00', 'A'),
  (2, '2026-01-01 10:00:00+00', 'B'),
  (3, '2026-01-01 11:00:00+00', 'C'),
  (4, '2026-01-01 12:00:00+00', 'D');
CREATE INDEX cursor_event_page
ON cursor_event(occurred_at DESC, id DESC);

SELECT id, occurred_at, title
FROM cursor_event
ORDER BY occurred_at DESC, id DESC
LIMIT 2;

The next request uses the timestamp and ID of the last returned row. For the displayed fixture, the first page's boundary is the row with ID 3. The SQL below demonstrates that boundary explicitly; an application should bind these values as typed parameters.

SELECT id, occurred_at, title
FROM cursor_event
WHERE (occurred_at, id) <
      (timestamptz '2026-01-01 11:00:00+00', 3::bigint)
ORDER BY occurred_at DESC, id DESC
LIMIT 2;

The strict inequality excludes the boundary row. The ID resolves timestamp ties. Preserve full timestamp precision in the encoded cursor; truncating to milliseconds can change the boundary when the database stores finer precision.

Keep filters inside the cursor contract

A cursor from one tenant or filter should not silently be reusable for another. Encode or validate the relevant query context: tenant, ordering version, filter identity, and boundary values. Treat the cursor as untrusted input, validate its types, and enforce authorization independently. A signed cursor can detect tampering but does not replace row-level access checks.

If the application changes the sort from newest-first to oldest-first, invalidate old cursors or version their format. The same tuple with the wrong comparison direction produces incorrect pages even though the SQL remains valid.

Reference descending feed. The cursor stores the complete timestamp-and-ID boundary; it is not a snapshot of all matching rows.

Handle mixed directions explicitly

A simple tuple comparison is convenient when all ordering components use the same direction. For ORDER BY score DESC, id ASC, the next-page predicate has different comparison directions:

-- Assumes score is NOT NULL and id is unique.
SELECT id, score
FROM ranked_item
WHERE score < $1
   OR (score = $1 AND id > $2)
ORDER BY score DESC, id ASC
LIMIT $3;

This is an application query template, not a standalone executable fixture; ranked_item is the application's table and the parameters must be bound. An index on (score DESC, id ASC) matches the ordering, but inspect the actual plan for the disjunction. Correct semantics come before assuming one compact tuple expression can represent every mixed order.

Nulls require another explicit decision. Row comparisons involving nulls can evaluate to unknown rather than true or false. Declaring a sort column non-null is often the simplest contract. If null is meaningful, design a null bucket with explicit NULLS FIRST or NULLS LAST behavior and a matching predicate. A COALESCE sentinel is safe only when it cannot collide with real values and its ordering semantics are deliberate.

Explain what concurrent changes can do

Under ordinary read committed requests, each page can see a different snapshot. A new row inserted ahead of the cursor will not appear on later pages in a descending traversal. A row whose sort key moves across the boundary can be missed or repeated. Keyset pagination avoids positional drift, but it cannot make mutable ordering keys immutable.

For activity feeds, this may be the desired contract: refresh to see new events, continue to browse older ones. For a complete export, it may be unacceptable. Consider a bounded export with an initial high-water mark, or a controlled snapshot-based export workflow. A high-water mark limits newly added rows only if the chosen key and insertion semantics support that guarantee; it does not automatically freeze updates to existing rows.

Holding a repeatable-read transaction across many user requests can retain old versions and consume connections. It is usually a poor default for interactive browsing. A durable export job with bounded duration is easier to operate than an open transaction tied to a user's browsing session.

Test the boundary cases

Use multiple rows with equal timestamps, an empty page, a deleted boundary row, an insertion before the boundary, and an update that moves a row across it. Deleting the boundary row does not invalidate value-based continuation, because the predicate uses the saved values rather than requiring the row to still exist.

For reverse navigation, invert the boundary comparison and query order, then reverse the returned rows for presentation. Test the first and last pages explicitly. Fetching one extra row can indicate whether another page exists without a separate total count, but a total count under concurrent writes has its own snapshot semantics.

A good pagination API documents both order and consistency: which fields define position, whether those fields can change, and what users should expect when new data arrives. The cursor is a precise boundary, not a promise of a frozen world.

Sources and review scope

• Row comparisons
• LIMIT and OFFSET
• Transaction isolation
• Indexes and ordering