Why does page 2 skip rows?

Paging through a 100,000-row Postgres 16 table with LIMIT 20 OFFSET n while another connection deleted 100 rows a second delivered 98,415 rows and silently missed 1,092 of them — 1.1% — with no error, no warning and no gap in the page numbers. At 1,000 deletes a second it missed 8,762 of 84,326. The same pass with a keyset cursor on (created_at, id), deliberately slowed down so it was exposed to more concurrent writes than the OFFSET run, missed zero.

This is the failure mode where nobody files a bug. The importer finishes. The page count looks right. Some records just never arrive.

The short answer

• LIMIT n OFFSET m is only correct if the result set does not change between requests. OFFSET counts rows by position. Anything that changes a row's position after you have passed it changes which row lands at the next offset.
• Deletions cause skips; insertions cause duplicates. On a created_at DESC feed, 1,585 concurrent deletes cost 1,092 skipped rows, and 948 concurrent inserts caused 948 rows to be delivered twice. Neither raised an error.
• Keyset pagination — WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20 — missed 0 and duplicated 0 at every write rate tested, including 16,740 inserts and 14,107 deletes during a single pass.
• The composite tiebreak on id is the whole trick. With 30 rows sharing each timestamp, WHERE created_at < $1 lost 33,330 of 100,000 rows on a completely idle table, and the "safe" <= version looped forever, returning the same 20 rows 8,000 times.
• OFFSET also gets slower with depth; keyset does not. Page 15,000 of a 300,000-row table took 9.1 ms with OFFSET and 0.25 ms with keyset. A full pass took 8,727 ms versus 780 ms.

What was measured, and on what

Apple M3, 8 cores, 16 GB RAM, macOS 26.4.1. Postgres 16.15 (aarch64, Debian) in Docker 29.5.2 with 8 CPUs and 8.3 GB visible to the Docker VM, shared_buffers=512MB, max_connections=100, --shm-size=1g. Client is Node v23.5.0 with pg 8, over TCP to a published port.

Two tables: orders, 300,000 rows with a (created_at DESC, id DESC) index, for the depth and cost measurements; feed, 100,000 rows, recreated before every run, for the correctness runs. Page size is 20 throughout, so a clean pass is 5,001 pages. Ordering is created_at DESC — newest first, the normal case.

A second connection writes at a fixed rate for exactly as long as the pass lasts. Afterwards the stable set is computed — rows that existed when the pass started and still existed when it ended. Missed is a stable row the paginator never returned; delivered twice is a row returned more than once. These are lower bounds: this paginator does nothing between pages, so it is exposed for the shortest possible time. A real importer is worse.

How many rows does OFFSET actually miss?

Deletions first, because deletions are what cause the skip. A row removed from above the cursor shifts every row below it up by one position — into the gap the paginator has already read past.

The relationship is exact, not statistical. Every delete above the cursor costs precisely one unseen row; every delete below it costs nothing. At 100 deletes a second, 1,092 of 1,585 deletes — 69% — landed above the cursor and each buried a different record. As a fraction of the table that is 1.1% lost at 100 deletes a second and 10.4% at 1,000, over passes lasting 16.2 and 17.4 seconds. The pass is the exposure window, which is why a slow OFFSET paginator is doubly bad: it is wrong, and it stays wrong for longer.

Why do inserts duplicate instead of skip?

This is the result I expected to go the other way, and it is worth stating plainly: on a newest-first list, inserts never cause a skip. New rows arrive at the head, above everything the paginator has already read, and push the remaining rows down by one position each. The next offset then lands on a row that was already returned. At 1,000 inserts a second the pass returned 13,140 duplicate rows and ran 658 pages longer than it should have — and still saw all 100,000 originals.

