SQL Pagination: OFFSET vs Keyset (Cursor) Pagination

The short answer

• LIMIT/OFFSET is the simple option for small tables and for cases where you need page numbers.
• Keyset (cursor) pagination is the option for large tables, infinite scroll and APIs: speed does not depend on how far the user has scrolled, and records are not duplicated.

Why OFFSET gets slow

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

To return 20 rows, the database has to find and skip 100,000 rows. An index helps with sorting but cannot "jump" straight to the right position. The deeper the page, the more work: the first pages are fast, distant ones are slow.

Why OFFSET returns duplicates and gaps

While a user is looking at page one, a new record is inserted. Every row shifts by one position, and the last record of page one shows up again at the top of page two. A deletion does the opposite: a record disappears between pages.

In an infinite feed this is very visible: the user sees the same post twice.

How keyset pagination works

Instead of "skip N rows", the query says "give me the records after the last one shown":

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Here $1 and $2 are the created_at and id of the last record on the previous page.

Three requirements:

1. A stable sort. The sort must order rows unambiguously. created_at alone is not enough because two records can share a timestamp, so add the unique id as a tie-breaker.
2. The WHERE clause matches ORDER BY. Same fields, same order, same direction.
3. A composite index on the sort fields:

CREATE INDEX posts_created_at_id_idx ON posts (created_at DESC, id DESC);

With this index the database finds the cursor position directly and reads only 20 rows, whether it is page 1 or page 1,000.

Row value comparison (a, b) < (x, y) is supported in PostgreSQL and MySQL. If your database or planner handles it poorly, expand the condition:

WHERE created_at < $1 OR (created_at = $1 AND id < $2)

How to build it into an API

Give the cursor to the client as an opaque string, for example base64 of a JSON with the sort fields:

{
  "items": [ ... ],
  "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTAxLTE1VDEwOjAwOjAwWiIsImlkIjo0Mn0="
}

Practical rules:

• Fetch LIMIT + 1. If 21 rows come back, there is a next page: return 20 and a cursor from the twentieth. Otherwise return nextCursor: null.
• Do not let clients build cursors themselves. Then you can change the format without breaking them.
• Validate the cursor on the server. It is user input: it can be forged or malformed.
• A cursor is tied to its sort. If the user changes the sort or filter, pagination starts over.

Feeds, admin panels and page numbers

Keyset cannot "jump to page 37": that requires knowing the position, which means counting rows. Also keep in mind that COUNT(*) for "total pages" on a large table is expensive too. Showing "more than 10,000" or an estimate is often enough.

Previous page is the mirror image: use > instead of <, reverse the sort, then reverse the fetched batch before returning it.

Common mistakes

• Sorting only by a non-unique field: rows with equal values get lost or duplicated between pages.
• The index exists, but its field order or direction does not match ORDER BY.
• Sorting by a nullable field without explicit NULL handling in the condition and the index.
• A cursor that is just a page number, which is OFFSET under another name.