Offset vs keyset pagination

Offset pagination asks the database for “rows 100,001 to 100,020”, and the database has to produce the first 100,000 rows to find them. Keyset pagination, also called the seek method, asks for “the 20 rows that come after this one” using the last row’s sort key, so an index can jump straight there. Offset is simple and gives you page numbers; keyset stays fast and stable at any depth.

Picture it first: the bookmark

A friend reads you a printed register of 2,000 cricket results over the phone, 20 at a time, newest first.

With the counting method, every call starts the same way: “skip the first 4,980 results, then read me 20.” She runs her finger down from the top and counts. Each call takes longer than the last. Worse, if a new match is written in at the top between calls, her count lands one line early and you hear a result twice.

With the bookmark method, you say: “read me the 20 results after India vs Australia on 12 March.” The register is sorted by date, so she flips straight to that line and reads on. Every call takes the same time, and a new match at the top changes nothing.

Map it back: counting is OFFSET, the bookmark is the cursor (the last row’s sort key), the sorted register is a B-tree index, and the new match is a concurrent insert.

How OFFSET really executes

Take a food delivery app’s order history, newest first, 20 per page:

SELECT id, created_at, total_paise
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;          -- page 5,001

The PostgreSQL manual says it plainly: the rows skipped by OFFSET still have to be computed inside the server. Even with an index on (created_at, id), the plan walks the index from the newest entry and hands every row to a Limit node, which discards the first 100,000. Here is PostgreSQL 18, on a laptop, with a 2-million-row table:

Limit (actual time=16.104..16.107 rows=20.00 loops=1)
  Buffers: shared hit=2310
  ->  Index Scan Backward using orders_created_id on orders (actual time=0.012..13.020 rows=100020.00 loops=1)
        Index Searches: 1
        Buffers: shared hit=2310
Execution Time: 16.128 ms

The tell is rows=100020.00 on the index scan, under a node that returns 20. The index can’t jump to entry 100,000 because a B-tree records keys, not positions: no page stores how many entries lie before it, so the only way to find the 100,000th is to count. Page p with n rows a page has an offset of (p − 1) × n and reads p × n rows. Here’s the same query at five depths, with a warm cache:

Work grows linearly with depth, and the last row jumps faster than linear: 10× the rows took 37× the time, because the 300 MB table no longer fitted in the 128 MB buffer cache and pages came from the operating system instead. Page 50,001 is about 45,000 times slower than page 1 (592 ÷ 0.013). This table was physically in created_at order, which is the best case; in a real table the skipped rows are scattered across more pages.

MySQL behaves no better. On MySQL 8.4, OFFSET 100,000 over a 200,000-row copy made the optimizer drop the index entirely and pick a full table scan plus filesort (639 ms), because reading 100,020 rows through a secondary index means a primary-key lookup for each.

Paging through everything is worse still: the total is quadratic. Exporting 2 million rows at 20 a page is 100,000 pages, and the database reads 20 × (1 + 2 + … + 100,000) = 20 × 100,000 × 100,001 ÷ 2 ≈ 100 billion rows, to deliver 2 million.

Who visits page 5,001? Rarely a person. GitLab’s pagination guide names the real culprits: API scripts and scrapers that walk every page, and old links opened after the earlier pages have left the cache.

Rows shift between pages

OFFSET addresses rows by position, and positions move whenever data changes. Picture a restaurant’s order dashboard at dinner rush, three orders a page for simplicity:

• t1: page 1 (OFFSET 0) returns O9, O8 and O7.
• Then a new order, O10, arrives at the top. Every older row slides down one place.
• t2: page 2 (OFFSET 3) skips O10, O9 and O8, and returns O7, O6 and O5. O7 appears twice.

A delete does the opposite. If O8 is cancelled and removed between the two requests, every row slides up, OFFSET 3 skips O9, O7 and O6, and O6 is never shown. On a busy list this happens on almost every page turn, and on a feed it shows up as the same post twice in an infinite scroll.

There is a quieter version of the same bug with no writes at all. If the ORDER BY isn’t unique, ties come back in whatever order the plan produces, and the PostgreSQL manual warns that different LIMIT and OFFSET values can pick different plans. A row on a page boundary can then show up twice or not at all. Always end the ORDER BY with a unique column.

