Topic: PostgreSQL

Why does the next page repeat a row? OFFSET and keyset pagination

Trace five rows through OFFSET and keyset pagination, then connect compound cursors to B-tree indexes, NULL handling, and the limits of changing data.

The first page looks fine. The second repeats an article. Before adding client-side deduplication, check whether the query itself permits that result.

I would first decide what the list needs to do. Jumping to page ten and reading continuously do not necessarily call for the same pagination method. The examples below use PostgreSQL and five fictional rows, without claiming unmeasured speedups.

OFFSET skips current positions

Assume these timestamps fall on the same day. Both columns are NOT NULL, and id is unique:

Use created_at DESC, id DESC and two rows per page:

SELECT id, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 2 OFFSET 2;

This returns page two: 103 and 102. With page numbers starting at one, the offset is (page - 1) * pageSize. Page three skips four rows, not two.

Consider these independent changes after reading page one:

The insertion repeats 104. The deletion skips unread row 103. The database skips the first two positions it sees now; it does not keep a record of what the reader saw. Client deduplication can hide a repeat, but cannot recover a missing row.

The PostgreSQL LIMIT/OFFSET documentation also notes that skipped rows still need processing. Deep pages in large lists can therefore cost more as well.

Continue from the last row’s values

Keyset pagination stores a boundary made from sort values. Page one ends at 104, so its cursor needs both the timestamp and id. Here the column is timestamptz and the example date uses UTC:

SELECT id, created_at
FROM posts
WHERE (created_at, id) <
      (TIMESTAMPTZ '2026-09-09 12:00:00+00', 104)
ORDER BY created_at DESC, id DESC
LIMIT 2;

The result is 103 and 102. Inserting 106 ahead of the boundary does not shift this condition. A real API should bind query parameters rather than concatenate user input. Preserve the timestamp precision returned by the database in the cursor, not just the minutes shown on screen.

The tuple comparison checks time first, then id when times match. A timestamp-only condition can exclude unread rows sharing the boundary timestamp. Using <= includes the boundary itself. Row constructor comparison defines the comparison order.

Because the cursor contains values, the boundary row can disappear without making those values unusable. This need not involve an open database cursor.

Fetch one extra row, but keep the right boundary

For a twenty-row page, request twenty-one. If that extra row exists, return the first twenty and mark that another page is available. Build the next cursor from row twenty. Using row twenty-one would skip an item the API has not returned when the next query applies its strict comparison.

Reset pagination when the category or sort changes. A timestamp cursor does not describe a position in price order. No next page means this query found no more visible matching rows, not that future rows cannot appear.

What does a compound B-tree index help with?

For that query, consider:

CREATE INDEX posts_created_at_id_idx
ON posts (created_at, id);

This index orders entries by time, then id. When the boundary matches the index, the database can locate the boundary region and read in the required direction instead of counting a large prefix.

B-tree supports backward scans. With both columns NOT NULL, an ordinary ascending index can serve the two-column descending order too. Mixed ASC and DESC ordering needs separate consideration. See indexes and ordering and multicolumn indexes.

An index does not guarantee reading just twenty entries. Other filters may discard many candidates before filling a page. Indexes also cost storage and writes; check existing indexes before creating the example.

In a controlled environment, use representative data and EXPLAIN (ANALYZE, BUFFERS) to compare shallow and deep pages. Inspect index conditions, filtering, sorts, and actual reads. ANALYZE executes the query, so do not try an expensive query on production casually. See using EXPLAIN.

Sorting NULL last does not make it the largest value

NULLS LAST controls placement in a sort. It does not turn NULL into an ordinary maximum value:

SELECT NULL::integer < 10; -- NULL, not true

WHERE keeps true results. A tuple comparison can finish before reaching a later NULL if earlier fields decide the order. If it must compare a NULL to decide, the result can be unknown.

Adding NULLS LAST alone does not make a cursor cross the NULL section correctly. Non-null sort fields are easier to reason about when the data model permits them. Otherwise, design the cursor and conditions for crossing sections and test the boundary. Arbitrarily replacing missing values with zero changes the meaning.

Keyset is not a snapshot

Changing a sort value can move a previously read row behind the cursor and make it appear again. Independent page queries do not automatically share one snapshot.

Sequin’s engineering article discusses keyset pagination for backfills, including updates and commit timing. A complete export needs its own consistency strategy. A list cursor alone does not guarantee a complete synchronization protocol.

I would keep OFFSET as an option for a small, stable administrative list that needs page jumps. Keyset is worth evaluating for large lists read continuously, provided sorting, filters, and indexes fit. Arbitrary page-number navigation is not a capability it supplies by itself.

What I learned

• I would trace inserts and deletes across two queries before accepting a pagination contract.
• I treat a cursor as a boundary of sort values, checking uniqueness, NULLs, and timestamp precision together.
• I separate correct results from evidence that an index reduces reads.
• A list that can continue does not automatically provide consistent exports or synchronization.

Further learning

• Use The Index, Luke!: Fetching the next page explains pagination through query access patterns.
• Caleb Curry: API Pagination is an English video of about 26 minutes. Start with OFFSET at 10:14, then cursors at 13:56 and their limitations at 18:06. Use the linked PostgreSQL documentation for database-specific NULL and index semantics.