OFFSET 3 hands back a page-one row after a single insert above the window

OFFSET counts ranks, and a rank belongs to the result set as it exists when the query runs. The dev.to post describes OFFSET 3 as the 4th row onward in the ordering as it stands now ^[5]. Insert id 11 above the window and the row that was third becomes fourth, so item8 arrives on page two after appearing on page one ^[4]. You can add indexes all week and item8 still comes back ^[1]. Part 1 of the same series measured the speed half: at depth 9,999,980 PostgreSQL reads ten million rows and discards all but twenty ^[2].

Deletion moves the boundary the other way. A row slides up past it, and paging never reaches that row ^[6].

The cursor query asks a different question. WHERE id < 8 names a position in the ordering, and on the shifted table it returned 7, 6 and 5 ^[7]. Rows arriving or leaving elsewhere in the set change ranks; they do not change id = 8 ^[8].

Keyset stops there. Order a feed by updated_at DESC, let someone edit an old post, and that row jumps ahead of the cursor and is served a second time ^[9]. Order by total_amount, let an amount change, and a row can move behind the cursor, and the query never returns it ^[10]. So the first requirement is an ordering nobody rewrites, which is why created_at plus the primary key is the post's default ^[11]. Where the product genuinely needs most-recently-updated-first, the post's advice is to accept that pagination over it is approximate and say so ^[12].

created_at alone is not enough at volume. In the author's ten-million-row orders table, ids 5000001 through 5000100 all carry created_at 2025-01-01 13:53:20+00 ^[13]. WHERE created_at < :cursor cannot express where inside those hundred rows the last page ended, so the duplicates and gaps return ^[14].

The form of the comparison is the third requirement. (created_at, id) < (:last_created_at, :last_id) means created_at is less than the timestamp, or equal to it with a smaller id ^[16]. PostgreSQL can push that row comparison into a composite index as a single Index Cond, and expanding it into OR by hand can lose the index ^[17].

With orders (created_at DESC, id DESC) built ^[18], the plan at the depth that cost OFFSET 110,659 pages ^[20] reports an Index Only Scan, Heap Fetches: 0, Buffers: shared read=4, and Execution Time 0.062 ms ^[19]. Four pages against 110,659 is roughly 27,700 times fewer ^[21]. For that to transfer, the read has to stay inside the index as it does here: the query selects id and created_at, both index columns, which is what keeps heap fetches at zero ^[15][19].

The fourth requirement is the index direction, and the usual advice is imprecise. A B-tree walks backwards, so flipping every column at once is free ^[22]. The DESC/DESC index answered ORDER BY created_at ASC, id ASC with an Index Only Scan Backward in 0.030 ms and no sort node ^[23]. Mixing directions between columns is the case that breaks: the post's plan for ORDER BY created_at DESC, id ASC shows an Incremental Sort, and the published excerpt ends before the timing ^[24].