Keyset Pagination vs OFFSET Pagination: Which One and When

OFFSET pagination: the familiar approach

SELECT id, title, created_at
FROM articles
ORDER BY id
LIMIT 20 OFFSET 1000;

This is the pagination almost everyone reaches for first — it's simple, and it directly supports "show me page 51," since the offset is just (page - 1) * page_size. The tradeoff shows up only once the table and the offset both get large.

Why OFFSET gets slower on deeper pages

OFFSET does not teleport past the skipped rows — it cannot, because the database has no way to know which physical rows correspond to "row 1000" without first counting through the sorted result up to that point. Every request for page 51 re-scans and discards the same 1,000 earlier rows, then finally returns the 20 you asked for. Page 50,000 discards a million rows, every single time that page is requested.

SELECT id, title, created_at
FROM articles
ORDER BY id
LIMIT 20 OFFSET 1000000;

Index Scan using articles_pkey  (rows=1,000,020)
  -> must traverse and discard 1,000,000 rows
Planning Time: 0.3 ms

SELECT id, title, created_at
FROM articles
WHERE id > 1000000
ORDER BY id
LIMIT 20;

Index Scan using articles_pkey  (rows=20)
  Index Cond: id > 1000000
Planning Time: 0.2 ms

~450x faster on a 5-million-row table, indexed on id in both cases — the gap only widens as the page number climbs higher.

There's a second, quieter problem: OFFSET pagination isn't stable under concurrent writes. If a row is inserted or deleted between the time a user loads page 2 and requests page 3, every row after that point shifts position by one, and the next page can silently skip a row or show a duplicate.

Because keyset pagination always filters relative to the last row's actual key value rather than a row count, a delete or insert elsewhere in the table has no effect on where the next page starts.

Keyset pagination: seeking instead of skipping

-- First page
SELECT id, title, created_at
FROM articles
ORDER BY id
LIMIT 20;

-- Next page: pass the last id seen (say, 1020) as the cursor
SELECT id, title, created_at
FROM articles
WHERE id > 1020
ORDER BY id
LIMIT 20;

The "cursor" is nothing more than the sort key value of the last row on the current page — usually encoded (often base64) and handed back to the client to send with the next request. As long as that column is indexed, WHERE id > 1020 is an index seek straight to the right starting point, no matter how far into the table it is.

Handling ties with a composite key

Pagination is rarely sorted by a unique id alone — sorting by created_at is far more common, and timestamps can repeat. A naive WHERE created_at > :last_created_at will silently skip or duplicate rows that share the exact same timestamp as the cursor row. The fix is a composite key: sort and filter by the non-unique column plus a unique tiebreaker.

SELECT id, title, created_at
FROM articles
WHERE (created_at, id) > ('2026-07-20 09:00:00', 1020)
ORDER BY created_at, id
LIMIT 20;

The row-value comparison (created_at, id) > (:cursor_created_at, :cursor_id) — supported directly in PostgreSQL and MySQL, and expressible via an equivalent OR-chain in databases without row-value syntax — correctly orders by timestamp first and falls back to id only to break ties, guaranteeing every row is seen exactly once.

Comparison table