Keyset fixes the shifting because its cursor is a value, not a position. A new O10 sorts above the cursor and can’t affect “rows after O7”; deleting O8 doesn’t matter either. But keyset isn’t a snapshot. A row inserted below the cursor (a back-dated entry, a late-arriving event) will show up on a later page, and a row whose sort key changes can cross the cursor and be shown twice or skipped. If you need a frozen view, add an upper bound captured on the first page (AND created_at <= $3, where $3 is the newest timestamp the first page saw). For a one-off export, one transaction at repeatable read gives you a true snapshot, but never hold that open across a user’s clicks.

Keyset pagination: seek, don’t skip

The first page is the plain query with no offset. Every later page passes in the sort key of the last row it showed:

-- next page: $1, $2 = created_at and id of the last row shown
SELECT id, created_at, total_paise
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

(created_at, id) < ($1, $2) is a row-value comparison. The PostgreSQL manual defines it the way a dictionary sorts words: compare the first elements, and only if they’re equal move on to the next. So it means “an earlier created_at, or the same created_at and a smaller id”, which is exactly “comes after this row in the DESC, DESC order”. With the cursor 100,020 rows deep, PostgreSQL 18 turns it into a seek:

Limit (actual time=0.015..0.018 rows=20.00 loops=1)
  Buffers: shared hit=3 read=1
  ->  Index Scan Backward using orders_created_id on orders (actual time=0.014..0.016 rows=20.00 loops=1)
        Index Cond: (ROW(created_at, id) < ROW('2026-11-27 07:05:23+05:30'::timestamp with time zone, 1899981))
        Index Searches: 1
        Buffers: shared hit=3 read=1
Execution Time: 0.053 ms

Four buffers: a short walk down the index plus the table page holding these 20 rows. The same four on page 2 or page 50,001.

To tell the client whether there is a next page without counting, ask for LIMIT 21. If 21 rows come back, show 20, take the 20th as the cursor, and set has_more. Stripe’s list API exposes exactly that flag instead of a total.

The tie-breaker must be unique

Suppose three orders share created_at = 19:30:00 and the page boundary falls between them: one is on this page, two are not. created_at < '19:30:00' skips the two you haven’t shown; created_at <= '19:30:00' repeats the one you have. Neither is right, because created_at alone doesn’t give every row its own position.

Adding a unique column as the last sort key makes the order total: every row has exactly one place, so “after this row” is always well defined. The primary key is the usual choice. Any unique, immutable column works, including a UUIDv7.

The index that makes it a seek

The predicate is only fast if an index is sorted the same way: here (created_at, id). A B-tree can be read backwards, so one ascending index serves DESC, DESC too. In InnoDB every secondary index already ends with the primary key, so an index on created_at alone is effectively (created_at, id). Column order rules (equality filters first, then the sort columns) are in composite index design, and the B-tree itself is in database indexes.

Batch jobs walk tables the same way

Keyset isn’t only for screens. A backfill or nightly export that walks a big table in chunks should page by key too: WHERE id > $1 ORDER BY id LIMIT 1000, remembering the largest id of each batch. An OFFSET loop has the quadratic total from earlier, and if the job deletes or moves the rows it has processed, every batch slides the remaining rows up past its window and silently skips a batch’s worth. Keyset batches are also resumable: store the last id, and restart from it after a crash (online schema and data migrations).

Writing the seek predicate

The row-value form is the clearest, but engines don’t treat equivalent spellings equally. Here is the same cursor 100,000 rows deep, three ways, on PostgreSQL 18 and MySQL 8.4:

PostgreSQL understands row comparisons natively and passes them to the B-tree as a single bound. The expanded OR form, the one many ORMs and hand-written cursors produce, is not an index condition at all: the plan reported Rows Removed by Filter: 100020 and took 20 ms instead of 0.02 ms. The fix is the redundant leading bound in the third row, which gives the index a place to start.

MySQL is the mirror image. Its manual documents range access for row constructors only for IN() lists, and says that a row constructor which doesn’t cover an index prefix uses only part of the index; its own example c1 = 1 AND (c2, c3) > (1, 1) uses only c1, and rewriting it as c2 > 1 OR (c2 = 1 AND c3 > 1) uses all three columns. On 8.4, even (created_at, id) < (…) on its own produced type: index and a filter. The expanded form gets a proper range scan; key_len 13 is the 5-byte DATETIME plus the 8-byte BIGINT primary key that InnoDB appends.

