<- all tokdocs

Offset vs Cursor Pagination: How OFFSET Really Works in PostgreSQL

By , founder of Mavric and Apso

View on TikTok ->

Offset pagination uses LIMIT and OFFSET to fetch a page of rows. Cursor pagination returns a pointer with each page that tells the client where to resume. A TikTok from @therealraymondjones gets the tradeoff right in broad terms, but says OFFSET "does kind of like a full scan," which is not how PostgreSQL describes it. The bigger problem is the query on screen, which has no ORDER BY, and PostgreSQL says that makes paginated results unreliable at any offset.

Does OFFSET cause a full table scan?

No. The video says the query will "retrieve all the records, limit it and then offset it," and its description says "Avoid full table scans."

PostgreSQL's documentation states the real cost: "The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient." If an index covers the WHERE filter and the sort key, the planner can walk the index and touch about OFFSET + LIMIT rows. In the video's example, LIMIT 50 OFFSET 100, that is about 150 rows, whether the table holds a thousand rows or a billion.

The order is also reversed in the video. The SELECT reference says that when both are given, "start rows are skipped before starting to count the count rows to be returned." The engine skips, then counts. It does not build the full result and slice it.

The intuition behind the claim is sound. Offset cost grows with the offset value, so page 1 is cheap and page 10,000 is not. MySQL documents one case close to the video's description: "if a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found." That happens when no index supports the sort, which is a missing-index problem rather than an OFFSET problem.

Why do LIMIT and OFFSET need ORDER BY?

The query on screen is SELECT * FROM TABLE WHERE <CONDITION> LIMIT 50 OFFSET 100. It has no ORDER BY, and the video never mentions ordering, sorting or indexes.

PostgreSQL's warning is direct: "When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows." It adds that the planner may choose different plans for different LIMIT and OFFSET values, so "using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY."

Without it, page 2 and page 3 can overlap or skip rows even on a table nobody is writing to. The order must also be unique. The video's example is a license plate reader feed with a captured_at timestamp, and two cameras can record in the same millisecond. Ordering by captured_at, observation_id breaks the ties.

Why use cursor pagination instead of offset?

The video justifies cursors by volume: there are "a lot a lot of records" and you do not want to return them all at once. LIMIT 50 already handles that.

The real difference is what happens between two requests. OFFSET 100 means skip 100 rows of the current result. If three new rows land ahead of the client's position between pages, every row shifts by three and the next page repeats three rows. Deletes cause the opposite, and three rows are never seen.

Cursors narrow that problem without removing it. Intercom's API docs say: "The starting_after context is stateless. Consequently, if items are updated between 2 paginated queries, this can lead to duplicate or missed records." A cursor is stable only for the sort key it encodes.

Is cursor pagination faster than offset?

The video's title card asks "Which is Faster?" and nothing in the video measures it. There is no query plan, timing or row count.

At OFFSET 100 on an indexed query, the difference is small. The gap opens at deep offsets, where the engine computes and discards many thousands of rows. On page one there is no difference.

Cursors have a cost too: no random access. Stripe's list API offers only limit, starting_after, ending_before and a has_more flag, with no page or offset parameter. A client cannot jump to page 40 without walking the 39 pages before it. Offset pagination gives you page jumps and total page counts, which is why both styles still exist.

How does a pagination cursor work?

The video describes it well: the server returns "the next thing that you need" and the client passes it back on the next request. The JSON on screen ends with a next_cursor field, Slack's exact name.

Slack's docs say "Cursors are like pointers," and that responses carry a response_metadata object with "a next_cursor when there are additional results to be retrieved." An empty or missing next_cursor means the list is done.

The GraphQL Cursor Connections Specification says cursors should be "considered opaque by the client," and defines PageInfo with hasNextPage, hasPreviousPage, startCursor and endCursor.

On the server, a cursor usually decodes into a keyset condition. PostgreSQL's row comparison handles this, comparing elements "left-to-right, stopping as soon as an unequal or null pair of elements is found":

WHERE (captured_at, observation_id) > ('2026-10-05T12:00:00Z', 'obs_829381')
ORDER BY captured_at, observation_id
LIMIT 50

With a matching index, each page becomes a single index seek with no skipped rows.

Is LIMIT ... OFFSET standard SQL?

The video hedges with "i believe we can offset," and the syntax is correct. PostgreSQL documents LIMIT { count | ALL } followed by OFFSET start. MySQL supports LIMIT row_count OFFSET offset "for compatibility with PostgreSQL," alongside its own LIMIT offset, row_count.

Bottom line

OFFSET does not scan the table, but its cost grows with depth, and without a unique ORDER BY it can return overlapping or missing rows on any page. Cursors handle feeds with constant inserts better and give up page jumps. Either way, order by a unique key and index it.

Sources

Published October 7, 2026. Based on a TikTok by @therealraymondjones. Full video notes (markdown)