Database Pagination: Cursor vs. OFFSET in Depth

One afternoon, the monitoring channel erupted: one endpoint's P99 latency had soared to eight seconds. The investigation found no slow SQL query and no missing index, just an ordinary list endpoint: SELECT ... ORDER BY id LIMIT 20 OFFSET 100000. Frontend telemetry showed that a user, very likely a crawler, had jumped directly to page 5,000.

This is the classic OFFSET pagination trap. It seems perfectly natural, but at scale it becomes a hidden performance bomb. This article explains why OFFSET is slow, why cursor pagination is fast, how to design cursors, and when OFFSET is unavoidable.

1. The OFFSET Performance Trap: It Really Scans the First N Rows

Start with the simplest pagination query:

-- Page 5,000, 20 rows per page
SELECT id, title, created_at
FROM articles
ORDER BY id
LIMIT 20 OFFSET 100000;

Many people assume OFFSET 100000 means "jump to row 100000" and that the database can somehow locate it instantly. In reality, it dutifully scans from the beginning, reads 100,000 rows, discards them, and then returns the next 20 rows.

The execution process is roughly:

1. Start at the first row of the id index or the full table
2. Read, evaluate, and count rows one by one
3. Read and discard the first 100,000 rows
4. Finally return rows 100,001 through 100,020

In other words, the actual cost of OFFSET N LIMIT M is roughly scanning N+M rows, not M rows. The larger N becomes, the slower the query gets.

The performance curve looks roughly like this:

This is an O(N) scan cost, where N is the OFFSET value. Deep pagination is nearly equivalent to a full-table scan.

The deeper problem is I/O. The rows scanned for OFFSET are not free: they must be read either from the index or from the underlying table. Even if they are ultimately discarded, the intermediate random I/O has already occurred. When everything is cached in memory, this is "only" wasted CPU. Once the working set exceeds innodb_buffer_pool_size, it turns into disk I/O and performance falls off a cliff.

In EXPLAIN, the rows column will show a large number, and Extra may contain Using filesort. That is the database telling you it did a lot of work.

2. Cursor Pagination (Keyset Pagination): O(1) Positioning

Cursor pagination takes a completely different approach. Instead of telling the database "how many rows to skip," tell it "where to start."

-- First page
SELECT id, title, created_at
FROM articles
ORDER BY id
LIMIT 21;   -- Fetch one extra row to determine whether another page exists

-- Second and subsequent pages (assuming the last row on the previous page has id = 12345)
SELECT id, title, created_at
FROM articles
WHERE id > 12345
ORDER BY id
LIMIT 21;

Here, 12345 is the cursor. It is essentially the sort-key value of the last record on the previous page.

Why is this fast? Because WHERE id > 12345 can use an index range scan directly. In the B+Tree, the database locates the position of id = 12345, scans the next 21 entries sequentially, and stops. It never touches the preceding 12,345 rows.

The cost depends only on the number of returned rows, or the page size, and not on OFFSET. No matter how deep you paginate, latency remains nearly constant: O(page size), approximately O(1).

For the same amount of data, the latency curve for cursor pagination looks like this:

That is an order-of-magnitude difference. OFFSET degrades linearly, while cursor performance remains constant.

3. Why Cursors Are So Fast: Direct Index Positioning

Let us look more closely at why cursors can achieve O(1)-like performance.

The id column usually has a primary-key index, often a clustered index. Searching a B+Tree costs O(log N), followed by an O(M) sequential scan of leaf nodes, where M is the number of returned rows. The complete query therefore costs O(log N) + O(M).

Execution of WHERE id > 12345 on a B+Tree:

1. Traverse downward from the root and use binary search to locate id = 12345, or the next larger value: O(log N), requiring roughly three or four comparisons even across tens of millions of rows
2. Scan forward through the leaf-node linked list and fetch 21 rows: O(M)
3. Return the results

By contrast, OFFSET 100000 does this:

1. Locate the first row from the root: O(log N)
2. Scan 100,000 rows through the leaf-node linked list, discarding them as it goes: O(N)
3. Scan and return another 20 rows: O(M)

The entire difference is in step 2. A cursor replaces "scan the first 100,000 rows" with "perform one B+Tree lookup." That is the fundamental improvement.