The bounded expanded form (third row) seeks on both engines. Whatever you write, run EXPLAIN and check that the cursor shows up as an index condition or a range, not a filter (query execution plans).

Mixed sort directions

A “biggest orders first” list sorts total_paise DESC, id ASC. The row form can’t express that, because both elements of a row comparison use the same operator. Worse, the wrong version compiles, uses the index and runs fast: (total_paise, id) < ($1, $2) repeats ties with a smaller id and skips ties with a larger one. Three correct options:

• Write the expanded form with one operator per column, total_paise <= $1 AND (total_paise < $1 OR (total_paise = $1 AND id > $2)), over an index declared (total_paise DESC, id ASC). The PostgreSQL manual notes that a plain two-column index can’t produce mixed order; engine details are in composite index design.
• Negate a numeric column so both directions agree: index ((-total_paise), id) and seek with WHERE (-total_paise, id) > (-$1, $2) ORDER BY -total_paise, id. PostgreSQL uses it as a row Index Cond.
• Change the tie-breaker’s direction to match (DESC, DESC) if nobody cares which of two equal totals comes first. Usually nobody does.

Going backwards

A “previous” button needs the rows just before the first row on screen. Flip both the comparison and the sort, take 20, then reverse them in the application:

-- previous page: $1, $2 = created_at and id of the FIRST row shown
SELECT id, created_at, total_paise
FROM orders
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC
LIMIT 20;                       -- then reverse the 20 rows in the app

Without the flipped ORDER BY, the database would return the 20 newest rows above the cursor rather than the 20 nearest to it. In practice each response carries two cursors, built from its first and last rows, so the client can go either way; on the first page the previous cursor is simply absent. The same index serves both directions. Jumping to the last page works the same way: sort ascending with no cursor, take 20, reverse. Stripe’s ending_before and starting_after parameters are exactly this pair: “the page before this object” and “the page after it”.

Filters, nulls and other traps

Filters lead the index. A restaurant’s own order list adds restaurant_id = 42. Put the equality filter first and the sort columns after it: (restaurant_id, created_at, id). PostgreSQL then seeks on both at once, Index Cond: ((restaurant_id = 42) AND (ROW(created_at, id) < ROW(…))), and reads one restaurant’s 20 rows. A filter that is not in the index is checked row by row as the scan walks: if 5% of orders are CANCELLED, a page of cancelled orders costs about 20 ÷ 0.05 = 400 entries, and a filter that matches nothing walks to the end of the index to prove it. Each sort option and filter shape you offer needs its own index, so offer few; a partial index helps for one hot status.

Nulls make rows vanish. Under SQL’s rules a comparison with NULL is unknown, not true, so rows with a NULL sort key never satisfy (delivered_at, id) < (…). If the cursor row itself has a NULL, the next page comes back empty. Options, best first: make the column NOT NULL (with a sentinel if needed); page through the non-null rows, then switch to a second phase, WHERE delivered_at IS NULL AND id < $2; or sort on an expression such as COALESCE(delivered_at, '-infinity') with a matching expression index. GitLab’s guide shows the extra OR … IS NULL branches its library generates, and warns how quickly the complexity grows.

Mutable keys move rows. Paging a feed by score or updated_at means a row can jump across the cursor between requests and appear twice or never. That’s often acceptable for a feed (de-duplicate by id on the client). It isn’t for exports or sync jobs, which should page by an immutable key such as id.

Cursor values must survive the round trip. PostgreSQL timestamps have microsecond resolution. If the cursor passes through a JavaScript Date or any format that keeps only milliseconds, 19:37:05.123456 comes back as 19:37:05.123, and the next page skips or repeats the rows in between. Carry the exact value (a string, or an integer of microseconds), or put the tie-breaker id to work by sorting on it alone where you can.

Sorting by another table’s column needs a copy. Orders sorted by restaurant name need the name in the cursor and in the index, but an index can’t span two tables, so the database usually joins and sorts every candidate row before it can apply the cursor and LIMIT. Copy the sort column onto the table you page through, so one index covers the filter, the sort and the tie-breaker.

Page numbers and total counts

