OFFSET Pagination: Page Two Skipped a Row

The client asked for page 1, then page 2. Twenty orders each. Between the two requests a new order landed at the top of ORDER BY created_at DESC. Page 2's OFFSET 20 started one row later than page 1's last row. The order that had been #20 disappeared. Nobody deleted it. OFFSET counts from a live table, not from the window you already sent.

That is the whole property. LIMIT 20 OFFSET 20 means "skip twenty rows now, then take twenty." It does not mean "the next twenty after the ones I already returned." If the table moved, the window moves.

The API design post names this in a paragraph and tells you to prefer cursors. This post is the failure, the SQL that causes it, and the keyset that does not.

What OFFSET actually does

SELECT id, created_at, number
  FROM orders
 ORDER BY created_at DESC, id DESC
 LIMIT 20 OFFSET 20;

The engine walks the sort, discards the first twenty rows it sees in this execution, and returns the next twenty. There is no bookmark. The discarded rows are whoever is in positions 1–20 at this instant.

A second property sits next to the skip: the walk is not free. Postgres (and MySQL, and SQL Server) still produce those twenty discarded rows, then throw them away. Page 50 with OFFSET 980 does the same work as pages 1 through 49 plus one more page. An index on (created_at DESC, id DESC) makes the walk an index scan. It does not make the discard free.

page and per_page in the URL are just arithmetic on top of that:

OFFSET = (page - 1) * per_page

They inherit the same meaning. Page 2 is not "after page 1." It is "skip 20, now."

The table moved

Twenty orders, newest first. Client holds page 1: ids 20 through 1. A new order 21 commits. Page 2 runs OFFSET 20.

Before insert (newest first):  20 19 18 … 2 1
Page 1 returned:               20 19 … 1          (positions 1–20)

After insert:                  21 20 19 … 2 1
OFFSET 20 now discards:        21 20 19 … 2
Page 2 returns:                1                   and then older rows

Id 1 appears on both pages. The row that used to sit at position 21 is now at 22 and waits for page 3. The client that concatenates pages has a duplicate and a hole.

A delete is the other direction. Cancel order 20 after page 1. OFFSET 20 now starts at what used to be position 22. The row that was #21 is gone from the listing. Nobody filtered it. The window slid up.

The news feed post calls this out because feeds mutate on every refresh. An orders table that receives inserts while a clerk pages through "unshipped" is the same shape. So is a search index that is still being written. Stability is not a property of LIMIT. It is a property of a predicate that names the last row you already saw.

Keyset names the last row

Keyset pagination (seek method, cursor pagination) does not count. It asks for rows after a sort position you already returned.

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

The cursor is the (created_at, id) of the last row on the previous page. The comparison is on the same columns as ORDER BY. The next page is "strictly older than this pair," not "skip N."

An insert of 21 after page 1 does not change that predicate. 21 is newer than the cursor, so it is not in the WHERE. The next twenty older rows are the same twenty you would have gotten if nothing had inserted. A delete of a row you already returned also does not move the window. A delete of a row you have not seen yet simply means that row will never appear — which is correct, it is gone.

The pair is required. created_at alone is not unique. Two orders in the same timestamp, or the same millisecond if you store timestamptz, make < :cursor_created_at skip a sibling and <= re-emit the last row. id is the tiebreaker. It has to be unique, and it has to be in both the ORDER BY and the WHERE. A Snowflake-style id that already sorts by time can be the whole cursor. A random UUID cannot; you still need the real sort key next to it.

The row comparison (created_at, id) < (:c_ts, :c_id) is the tuple form. If the driver or the dialect does not like it, expand it:

WHERE created_at < :cursor_created_at
   OR (created_at = :cursor_created_at AND id < :cursor_id)

Same meaning. The index is still (created_at DESC, id DESC). A mismatch — ORDER BY created_at DESC and WHERE id < :cursor_id — is a different query. It will look right on a monotonic id and lie the moment two clocks, two workers, or a backfill insert an older id with a newer timestamp.

The cursor is a position, not a page number

Encode the pair. Do not hand the client a raw SQL fragment.

GET /orders?limit=20
→ items, next_cursor = b64("2026-10-22T14:01:09Z|7f3a…")

GET /orders?limit=20&cursor=eyJ0IjoiMjAyNi0xMC0yMlQxNDowMTowOVoiLCJpIjoiN2YzYSJ9
→ items strictly older than that pair, next_cursor from the new last row

has_more is "the page came back full," or one extra row you do not return (LIMIT 21, send 20). Do not run COUNT(*) to decide. The count is a second query on a moving table. It is also the number OFFSET UIs print as "page 3 of 93," which is already stale by the time you serialize it.

Previous-page is the opposite inequality and the opposite ORDER BY, then reverse the rows in the app:

SELECT id, created_at, number
  FROM orders
 WHERE (created_at, id) > (:cursor_created_at, :cursor_id)
 ORDER BY created_at ASC, id ASC
 LIMIT 20;

You are seeking newer than the first row of the current page. Flip the result so the client still sees newest-first. A single cursor plus a direction flag is enough. Two cursors (before, after) is the same idea with less state on the client.

Filters belong in both the first page and every later page. status = 'unshipped' in the first query and missing from the keyset query will pull shipped rows that happen to sort after the cursor. The cursor does not remember the filter. You do.

NULL in a sort column is a hole. SQL's NULL is not less than a timestamp and not greater than one. A row with a null created_at will never satisfy < or >. Do not keyset on a column you allow to be null. Coalesce it into a real value you control, or keep it NOT NULL.

When OFFSET is still the right call

A human typed "page 47." Keyset cannot jump there. It can only walk from a known row. An admin grid that must deep-link to a page number, on a table that barely moves during the session, can live with OFFSET. Tell the UI the total is approximate if you show one.

The result is a ranking you cannot seek. "Sort by relevance" from an external scorer, or a ORDER BY random(), has no stable key. OFFSET is then counting a list you already materialized. Materialize it on purpose (a snapshot table, a search engine's own cursor) rather than re-running a non-deterministic ORDER BY with a bigger offset.

The set is tiny and static. Ten rows of reference data. The anomaly still exists. You will not observe it.

What OFFSET is not:

• A way to skip work. Page 100 still reads 100 pages of index.
• A cursor. Passing offset=20 as if it were "after page 1" is how the clerk lost order 20.
• Compatible with ORDER BY that is not deterministic. Without a unique tiebreaker, two executions of the same OFFSET can return different members of a timestamp tie. That is a skip even when nobody writes.

Need	Tool
Next / previous over a live table	Keyset on the ORDER BY columns, unique tiebreaker
Jump to page N on a quiet table	OFFSET, accept the slide
"How many total?"	A separate count, or none
Feed, inbox, activity, anything that inserts at the top	Keyset. The feed already made this choice

The clerk in the first paragraph did not have a bug in LIMIT. They asked the database to count from the top, twice, and the top changed. Send the last (created_at, id) back. Ask for rows older than that pair. The new order stays on page 1, where it belongs, and page 2 is the twenty that were actually next.