Pagination

Offset against keyset: cost at depth, and what happens as rows arrive.

On the roadmap:Intermediate · step 1: Faster queries

Starterabout 10 to 15 min7 experiments

Experiments

New here? A few short steps show what happens, one at a time.

Settings

How the next page is found

SELECT … ORDER BY created DESC LIMIT 20 OFFSET 2000

Rows in the table10 million

Rows per page20

New rows arriving per page read0

Rows sharing one timestampall different

Rows read for page 1,000

20,000

Rows seen twice

Rows never shown

Pages read in the test

Each page reads every row before it: page 1,000 reads 20,000 rows to return 20. Raise the rows or add arriving rows to see more.

Rows the database reads to produce one page

rows read (each step is ten times more)

OffsetKeyset

For 10 million rows, 20 per page. An offset has to read past every skipped row, even with an index, so deep pages get slower and slower. A keyset seeks straight to its place in the index.

In plain words

Pagination returns a long list a page at a time. The obvious way, skip a number of rows and take the next few, gets slower with every page and misbehaves when the list changes while you read it. Keyset pagination starts after the last row you saw, and avoids both problems.

Why it matters

Every feed, search and API has a next page. Offsets are easy to write and are the first thing most people reach for, but they hide a cost that grows with depth and a correctness problem that grows with traffic. Here you can read the same list both ways and see exactly when each one breaks.

How real systems do it

The simulation is a simplified model. Here is what production systems add or do.

A database cannot skip to row N of an ordered result without counting past the earlier rows, so OFFSET 100000 reads and discards 100,000 rows, even with an index.
Stripe, Slack and GitHub's GraphQL API return opaque cursors, and you ask for the page after the cursor. Stripe's list endpoints use starting_after and ending_before for the same reason.
A cursor encodes the values of the sort key of the last row, for example the time and the ID. It is usually opaque to the client, so the server can change what it holds.
The trade-off is that you cannot jump straight to page 500, and a total count is a separate, expensive query. For feeds and exports, going forward is what people do, so keyset fits.