Keyset can’t jump to page 37. A row’s position isn’t stored anywhere; finding where page 37 starts means counting 720 rows, which is OFFSET again. Keyset gives you next, previous, first and last. Two observations soften this. Infinite scroll and “Load more” never show page numbers anyway. And keyset can jump to a value: “orders before 1 March” is just a seek with the cursor ('2026-03-01', 0), which is often more useful than “page 37” ever was.

Exact totals are expensive. Under MVCC, PostgreSQL can’t keep a single row count, because each transaction may see a different set of rows; count(*) has to check visibility row by row. On the 2-million-row table it took 59 ms using two parallel workers on a warm cache, and the cost grows with the table: about 100 times that at 200 million rows, on every page view, before any disk reads. The alternatives:

• Estimate. SELECT reltuples::bigint FROM pg_class WHERE oid = 'orders'::regclass returns the planner’s figure, refreshed by VACUUM, ANALYZE and CREATE INDEX. It said 2,000,001 for 2,000,000 rows. For a filtered list, read the row estimate from EXPLAIN. Show “about 2 million”.
• Cap the count. SELECT count(*) FROM (SELECT 1 FROM orders WHERE restaurant_id = 42 LIMIT 1001) t costs at most 1,001 index entries. GitLab’s guidance is exactly this: show “1000+” when it returns 1,001.
• Maintain a counter per restaurant, updated in the same transaction or asynchronously. Exact and cheap to read, but the counter row becomes a hot spot under heavy writes.
• Use a search engine for deep faceted browsing with counts per filter (how search engines work). Even there, deep offsets are capped: Elasticsearch refuses from + size beyond 10,000 hits by default and points you to search_after, its keyset.

When you must keep OFFSET

Sometimes you can’t switch yet: a public API promises ?page=N, or an admin screen needs page numbers. Three moves limit the damage:

1. Make the order unique and indexed, so the database walks an index instead of sorting the whole table.
2. Cap the depth. Reject offsets past, say, 10,000, as Elasticsearch does by default, and offer filters or a date jump instead.
3. Use a deferred join (also called a late row lookup). Find the 20 ids using only the index, then fetch just those 20 rows:

SELECT o.id, o.created_at, o.total_paise, o.status
FROM orders o
JOIN (
  SELECT id FROM orders
  ORDER BY created_at DESC, id DESC
  LIMIT 20 OFFSET 1000000
) page ON page.id = o.id
ORDER BY o.created_at DESC, o.id DESC;

The inner query needs only created_at and id, so it can be an index-only scan: it skips a million small index entries instead of a million table rows. On PostgreSQL 18 that cut the OFFSET 1,000,000 query from 592 ms and 23,062 buffers to 66 ms and 3,917 buffers. On MySQL 8.4 the OFFSET 100,000 query fell from 639 ms to 38 ms, as a covering scan of the secondary index plus 20 primary-key lookups. GitLab ships the same trick, written as a CTE plus a LATERAL lookup, as a stop-gap for deep page= requests.

Be honest about what it buys: each skipped row is cheaper, but the cost is still linear in depth. It also needs a current visibility map in PostgreSQL (index-only scans), which vacuum maintains.

Pagination across shards

On a sharded table, a list that isn’t filtered by the shard key needs rows from every shard. Keyset handles this well because its cursor is a global position: “after (19:31:44, 4410)” means the same thing on every shard. The coordinator sends the same seek to each shard, each returns at most 20 rows, and the coordinator merge-sorts them, keeps 20 and sends the last one back as the next cursor. Each page costs shards × 20 rows, however deep you are, though every page still waits for the slowest shard.

Two details matter. The tie-breaker must be unique across shards, so per-shard auto-increment ids won’t do; use globally unique IDs. And if the list is filtered by the shard key (one restaurant’s orders), route to that one shard and skip the merge.

Offset across shards is much worse than offset on one machine. No shard knows how many of its rows come before global position 10,000, so each must send its top 10,020 rows to the coordinator. With 16 shards, page 501 ships 16 × 10,020 = 160,320 rows to show 20, against 16 × 20 = 320 for keyset. Elasticsearch’s documentation describes exactly this: every shard loads the requested hits plus all previous pages into memory, which is why deep from is capped.

Choosing an approach

A workable default: use keyset for any list that grows without bound (order history, feeds, audit logs, API list endpoints, exports) and keep OFFSET where the list is small or capped, such as a settings table or search results nobody reads past page 10. A hybrid is common too: numbered links for the first few pages, then “Load more” with a cursor beyond them.

