Offset vs cursor pagination

The decision

Paging means returning a long list in small parts called pages. With offset paging, the client says "skip 40 rows, then give me 20" (limit=20&offset=40). With cursor paging, the client sends a cursor, a token that marks the last item it saw. The server returns the next 20 items after it. Offset is simple and lets a user jump to any page. A cursor stays fast on deep pages and does not repeat or miss items when the list changes.

Choose offset when

• The list is small. Assumption: the whole list stays under 10,000 rows.
• Users must jump to a page number, such as "page 7 of 30".
• The data rarely changes while a person reads it, as in an admin table.
• You want the simplest code and a total count on screen.

Choose cursor when

• The list is long or grows without limit: feeds, orders, logs, messages.
• New rows arrive while people scroll. Offset would show repeats or skip items.
• Clients read to the end, such as an export or a sync job.
• Users scroll and never jump to page 700.

The numbers that change the answer

Assumptions: the shop has 1,000,000 orders. The page size is 20. The database works through 200,000 rows per second for this query.

Last page. Offset asks for offset=999980. The database must still compute the skipped rows, so it works through 999,980 + 20 = 1,000,000 rows (the PostgreSQL manual says so; see Sources). A cursor query works through 20 rows.

• Rows: 1,000,000 / 20 = 50,000 times more for offset.
• Time: offset 1,000,000 / 200,000 = 5 seconds. Cursor 20 / 200,000 = 0.0001 seconds, which is 0.1 ms.

Shifting pages. Orders are sorted newest first. The user reads page 1, items 1 to 20. Then 5 new orders arrive. Every old item moves down 5 places. The call offset=20 returns places 21 to 40, which are old items 16 to 35. Old items 16 to 20 appear twice: 5 repeats. A cursor "after old item 20" returns old items 21 to 40, with no repeat.

An export. A partner reads all 1,000,000 orders, 100 per page. That is 10,000 pages. Page k works through k x 100 rows with offset. Total = 100 x (1 + 2 + ... + 10,000) = 100 x 50,005,000 = 5,000,500,000 rows. With a cursor it is 1,000,000 rows. That is 5,000 times fewer. Time at our speed: offset 5,000,500,000 / 200,000 = about 25,000 seconds, which is 6.9 hours. Cursor: 5 seconds.

The test: worst-case rows = page number x page size. Assumption: under 10,000 rows, offset is cheap enough (10,000 / 200,000 = 50 ms).

A cursor query looks like this. The pair (created_at, id) is unique, so ties cannot cause repeats:

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

It needs an index on (created_at, id). An index is a sorted list that lets the database jump straight to the place. Without it, the database sorts all rows each time.

What breaks with each choice

• Offset: deep pages get slow, and pages shift when rows are added or removed. Fix: cap the depth (for example 500 pages), or move to a cursor.
• Cursor: you cannot jump to page 700. A total count needs a full count of rows, which costs about 5 seconds here. A cursor is wrong after the sort order changes. Fix: show "next" and "previous" only, or an estimated count. Treat the cursor as an opaque string, and return an error if it is invalid.
• Both: the sort order must be unique. The PostgreSQL manual warns that without a unique ORDER BY, you get an unpredictable subset. Always add the id as the last sort key.

What it costs

• Offset costs the least to build. A cursor needs a unique sort, a matching index and a token format. Assumption: 2 to 3 engineer-days.
• Changing later is hard once clients depend on page numbers. A public API cannot drop offset quickly. Assumption: run both styles for 6 months, then retire the old one.

How each level answers

SDE-1: Offset skips rows and returns the next ones. A cursor marks where the last page ended.

SDE-2: I use a cursor for feeds and orders, because new rows arrive and offset would repeat items. I use offset for a small admin table where people jump to a page.

Senior: The last page of 1,000,000 orders reads 1,000,000 rows with offset and 20 with a cursor. An export by offset reads about 5 billion rows, which is 5,000 times more. I sort by created time and id, and I add an index on both. The failure is a cursor that is broken, edited or made for a different sort order. I return a clear error, and the client restarts from the first page.

Staff: The paging style of a public API is a promise that lasts for years. I choose cursors for any list that can grow, and keep offset for small tables. The API team owns the rule. To migrate, I add cursor paging next to offset, mark offset as deprecated, and watch which clients still use it. I measure rows examined per request, p95 (the time within which 95 of 100 requests finish) time by page number, and tickets about repeated items.

Say it in the interview

"For a growing list like orders, I use a cursor on created time and id, with a matching index. Offset on page 50,000 reads 1,000,000 rows, while a cursor reads 20, and offset also repeats items when new orders arrive. I keep offset only for small admin tables where people jump to a page."

Common mistakes

• Using offset for an infinite feed. It is wrong because the cost grows with every page, and new rows cause repeats.
• Using a cursor on a column that is not unique, such as created time alone. It is wrong because rows with the same time can be skipped or repeated.
• Forgetting the index. A cursor without an index still sorts everything, so the speed gain disappears.

Sources

• PostgreSQL manual, LIMIT and OFFSET (skipped rows are still computed; use a unique ORDER BY): https://www.postgresql.org/docs/current/queries-limit.html (opens in a new tab) (checked 26 Oct 2026).
• The 1,000,000 orders, the page size, the speed of 200,000 rows per second and the effort estimates are assumptions made for teaching. The arithmetic is calculated from them. The row-value test in the query works in PostgreSQL. Check your own database.