The two errors are not symmetric, and on a table doing both they partially cancel. The mixed run — 100 inserts and 100 deletes a second — missed only 97 rows, an order of magnitude fewer than deletes alone, because the inserts kept pushing rows back into view. That cancellation is why the bug survives in production for years: the error rate is not stable, not proportional to anything you monitor, and on a quiet day it is zero. If your importer deduplicates by primary key, insert-driven duplicates are invisible and harmless. Delete-driven skips are neither.

Does a keyset cursor actually fix it?

A keyset cursor does not ask for "row 40 onwards". It asks for "rows sorting after this specific value", so positions can shift underneath it without effect.

SELECT id, created_at FROM feed
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The unpaced keyset runs finish in under a second, so a sceptic can fairly say they were not exposed to enough churn. The paced rows answer that: a 2 ms delay per page stretches the pass to roughly the OFFSET run's wall time, at which point keyset absorbed 14,107 deletes and 16,740 inserts and still missed nothing. Correctness does not come from being fast. It comes from the cursor being a value rather than a count.

Why does keyset need a tiebreak on id?

Because timestamps collide, and the version everybody ships first — WHERE created_at < $1 — breaks the moment they do. 100,000 rows, page size 20, no concurrent writes at all, varying how many rows share each timestamp:

The strict comparison discards the tail of whatever timestamp group straddles the page boundary. Switching to <= to "be safe" replaces silent loss with silent repetition, and once a group is larger than the page size it stops making progress at all: the last cell is a run that returned the same 20 rows 8,000 times before hitting the abort cap. It would still be running.

The composite comparison has neither problem, because (created_at, id) is unique. Postgres evaluates row constructors lexicographically and matches them straight to a composite index — no OR chains, no rewriting. And none of this needs concurrency: it is a bug on a table nothing is writing to.

The cursor bug that is not in the SQL

While building this I lost 4,332 rows to correct SQL. Postgres stores timestamptz to microsecond precision; a JavaScript Date holds milliseconds. Read a cursor value into a Date and send it back and you have quietly rounded it, and rows in the gap disappear.

50,000 rows spaced 137 microseconds apart, correct composite-tiebreak keyset query, idle table:

cursor_type=JS Date (millisecond)      pages=2284  distinct_seen=45668  MISSED=4332
cursor_type=string (microsecond)       pages=2501  distinct_seen=50000  MISSED=0

Nearly 9% of the table gone. The first version of this benchmark spaced rows 100 microseconds apart and reported zero missed, because every cursor row happened to land on an exact millisecond. That is how narrowly this hides. The fix in pg is one line — pg.types.setTypeParser(1184, v => v) — or never parse the cursor at all, which is the better habit: an opaque cursor is a token the client returns verbatim, not a value it interprets.

What does a deep OFFSET cost?

OFFSET has no way to jump. It scans and discards. On the 300,000-row orders table, page size 20, median of 25 timed calls after five warm-up calls. The whole grid was run twice; the numbers below are the second run, and no depth differed between the two by more than 0.12 ms:

Linear against depth for OFFSET, flat for keyset. EXPLAIN (ANALYZE, BUFFERS) on page 10,000 says exactly why:

-- LIMIT 20 OFFSET 199980
 Limit (actual time=15.046..15.048 rows=20 loops=1)
   Buffers: shared hit=770
   ->  Index Only Scan using orders_feed_idx on orders (actual time=0.011..10.532 rows=200000 loops=1)
 Execution Time: 15.060 ms

-- WHERE (created_at,id) < ($1,$2) LIMIT 20
 Limit (actual time=0.005..0.007 rows=20 loops=1)
   Buffers: shared hit=4
   ->  Index Only Scan using orders_feed_idx on orders (actual time=0.005..0.006 rows=20 loops=1)
         Index Cond: (ROW(created_at, id) < ROW('2026-08-29 18:22:37.811375+00'::timestamptz, 199980))
 Execution Time: 0.027 ms

200,000 index entries read and discarded, 770 buffers, to return 20 rows; the keyset query touches 4. Both use the same index. Over a whole table the cost compounds — 8,727 ms to page 100,000 rows with OFFSET against 780 ms with keyset, and the gap widens with table size. It is the same counting-versus-seeking distinction that makes COUNT(*) expensive.

