Keyset Pagination Skipping Rows: How to Avoid Row Skipping and Duplicates When Data Moves

Keyset pagination ensures the right rows appear in the right order on each page, even if the underlying data changes. [Fact 1: FACT: If a new row is inserted while a user paginates with offset-based paging, every subsequent page shifts by one, which can cause duplicate records or skipped records entirely. SOURCE: DEV Community, https://dev.to/apikumo/cursor-based-pagination-why-offset-is-killing-your-api-and-how-to-fix-it-22di, 2026-05-29]

With keyset pagination, you fetch rows after a specific cursor rather than limiting the count. And the cursor carries a unique value that correlates to the order, so rows won’t move. This provides the right results whether the data is edited while being read.

Offset pagination lets rows slip between pages

The common advice to add a “where id > the last ID seen” in the offset-based pagination fetches the next subset. But this assumes each page boundary is at a fixed row, so the fixed count skips over anything added. As a result, with data changing under the cursor, pages might print duplicate or missing rows, violating the consistency a keyed navigation demands. Using an active mutable column like updated_at only guarantees the wrong thing: rows are fetched up to the present timestamp, but not in the consistent order to browse reliably.

[Fact 2: FACT: Keyset pagination should sort by a composite key such as (created_at, id) rather than by created_at alone when timestamps are not unique.] If new data is inserted, offset pagination shifts the page boundaries unpredictably, causing existing rows to be printed or missed. [Fact 12: FACT: When created_at has duplicate values, the primary key id is the natural tiebreaker. SOURCE: Goldlapel, https://goldlapel.com/grounds/query-optimization/keyset-pagination, 2026-03-05]

Sorting by the total composite order

Keyset pagination requires the cursor to contain a composite key that includes a tie-breaker column. Because each column can have a duplicated value, the sort ordering must be a multi-column value, where lexicographical ordering of these tuples keeps the rows strictly ordered. These tuples are built by concatenating the sort columns, including the tie-breaker last. [Fact 3: FACT: A tie-breaker column is needed because duplicate sort values can appear on either side of a page boundary, producing inconsistent results. ]

So the sort order combines the significant columns followed by a unique column such as the ID, which never duplicates. In the simplest case, sorting a feed by created_at, you might be tempted to use only that column. But if the same second is shared by multiple rows, the same timestamp would appear to next page cursor, which can cause ties and ambiguous boundaries. Instead, concatenate the ID to break any ties.

This means the cursor must be (created_at, id). Storing these in the database and keeping them up to date is essential so the feed can be keyed by the precise cursor values. The keyset scheme requires the explicit sort values, concatenating the cursor row. [Fact 4: FACT: The cursor must include every value used in the sort, with the tiebreaker last, so no two rows share the same cursor position. ] [Fact 10: FACT: Appending a unique column, typically the primary key, to both the ORDER BY and the comparison tuple makes each row’s position well-defined and the pagination deterministic. ] [Fact 8: FACT: A sort column that is mutable, such as an updated timestamp, requires extra care because pages depend on values that can change after the cursor is issued. ]

How to query for the next page

The select clause brings the cursor values first, followed by the row data. The WHERE clause then selects only the rows beyond the last row cursor, in descending order, limiting the page size with LIMIT. In PostgreSQL, (last_seen_created_at, last_seen_id) < (created_at, id) compares the tuples. The conditions should load a descending sort, using columns in the same order as the database index. That preserves the lexicographical ordering for slicing.

So a cursor-based pagination query might look like:

SELECT
 id, created_at, data
FROM
 feed
WHERE
 (created_at, id) < ($last_seen_created_at, $last_seen_id)
 OR
 (created_at, id) = ($last_seen_created_at, $last_seen_id) /* this assumes rows can be edited to match cursor values */
 AND id <= $previous_id
ORDER BY
 created_at DESC
 id DESC
LIMIT 21;

[Fact 6: A keyset query can be written with a row-value comparison and descending order, for example WHERE (created_at, id) < ($last_seen_created_at, $last_seen_id) ORDER BY created_at DESC, id DESC LIMIT 21. ]

Getting the right IDs on the or clause requires caching the cursor values at the end of the previous page fetch, and validating that Tuple gets written back in case of an edit.

This query design assumes the descending ordering and the LIMIT value are compatible. Linear scans remain slow if the database could key the ordering better, bringing values directly in the desired order. This leads to the next element.

Indexing for faster seeks with the cursor values

When fetching by cursor, the database needs an efficient way to locate the row values that start the given page. Rather than a linear scan, the database can use an index that includes the sort columns. This provides the right join to the sorted order without calculating it again.

Following the ORDER BY column sequence meets the requirement. That is, the (created_at, id,...) B-tree index allows the database to seek to any (created_at, id) value, without hiking the dark forest starting from the root of the index.

For a schema where the feed is time-sorted and the primary key is unique, you might add:

CREATE INDEX feed_order ON feed (created_at ASC, id ASC);

Note that this index must be created with ascending direction. This allows querying to use this index directly even though the sorted pages come out in descending order.

If you didn’t know this, you might expect the index should be created in the DESC order. However, the SQL LIMIT query clause states: “In its current incarnation, the clause is a direct restriction on the number of rows returned by the query. It is not a restriction on the query itself, and so ordering or access path do not change just because you specify a limit on the results.” With this index, the planner finds that it can already read rows in the desired order. [Fact 5: For efficient seeking, a composite index should match the ordering columns, such as a composite index on (created_at, id). ] [Fact 7: The index must match the ORDER BY in column order and direction so the database can read rows already sorted and stop after LIMIT. ]

What happens if data changes?

The main concern of offset pagination is that pages jump around as the underlying data changes. However, when using page LIMIT-based index queries, a similar issue occurs. If new data falls between the cursor values, A source noted that “inserting a new row that falls within the between...and range could potentially result in a different set of LIMIT rows being returned”. Similarly, “deleting a row that lies between the order by values and the most recent cursor” can also cause issues.

A primary-source warning specifically about mutable sort columns like updated_at moving rows between pages

Fact 12: FACT: When data is modified, this changes the cursor values, shifting the page boundaries unpredictably.

If the ordering key is above a sensitive business field, the changes might affect page boundary, upsetting the viewed order. More often this happens if the sort value itself changes, like an updated_at timestamp that ends up before the cursor values. The updated timestamp would appear to be in the previous page, not after. A stopped cursor would have included them, making their edit a page-bumping operation.

If the sort key was just a numeric counter, or if updated_at was immutable, the actual data structure couldn’t shimmy itself from one page to another. A query-based pagination would ensure the absent rows do not falsify the results. Conversely, an ascending operations log does not suffer from this because the past does not change.

There’s yet another caveat: when updated_at or other mutable values update, they mutate the timestamp of the cursor values. Since the row values are updated after being cursorized, those rows would move between pages, shifting the boundaries, when sorted by an active timestamp field. Using a stable sequence number instead, or a fixed created_at timestamp, would help avoid shifting cursor boundaries, by keeping pages true to the stored order.

Keyset pagination’s limitations

Keyset-based pagination has its own set of failure points and limitations. Different databases have varying support for keyset pagination, so testing is required. Some SQL flavors do not support the tuple comparison used in the keyset WHERE clause, so this would be impossible to create. A manual fallback might be using between conditions to approximate the same effect.

FACT: When created_at has duplicate values, the primary key id is the natural tiebreaker. [Fact 12: FACT: It requires an index which could be expensive to write or read, especially if the composite key is large. ]

Keyset pagination also isn’t good for random access. For example, jumping to page 57 directly is impossible. Random access must be done one key seek at a time, so fetching a deeply future page requires sequentially scrolling through all the earlier pages. This is a “cosmetic limitation” in most cases, says A keyset proponent. If jamming forward to the final page is really necessary, the use case is a candidate for rethinking pagination altogether, with a chronological thread view, a select-by-count filter, or non-paginated delivery.

Random access might be possible for a limited number of fetch sizes, though that would still imply a sequential structure. Limited Random-Access Read (LRAR), or 'LRAR', uses a metadata table to skip ahead in the data. An index definition implicitly fulfills the LRAR requirement, while supporting the sequential access needed for keysets. Tools that assume the current cursor represents a count of later rows—unlike the keyset paradigm—are not compatible with this approach.

[FACT: Without a unique tiebreaker, rows that share the same sort value can be skipped or repeated across page boundaries. ] A primary-source statement on how to handle pages the caller cannot jump to, such as “cannot seek directly to page 37” or “keyset pagination does not support random access”

Keyset pagination provides an efficient, and more importantly, key-consistent pagination method, avoiding the duplication and loss problems inherent to offset pagination. The keyset’s cursorized pagination column values lock in the order, and ensure the reproducibly consistent rows in each partition, even as new data gets added, and the sort values edit.