Large public APIs settled on cursors long ago. Stripe’s list endpoints take an object ID in starting_after or ending_before, return between 1 and 100 objects, and report has_more rather than a total. GitHub’s REST API returns a link header whose URLs use page, before/after or since, depending on the endpoint, and leaves out the last link when it can’t be calculated. GitLab uses keyset in both its REST and GraphQL APIs. How to encode cursors and design the API contract is in pagination at the API level.

Cheat sheet

How to say it in the interview

“For the order history I’d use keyset pagination. OFFSET makes the database produce and throw away every skipped row, so page 5,000 reads 100,000 rows, and inserts between requests cause duplicates. Instead the client sends the last row’s (created_at, id) and I query WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 21, with an index on (user_id, created_at, id). That’s one index seek and 21 entries at any depth; the 21st row tells me there’s a next page. The id breaks ties so no row is skipped. I give up page numbers and exact totals: the UI gets ‘Load more’ and a ‘1,000+’ badge.”

Why is OFFSET slow if there’s an index? The index finds the start of the order, not position 100,000. Nothing in a B-tree records how many entries precede a key, so the scan has to step through every entry up to the offset.

How do you give users page numbers with keyset? You mostly don’t. Offer next/previous, infinite scroll, or a jump to a value such as a date. If page numbers are a hard requirement, keep OFFSET with a capped depth and a deferred join, and accept the drift.

What if the sort column isn’t unique? Rows that share the boundary value get skipped with < or repeated with <=. Add the primary key as the last sort column and in the predicate, so every row has exactly one position.

How do you paginate a feed ranked by score? Score changes, so rows cross the cursor. For a feed, accept it and de-duplicate by id on the client, or freeze the ranked list for the session and page through that snapshot by rank. For exports, page by an immutable key.

How does this work on a sharded table? Send the same cursor to every shard, take n rows from each, merge-sort, keep n, and use the last as the next cursor: S × n rows per page at any depth. Offset would need S × (offset + n).

Where to go next

• Pagination: offset vs cursor: the API contract, opaque cursors and page tokens built on top of this.
• Composite index design: column order, satisfying ORDER BY and mixed-direction indexes.
• Database indexes: the B+tree that makes a seek cost a few page reads.
• Query execution plans: reading rows=, Index Cond and Rows Removed by Filter.
• Index scan vs full table scan: index-only scans, the visibility map and how LIMIT changes the plan.
• Database sharding: scatter-gather queries and the shard key that lets you avoid them.
• Design a news feed: cursor pagination over a ranked, constantly changing list.

References and further reading

1. PostgreSQL documentation: LIMIT and OFFSETpostgresql.orgRows skipped by OFFSET are still computed inside the server; LIMIT needs an ORDER BY that gives a unique order.
2. PostgreSQL documentation: Row constructor comparisonpostgresql.orgLeft-to-right semantics of (a, b) < (x, y) and how NULL elements make the comparison unknown.
3. PostgreSQL documentation: Indexes and ORDER BYpostgresql.orgORDER BY with LIMIT served straight from a B-tree, backward scans, and indexes declared (x ASC, y DESC) for mixed directions.
4. MySQL 8.0 Reference Manual: Row Constructor Expression Optimizationdev.mysql.comRow constructors that don’t cover an index prefix use only part of the index; the rewritten OR form gets a full range scan.
5. GitLab development docs: Pagination guidelinesdocs.gitlab.comOffset reads PAGE × PAGE_SIZE rows, capped “1000+” counts, keyset without page numbers, and nullable sort columns.
6. GitLab development docs: Offset pagination optimizationdocs.gitlab.comThe index-only-scan (deferred join) stop-gap for deep OFFSET queries, and why it still scales linearly.
7. PostgreSQL wiki: Count estimatewiki.postgresql.orgWhy count(*) must check row visibility under MVCC, and estimating counts from pg_class.reltuples or EXPLAIN.
8. Elasticsearch documentation: Paginate search resultselastic.coDeep from + size makes every shard load all previous pages; the 10,000-hit default limit and search_after.
9. Stripe API reference: Paginationdocs.stripe.comCursor pagination with starting_after and ending_before, a limit of 1–100, and has_more instead of a total.
10. GitHub REST API: Using paginationdocs.github.comThe link header and endpoints that page with page, before/after or since.