Pagination strategies

Offset pagination needs an order

Offset pagination names a starting position and a page size. In SQL, that is commonly OFFSET plus LIMIT:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

This asks for at most 20 rows after skipping 40 rows from the ordered result. PostgreSQL documents that SQL does not promise a row order unless the query uses ORDER BY. It also recommends an order that constrains rows uniquely when LIMIT is present.

The id column matters when two posts have the same created_at. Assuming id is unique, the pair (created_at, id) gives every row one position.

Wrong "The database returns insertion order unless I ask for another order."

Right Without ORDER BY, the database may return whichever order its chosen plan produces. Pagination needs an explicit, unique order.

Offset is convenient when the product exposes numbered pages or lets a user jump directly to page 12. The requested page maps to an arithmetic offset:

const offset = (pageNumber - 1) * pageSize;

The calculation is easy. Its performance and behavior during writes are covered in the later notes.

Cursor pagination continues from a row

Cursor pagination uses the last row from the current page as the next boundary. This module uses keyset pagination for that query pattern. Elasticsearch exposes the same idea as search_after, which accepts the previous hit's sort values.

For the ordering above, a next-page query can use both values from the last post:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The less-than comparison matches the descending order. Rows after the cursor have a smaller timestamp, or the same timestamp and a smaller ID. This example assumes both cursor columns are non-null.

Wrong "A cursor is an offset encoded as a string."

Right A keyset cursor represents a position in the sort keys. Encoding offset 40 does not change the query's offset behavior.

Offset answers "start at position 40." Keyset answers "continue after this ordered row." That difference drives the deeper performance and consistency tradeoffs.

Why deep offset pagination gets slower

PostgreSQL states the cost directly: rows skipped by OFFSET still have to be computed inside the server. A request for 20 rows at offset 100,000 does not let the server start by returning row 100,001 for free.

SELECT id, created_at, title
FROM posts
WHERE author_id = :author_id
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

The exact plan depends on the query, statistics, and available indexes. The safe claim is that a large offset can require substantial work before the page is returned. It is false to say that OFFSET always forces a table scan or always disables indexes.

Wrong "LIMIT 20 means the database only processes 20 rows."

Right LIMIT caps returned rows. PostgreSQL still computes the rows skipped by OFFSET.

Keyset pagination replaces the growing skip with a boundary condition:

SELECT id, created_at, title
FROM posts
WHERE author_id = :author_id
  AND (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

A B-tree index may deliver rows in the requested order without a separate sort. PostgreSQL calls ORDER BY with LIMIT an important case: when an index matches the order, the first n rows can be retrieved directly without scanning the remainder. A useful candidate for this query is:

CREATE INDEX posts_author_page_idx
  ON posts (author_id, created_at DESC, id DESC);

The planner chooses the plan. EXPLAIN reveals that choice; the index definition is not a promise that PostgreSQL will use it.

Filters and sort keys define the cursor

A cursor belongs to one result definition. If page 1 filters to published posts ordered newest-first, page 2 must repeat that filter and order:

SELECT id, created_at, title
FROM posts
WHERE author_id = :author_id
  AND state = 'published'
  AND (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 21;

The API can return 20 items and use the extra row only to decide whether another page exists:

const hasMore = rows.length > 20;
const items = rows.slice(0, 20);

Changing state, author_id, or the sort definition changes the ordered result set. A cursor from the old query no longer describes a boundary in the requested result.

The cursor must identify the last row's position in this ordering. A timestamp alone is insufficient when timestamps can tie, so this cursor also carries the unique id tie-breaker. The row comparison shown here works directly because both sort columns use DESC; mixed sort directions need a boundary predicate that handles each direction explicitly.

Exact totals are separate work

Page data and the exact total answer different questions. LIMIT 20 can stop a matching ordered scan after a small page. An exact COUNT(*) must compute the number of matching input rows.

SELECT COUNT(*)
FROM posts
WHERE author_id = :author_id
  AND state = 'published';

PostgreSQL documents that counting an entire table requires effort proportional to the table size because it scans the table or an index containing all rows. A filtered count has its own execution plan and must use the same filters if it is meant to describe the paginated result.

Wrong "The database already knows the exact total because it returned one page."

Right Treat an exact total as a separate aggregate with separate cost. If the interface only needs a next button, fetching one extra row can answer hasMore without claiming an exact total.

A total order does not freeze the data

Two guarantees are easy to conflate:

• ORDER BY created_at DESC, id DESC defines a unique position for each row when id is unique.
• A database snapshot defines which row versions a query can see.

The first prevents ambiguous ties. It does not make separate page requests observe one unchanged result set.

Consider rows ordered A, B, C, D, with page size two. Page 1 returns A, B. A writer inserts X before A, so the current order becomes X, A, B, C, D. Offset page 2 skips two rows and returns B, C. The client sees B twice.

A keyset request anchored after B instead asks for rows below B:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (:b_created_at, :b_id)
ORDER BY created_at DESC, id DESC
LIMIT 2;

The newly inserted X is above the boundary, so it does not shift the next page. This gives forward traversal relative to the cursor. It is not snapshot isolation. A deletion can remove an unseen row, and an update that moves a row across the boundary can change whether the traversal sees it.

Wrong "A unique tie-breaker makes cursor pagination consistent under every concurrent write."

Right The tie-breaker makes the order total. A stable snapshot is a separate requirement.

PostgreSQL 18 uses a fresh statement snapshot for each SELECT at Read Committed isolation. Two successive statements can therefore see different committed data. Repeatable Read makes successive statements in one transaction see the same transaction snapshot. Elasticsearch makes the same distinction in its search API: search_after continues from sort values, while a point in time preserves index state across searches.

Cursor scope is part of the API contract

Elasticsearch requires search_after requests to keep the same query and sort values. A backend API should enforce the equivalent rule for its own cursor. One approach is to encode a version, all boundary values, and a fingerprint of the normalized filter and sort definition:

type PostCursor = {
  version: 1;
  createdAt: string;
  id: string;
  queryFingerprint: string;
};

The server compares the fingerprint before constructing SQL:

if (cursor.queryFingerprint !== fingerprint({ authorId, state, sort })) {
  throw new InvalidCursorError();
}

This check rejects a published, newest-first cursor when the request asks for a draft or oldest-first feed. Encoding is a transport choice, not an integrity check. If clients must not alter cursor fields, the server must authenticate the token or store the cursor state server-side. The pagination semantics still require the complete boundary and an unchanged query definition.

Nullable sort keys need an explicit policy. PostgreSQL's ordering includes NULLS FIRST or NULLS LAST, while ordinary row comparisons can evaluate to unknown when the decisive pair contains null. The SQL examples in this module assume non-null keys. A cursor design that accepts null needs a boundary predicate matching the declared null order, including transitions into and out of the null group.

Count and page consistency

An exact count can be both expensive and short-lived. PostgreSQL's documented whole-table COUNT(*) work is proportional to table size. A filtered count uses a separate plan over its matching rows.

SELECT COUNT(*)
FROM tickets
WHERE tenant_id = :tenant_id
  AND status = 'open';

At Read Committed, a count statement and a later page statement may see different commits. If the product contract requires the count and every page to describe one fixed result, they must share snapshot semantics. PostgreSQL Repeatable Read can do that for statements in one transaction; Elasticsearch provides a point in time for searches. The transaction boundary controls the PostgreSQL snapshot's use. An Elasticsearch point in time uses keep_alive, so that API must also handle an expired view.

If the UI needs only navigation, avoid promising an exact total:

return {
  items: rows.slice(0, pageSize),
  hasMore: rows.length > pageSize,
  nextCursor: rows.length > pageSize ? encodeBoundary(rows[pageSize - 1]) : null,
};

Numbered random access, exact totals, forward keyset traversal, and a fixed snapshot are separate product capabilities. Choosing one does not supply the others.