OFFSET counts from the top. Keyset opens at a bookmark.
A B-tree stores keys, not positions, so OFFSET 100,000 walks 100,020 entries. “After this row” is one seek plus 20.
OFFSET only costs speed?
Bad: Only deep pages hurt
A new order slides every row down, so page 2 repeats O7.
Good: Positions move
Keyset’s cursor is a value, not a position. Inserts above it change nothing.
The keyset query, line by line
- Remember the last row’s (created_at, id)
- WHERE (created_at, id) < (last_ts, last_id)
- ORDER BY created_at DESC, id DESC
- LIMIT 21: a 21st row means has_more
Three orders share created_at 19:30:00, and the page boundary falls between them.
The trap
With < you skip two; with <= you repeat one. End the sort with a unique column, usually the id.
Check EXPLAIN. Each engine seeks on a different spelling.
PostgreSQL seeks on the row value, MySQL on the expanded OR. The OR form with a leading created_at bound seeks on both.
501×
more rows shipped by OFFSET across 16 shards
No shard knows its rows’ global position, so for page 501 each sends its top 10,020: 160,320 rows. Keyset ships 320.
What keyset gives up
- No page 37: jump to a date instead
- No cheap total: show a capped “1,000+”
- NULL sort keys vanish: make the column NOT NULL
- Mutable keys move: page exports by id
Remember
OFFSET reads every row it skips. Keyset seeks with a unique tie-breaker and a matching index, 20 rows at any depth.