Pointer Walk

Pagination: OFFSET or Keyset

OFFSET 1,000,000 reads a million rows before returning ten. Paging after the last id reads only the ten.

DatabasesIntermediate7 min read

Showing a long list in pages

A table has a million rows and the screen shows ten. So the app asks for ten at a time: page 1, page 2, page 3. This is pagination, and there are two ways to ask for "the next ten".

One of them gets slower with every page.

OFFSET: count from the start

The familiar way is to say how many rows to skip.

sql
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 0;      -- page 1
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 10;     -- page 2
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 1000;   -- page 101

It is easy to write and it lets a user jump straight to any page number. But think about what "skip 1,000 rows" asks of the database. It has no way to know where row 1,001 starts. It must read the first 1,000 rows, throw them away, and then return the next 10.

Keyset: continue from where you stopped

The other way is to remember the last row you showed and ask for what comes after it.

sql
SELECT * FROM posts ORDER BY id LIMIT 10;                    -- page 1: ends at id 10
SELECT * FROM posts WHERE id > 10 ORDER BY id LIMIT 10;      -- the next 10
SELECT * FROM posts WHERE id > 20 ORDER BY id LIMIT 10;      -- and the next

With an index on id, the database walks straight to the first row after the given id and reads ten rows. Nothing is skipped and nothing is thrown away. This is called keyset pagination, or cursor pagination.

Watch the gap open

Rows read to return one page of 10Interactive
OFFSETLIMIT 10 OFFSET 0

0 rows read

KeysetWHERE id > (last id seen) LIMIT 10

0 rows read

Pink: rows read and thrown away. Green: rows returned to you.

Start. A list of a million rows, shown 10 per page. Both methods are asked for the same pages, further and further in.

Page
–
OFFSET reads
0
Keyset reads
0

On page 1 they are identical, and that is the trap. Nobody tests page 100,000. Then a search crawler, or an export job, walks through every page, each one slower than the last, and the database spends its time reading rows that are immediately discarded.

Example

Walking the whole table

To export a million rows in pages of 10 using OFFSET, the database reads 10 rows for page 1, 20 for page 2, 30 for page 3, and so on. Added up over 100,000 pages, that is about 50 billion rows read to deliver one million.

With keyset pagination it reads one million.

The second problem with OFFSET: shifting rows

OFFSET counts positions, and positions change when data changes.

  1. You load page 1: the 10 newest posts

    Positions 1 to 10.

  2. Someone publishes a new post

    Everything moves down one place. The post you saw at position 10 is now at position 11.

  3. You load page 2: positions 11 to 20

    The first post on it is one you have already seen.

Deletions do the opposite and cause a row to be skipped. Keyset pagination is immune, because "after id 10" means the same thing whatever was added or removed.

What keyset costs

Good at

  • Every page costs the same, however deep
  • No duplicates or gaps when rows are added or removed while paging

Where it bites

  • No jumping to page 500: only next and previous
  • Needs a sort order that is unique, backed by an index
  • Slightly more complex when sorting by a column that can repeat

Sorting by a column with repeats, such as a date, needs a tie-breaker so the position is exact. Sort by both and compare both:

sql
SELECT * FROM posts
WHERE (created_at, id) < ('2026-03-01 10:00', 8141)
ORDER BY created_at DESC, id DESC
LIMIT 10;

Which one to use

SituationChoice
"Load more" buttons, infinite scroll, feeds, APIsKeyset
Exports and background jobs that walk a whole tableKeyset
A small list where users need page numbersOFFSET is fine
Page numbers on a large listOFFSET, with a limit on how deep people can go

Key takeaways

  • OFFSET makes the database read and discard every skipped row, so deep pages get slow.
  • Page 1 is fast either way, which hides the problem until real traffic arrives.
  • Keyset pagination asks for rows after the last one seen and reads only the page.
  • It also avoids duplicates and gaps when rows change between requests.
  • The price is that you can only move to the next or previous page.

Your progress

Check your progress

0%

  1. Read the page50% of this page

    When you have gone through the page and its animations, tick it off.

  2. Take the quiz50% of this page

    6 questions, from easy to hard. Take it as often as you like: the questions change, and only your best score counts.

Saved on this device only. See all your progress