OFFSET 100000 LIMIT 20 makes the database read and throw away 100,000 rows — it gets slower every page. Keyset (seek) pagination remembers where you stopped and jumps straight there.

First page:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

Next page — pass the last row’s created_at and id from the previous page:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The row-value comparison (a, b) < (:a, :b) does the right thing across the tiebreaker.

Dialect notes:

Row-value comparison (a, b) < (x, y) works in PostgreSQL, MySQL, SQLite, and DuckDB.
BigQuery and Snowflake don’t support tuple comparison — expand it by hand:

WHERE created_at < :last_created_at
   OR (created_at = :last_created_at AND id < :last_id)

Always include a unique tiebreaker (like id) in the ORDER BY and the cursor. Without it, rows sharing a created_at can be skipped or repeated between pages. Keyset gives next/prev paging, not random “jump to page N” — for that, OFFSET is still the simple choice on small tables.

Fast pagination without OFFSET (keyset / seek method)