What does the index cost to maintain?

Keyset needs (created_at DESC, id DESC). On 300,000 rows it built in 59 ms and occupies 9,256 kB against a 17 MB heap. Its write cost, measured on the same table with and without it:

38% on per-row insert latency, 86% on bulk load, 48% on disk. That is the real price, and it is not an argument against keyset: OFFSET needs the same index to sort at all, so on a list you already order this way you are paying it either way.

Can you just hold a transaction open instead?

Yes, and it works. BEGIN ISOLATION LEVEL REPEATABLE READ before page 1 and COMMIT after the last page gives every page the same snapshot. The measured run — OFFSET paging inside one repeatable-read transaction while 15,517 rows were deleted — returned all 100,000 rows, missed 0, duplicated 0.

The cost is that the snapshot has to be kept alive. While that transaction was open, VACUUM feed reclaimed nothing — 15,517 dead tuples before, 15,517 after — because the paginator might still need to see them, and the oldest backend_xmin had fallen 842 transactions behind. After COMMIT, the same VACUUM took the dead count to 0. That is the good case: a 17-second transaction on a laptop. Add human think-time and you have a reader pinning the vacuum horizon for minutes, which is what quietly wrecks a busy table. It also makes the pagination stateful — one connection bound to one user's scroll.

A materialised snapshot has neither problem:

CREATE TABLE feed_snap AS
  SELECT row_number() OVER (ORDER BY created_at DESC, id DESC) AS pos, id
  FROM feed;
ALTER TABLE feed_snap ADD PRIMARY KEY (pos);

47 ms to build, 7,328 kB, and paging it by pos took 689 ms for the whole table with 0 missed and 0 duplicated. Its cost is staleness rather than correctness: 716 rows were deleted during the pass and the snapshot served every one of them as if it still existed. Fine for an export or a report; for an importer it means re-checking each row exists before acting on it.

What if the client sends a cursor for a deleted row?

This is where a keyset implementation usually goes wrong. If the cursor is just an id and the server looks the value up —

WHERE (created_at, id) < ((SELECT created_at FROM feed WHERE id = $1), $1)

— then deleting that row makes the subquery return NULL, the comparison returns NULL, and the page comes back empty. Not an error. An empty page, which every paginator on earth treats as "you have reached the end". Measured, with the cursor row deleted after page 100 of 5,001:

cursor=lookup   pages=101   rows_delivered=2000     rows_in_table=99999   LOST=97999
cursor=opaque   pages=5001  rows_delivered=100000   rows_in_table=99999   LOST=-1

98% of the table, gone, because one row was deleted while a user held a cursor. The opaque run's LOST=-1 is not a rounding artefact: it delivered 100,000 rows against 99,999 still in the table, because the row that was deleted had already been sent on page 100. Nothing was lost.

The fix is to never look the cursor up. Carry both values in the token — base64 of {"t":"2026-08-29T18:22:37.811375Z","id":199980} is enough — and compare against the values directly. A deleted row's timestamp still sorts exactly where it did. Nothing needs to exist for the comparison to be correct.

Check it yourself

Postgres 16 in Docker, 20,000 rows, 200 deletes a second, 3 ms of pretend work per page. Runs in about a minute.

docker rm -f -v pagedemo 2>/dev/null
docker run -d --name pagedemo -e POSTGRES_PASSWORD=pg -e POSTGRES_DB=demo \
  -p 55702:5432 postgres:16
until docker exec pagedemo pg_isready -U postgres -q; do sleep 1; done
mkdir -p /tmp/pagedemo && cd /tmp/pagedemo && npm init -y >/dev/null && npm i pg

// demo.mjs
import pg from 'pg'
pg.types.setTypeParser(1184, v => v)              // keep microsecond precision
const CFG = { host:'127.0.0.1', port:55702, user:'postgres', password:'pg', database:'demo' }
const N = 20000, PAGE = 20, DEL_PER_SEC = 200, WORK_MS = 3
const sleep = ms => new Promise(r => setTimeout(r, ms))