There is one prerequisite: the columns used by the WHERE clause must be indexed, and the sort direction must match the index order. Without an index on id, WHERE id > 12345 also degrades into a full-table scan. Cursors are not magic; they depend on indexes.

4. The Core Limitation of Cursors: No Page Jumps

There is no free lunch. The cost of cursor pagination is losing random-access capability.

Cursors are sequential: you must know the last record on the previous page before fetching the next page. A cursor does not know where "page 50" is because page 50 is not a meaningful concept. There are only "the 20 rows after cursor X."

Specific limitations:

1. Only "previous page/next page" navigation is available; you cannot jump directly to page 50
2. You cannot display "N pages total / page X" because page numbers do not exist
3. Page-jump UIs are unsupported, such as a pagination bar showing 1 2 3 ... 50 51 52
4. You cannot share a link to a specific page because a cursor is an opaque value and may expire

This means that if your product uses a traditional pagination bar where users can click "page 7," cursor pagination will not work without changing the frontend's page-number model.

Good use cases for cursors:

• Infinite scrolling for feeds, timelines, and chat history
• "Load more" buttons
• Exports and batch processing that continuously paginate to the end
• Mobile lists

Poor use cases:

• Admin tables where operators need to jump to page 87 to inspect a record
• Search results where users expect clickable page numbers
• Anywhere that must display "N pages total"

5. Cursor Design: Single Columns, Multiple Columns, and Row-Value Comparisons

The key requirement of cursor design is: the cursor value must uniquely identify a position in the sort order.

Single-Column Cursors (The Simplest Case)

The sort key is unique, such as an auto-incrementing primary key id or a UUID:

-- ORDER BY id
WHERE id > :last_id

Multi-Column Cursors (More Common)

Real-world queries often sort by more than one column, such as ORDER BY created_at DESC, id DESC. Why is created_at alone insufficient? Because timestamp precision can produce multiple records within the same second. Using only created_at can omit or duplicate data at page boundaries.

The correct approach is a row-value comparison:

