Headline: OFFSET pagination tells PostgreSQL to read every row it skips and then throw those rows away, so deep pages get steadily slower and can silently skip or duplicate results while the table is being written to. Keyset pagination replaces OFFSET with a WHERE filter on the last row's sort key, backed by a composite index that matches the ORDER BY, and keeps every page roughly the same cost.

An internal dashboard feed I maintain started timing out, but only for the people who scrolled a long way. Page 1 came back in milliseconds. Page 40 took seconds. The SQL had not changed, the index was still there, and the ORM was innocent. The table had simply grown, and OFFSET is an instruction to read rows and discard them. These are my notes from replacing it.

Key takeaways

OFFSET is linear in the offset. LIMIT 20 OFFSET 10000 makes PostgreSQL produce 10,020 rows in sort order and discard 10,000 of them, so the cost of a page grows with how deep the page is.

Keyset pagination is a range seek, not a skip.…