async function seed(c) {
  await c.query('DROP TABLE IF EXISTS feed')
  await c.query('CREATE TABLE feed (id bigserial PRIMARY KEY, created_at timestamptz NOT NULL)')
  await c.query(`INSERT INTO feed (created_at)
                 SELECT now()-(i||' seconds')::interval FROM generate_series(1,${N}) i`)
  await c.query('CREATE INDEX feed_idx ON feed (created_at DESC, id DESC)')
  await c.query('VACUUM ANALYZE feed')
}

async function run(method) {
  const cp = new pg.Client(CFG), cw = new pg.Client(CFG)
  await cp.connect(); await cw.connect(); await seed(cp)
  const deleted = new Set(); let live = true
  ;(async () => { while (live) {                       // the concurrent writer
      await sleep(20)
      const ids = Array.from({ length: Math.round(DEL_PER_SEC * 0.02) },
                             () => 1 + Math.floor(Math.random() * N))
      const r = await cw.query('DELETE FROM feed WHERE id = ANY($1::bigint[]) RETURNING id', [ids])
      for (const x of r.rows) deleted.add(Number(x.id))
    } })()

  const seen = new Set(); let off = 0, cur = null
  for (;;) {
    const r = method === 'offset'
      ? await cp.query(`SELECT id, created_at FROM feed
                        ORDER BY created_at DESC, id DESC LIMIT ${PAGE} OFFSET ${off}`)
      : cur
        ? await cp.query(`SELECT id, created_at FROM feed WHERE (created_at,id) < ($1,$2)
                          ORDER BY created_at DESC, id DESC LIMIT ${PAGE}`, [cur.created_at, cur.id])
        : await cp.query(`SELECT id, created_at FROM feed
                          ORDER BY created_at DESC, id DESC LIMIT ${PAGE}`)
    for (const x of r.rows) seen.add(Number(x.id))
    if (r.rows.length < PAGE) break
    off += PAGE; cur = r.rows[r.rows.length - 1]
    await sleep(WORK_MS)                                // pretend the importer does work
  }
  live = false; await sleep(50)
  let missed = 0
  for (let id = 1; id <= N; id++) if (!deleted.has(id) && !seen.has(id)) missed++
  console.log(`${method.padEnd(6)}  deleted_during_run=${deleted.size}` +
              `  still_in_table=${N - deleted.size}  rows_delivered=${seen.size}  MISSED=${missed}`)
  await cp.end(); await cw.end()
}

await run('offset'); await run('keyset')

$ node demo.mjs
offset  deleted_during_run=  969  still_in_table=19031  rows_delivered=19035  MISSED=499
keyset  deleted_during_run=  820  still_in_table=19180  rows_delivered=19593  MISSED=0

docker rm -f -v pagedemo

Run it twice; the OFFSET number moves, which is the point. It is never zero and it is never reported.

Where this goes next

The order of operations for anything that pages a list it does not own:

1. Order on something unique — (created_at, id), not created_at. If the sort column is nullable, the index needs the same NULLS LAST or the plan will not match.
2. Make the cursor an opaque token carrying every ordering column's value. Never an id the server looks up, never a bare timestamp, never a page number.
3. Compare with a row constructor, (a, b) < ($1, $2), so Postgres walks the composite index in one seek.
4. Keep the precision. Verify a cursor round-trips byte-for-byte through your driver before you trust the pagination.
5. Use OFFSET only where the answer may be approximate — a page number a human clicked. Never for an import, export, reconciliation or backfill.

This is the same cursor discipline as polling an API that has no webhooks, and for the same reason: the collection moves while you read it, and position is not identity.

Workflow Builder pages every connector this way — a stored (sort_value, id) cursor per connection, resumable after a crash, and never an offset.