-- Last row on the previous page: created_at = '2026-07-17 10:00:00', id = 12345
-- Descending order, so use "less than"
WHERE (created_at, id) < ('2026-07-17 10:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 21;

This is equivalent to:

WHERE created_at < '2026-07-17 10:00:00'
   OR (created_at = '2026-07-17 10:00:00' AND id < 12345)

The row-value form is more concise, and PostgreSQL, MySQL 8.0+, and SQLite all support it. Note that MySQL 5.7 and earlier do not optimize row-value comparisons well and may fail to use the index. Either upgrade or expand the condition into the OR form.

Design Guidelines

1. Sort keys must be stable and comparable. Numbers, timestamps, and strings are fine, but do not mix them with NULL values
2. Include a unique column, usually id, as a tiebreaker; otherwise records can be omitted or duplicated at page boundaries
3. Encode cursor values: normally, encode (created_at, id) with Base64 and send it to the frontend, which returns it unchanged. Never let the frontend construct SQL because that creates a SQL injection risk
4. Handle cursor expiration: if the record referenced by a cursor is deleted, WHERE id > X still works because it is a range comparison rather than an equality check. If new data is inserted in the middle, however, the cursor position can "drift." This is an inherent property of cursor pagination

6. Sort Direction: Ascending, Descending, and Bidirectional Cursors

Descending Order (Timeline Scenarios)

Most lists use descending order, with the newest records first:

WHERE id < :last_id        -- Single-column descending order
WHERE (created_at, id) < (:last_created, :last_id)  -- Multi-column descending order

The cursor uses < because values decrease as you move forward through the list.

Bidirectional Cursors (Both Previous and Next Pages)

Consider chat history: users can scroll upward for older messages and downward to return to newer ones. This requires two cursors: before for older records and after for newer records.

-- Scroll upward (older messages)
WHERE id < :before_cursor ORDER BY id DESC LIMIT 20;

-- Scroll downward (newer messages)
WHERE id > :after_cursor ORDER BY id ASC LIMIT 20;

The frontend must maintain these two cursors separately. A typical API design is:

GET /messages?before=xxx   → returns { data, has_more_before, next_before }
GET /messages?after=yyy    → returns { data, has_more_after, next_after }

This is also the standard approach used by chat products such as Slack and Discord.

7. When OFFSET Is Unavoidable: Page Jumps and Totals

Cursor pagination has many advantages, but OFFSET cannot be replaced in some scenarios.

1. Page Jumps Are Required

A user clicks "page 50" in a pagination bar. You do not know the cursor corresponding to page 50, so the only option is OFFSET (50-1)*20 LIMIT 20.

For this kind of interactive page jumping, especially in admin systems and reports, OFFSET is the only choice.

2. "N Pages Total / N Records Total" Is Required

Displaying 320 pages total requires the total count, which introduces another problem: SELECT COUNT(*) is also slow on large tables.

Practical engineering compromises include:

• Admin interfaces: accept the cost of OFFSET + COUNT because the user base is small
• Consumer-facing interfaces: do not display a total; show only "more available" or an approximation such as about 1000+ records
• Maintain an approximate total in Redis or another cache and update it asynchronously

3. Stable Pagination Is Required

OFFSET has one property that cursors do not: when the data does not change, pages returned by OFFSET are stable and reproducible. Cursors can drift when rows are inserted or deleted, while OFFSET does not, provided the dataset remains unchanged.

This can be essential for testing, reconciliation, and report exports.

8. Hybrid Strategies: The Best of Both Worlds

Production systems rarely use only cursors or only OFFSET. A hybrid is more common.

Strategy A: Use OFFSET for the First Few Pages, Then Switch to Cursors

User behavior follows a common pattern: 95% of visits are concentrated in the first five pages. Only a small minority paginate deeply, and those clients are often crawlers.

You can therefore use OFFSET for the first N pages, where the offset remains small and fast, then force users to switch to "load more" with cursors or enforce a maximum OFFSET.

-- Pages 1-10: OFFSET
LIMIT 20 OFFSET 180;   -- Still acceptable

-- After page 10: reject page jumps and direct clients to use a cursor
-- Or return 400: "Use the cursor parameter"

Many large platforms, including Zhihu, Juejin, and Google Search, do exactly this: search results can be paginated through at most 10 to 20 pages, after which further navigation is unavailable.

Strategy B: Infinite Scrolling + Cursors

For feeds, masonry layouts, and chat history, where page jumps are unnecessary by nature, use cursors everywhere. The UI has no page numbers, only "load more," which also matches the user's mental model.

This is the ideal use case for cursors.

Strategy C: ID Cursors + LIMIT as a Fallback

For endpoints where page-jump requirements are uncertain, support both offset and cursor parameters:

GET /articles?offset=0&limit=20          # OFFSET mode
GET /articles?cursor=eyJpZCI6MTIzNDV9&limit=20  # Cursor mode

Let the frontend choose based on the scenario. This is a pragmatic engineering compromise: optimize for usability, not purity.

9. Measured Performance Comparison

Here is a real-world reference point using a local PostgreSQL instance with an articles table containing five million rows and a primary key on id:

Cursor latency forms a flat line, while OFFSET produces an upward-sloping line. Once OFFSET becomes large enough, the endpoint is effectively unusable.

EXPLAIN also reveals the difference. The estimated rows value for an OFFSET query is OFFSET + LIMIT; for a cursor query, it is LIMIT.

Summary

1. OFFSET is not free: it must scan and discard the first N rows before returning M rows. Deep pagination is an O(N) scan and approaches a full-table scan
2. Cursor pagination is O(1): WHERE id > last_id directly uses an index range scan, keeping latency constant regardless of pagination depth
3. Cursors depend on indexes: the sort key must be indexed or performance will still degrade. Multi-column sorting requires a row-value comparison such as (a, b) > (x, y) and a unique tiebreaker column
4. Cursors cannot jump between pages: they support only forward and backward navigation, cannot jump directly to page N, and cannot display "N pages total." Timelines use < cursors, while bidirectional pagination requires two cursors
5. A hybrid strategy is usually the most practical: use OFFSET for the first N pages and switch to cursors for deeper pagination; use cursors throughout feeds and chats; retain OFFSET when page jumps and totals are required

In one sentence: OFFSET means "skip N rows," while a cursor means "start at X." The former charges by row and becomes more expensive with every page; the latter uses an index and costs roughly the same everywhere. Avoid OFFSET whenever possible, unless you genuinely need page jumps.