Cursor vs Offset Pagination: Why Page 5,000 Is Slow

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

This query is slow, and the reason is worth internalising because it is not obvious from the syntax: OFFSET does not skip rows. There is no way to skip. The database generates row 1, discards it, generates row 2, discards it, and continues for a hundred thousand rows before it starts keeping any.

An index does not save you. It makes each row cheap, and you are still paying for 100,020 of them to return 20.

The cost grows linearly with the page number, which produces the characteristic signature: page 1 is instant, page 50 is fine, page 5,000 times out. Your monitoring shows a fast p50 and a horrible p99, because the p99 is bots and scrapers walking deep into the list.

The correctness bug, which is worse

Performance is the famous problem. This one is quieter and does real damage.

You fetch page 1 (rows 1–20). While the user reads, three posts are created. Those go to the top. You fetch page 2 (OFFSET 20) — and rows 18, 19 and 20 of the old ordering are now at positions 21, 22 and 23.

The user sees three items twice. Delete rows instead of inserting them and the mirror happens: items silently vanish before being seen.

Cursor pagination

Stop counting. Remember where you stopped and seek there.

-- page 1
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;

-- page 2: the last row of page 1 was (2026-08-01 10:00:00, 8842)
SELECT * FROM posts
WHERE (created_at, id) < ('2026-08-01 10:00:00', 8842)
ORDER BY created_at DESC, id DESC
LIMIT 20;

With an index on (created_at DESC, id DESC) the database descends to that exact position and reads 20 rows. Page 5,000 costs the same as page 1, and rows inserted above your position cannot shift anything, because your position is a value, not a count.

Two details that are easy to get wrong:

Use a row-value comparison. (created_at, id) < (?, ?) is one lexicographic comparison the planner can turn into a single index seek. Writing it as created_at < ? OR (created_at = ? AND id < ?) is logically the same and often plans worse.

Always include a unique tiebreaker. If created_at is not unique, two rows share a timestamp and the boundary between pages is ambiguous — you will drop or repeat rows exactly at page edges. Appending the primary key makes the sort key total. This is one of the arguments for time-ordered ids like UUIDv7: the id alone is both unique and chronological.

Encode the cursor as opaque

Do not hand clients raw column values. Encode them:

{ "next": "eyJjIjoiMjAyNi0wOC0wMVQxMDowMDowMFoiLCJpIjo4ODQyfQ" }

Two reasons, both practical. It stops clients constructing cursors by hand and depending on your sort columns, so you can change the ordering later without breaking them. And it lets you version the payload — add a field, bump a version byte, reject stale shapes cleanly.

Base64 of a small JSON object is enough. It is not a security boundary: never put anything in a cursor that the user is not allowed to see, and always re-apply your authorisation filters on the next query. A cursor is a bookmark, not a capability.

What you give up

Jumping to page N. Cursors are inherently sequential — to reach page 50 you must walk 49 pages. There is no cursor for “page 50” because a cursor is a position in data, not an index into a list. If your UI has numbered pages, you cannot use cursors without changing the UI.

Total counts. COUNT(*) over a large filtered set is expensive whichever pagination you use. Options: show “1,000+” past a threshold, use an approximate count from pg_class.reltuples, or drop the count. Most infinite-scroll UIs do not need it, and most numbered-page UIs cannot avoid it.

When offset is fine

Not everything needs cursors.

• Small datasets. A hundred rows will never be slow. Use offset.
• Admin tables where jumping to page 40 is a real requirement and traffic is low.
• Stable, append-only-at-the-end data where nothing shifts under the reader.

The rule: offset for pages people choose, cursors for feeds people scroll. If it is public-facing, deep, or changing while being read, use a cursor.

What you have actually changed

Not just a performance fix. You changed what “page 2” means.

Offset says the twenty rows starting at position 20 in whatever the list is right now — a definition that depends on the entire dataset and is therefore unstable and expensive. A cursor says the twenty rows after this specific row — a definition that depends only on a row you have already seen.

That is why cursors are both constant-time and correct. It is the same resolution as everywhere else in this business: stop asking a question whose answer depends on everything.