Keyset pagination
On the roadmap:Intermediate · step 1: Faster queries
Compare offset paging with 'after this key' paging on a list that changes between requests.
In plain words
Paging by 'give me the rows after this one' instead of 'skip the first 40'. The next page starts exactly where the last one ended, even if new rows arrive in between.
Why it matters
Offset paging shows duplicates or skips rows when data changes, and gets slower the deeper you go. Keyset paging stays stable and fast because the database jumps straight to the place in the index.
Where you meet it
- Feeds and timelines
- Large APIs that hand out cursor tokens
- Admin tables over millions of rows
You will be able to
- Show how an insert makes offset paging repeat a row
- Page by a key, with a tie-breaker
- Say why a cursor is better than a page number for live data
In interviews
Expected whenever you design a feed, a list API or infinite scroll.
Practise on the Interview Ready roadmap1Learn the idea first
2See it work before you read the code
3Build it and run the check
What you build
- An ordered list of rows with ids
- offsetPage(): skip N rows, which drifts when rows are inserted
- keysetPage(): continue after the last seen key, which stays stable
- A demonstration of a duplicate caused by an insert
export type Row = { id: number; title: string };
/** Rows are kept newest first (highest id first), like a feed. */
export class Feed {
rows: Row[] = [];
add(title: string): Row {
const row = { id: (this.rows[0]?.id ?? 0) + 1, title };
this.rows.unshift(row);
return row;
}
/** OFFSET paging: skip page * size rows. */
offsetPage(page: number, size: number): Row[] {
return this.rows.slice(page * size, page * size + size);
}
/** Keyset paging: rows strictly after the last id the client has seen. */
keysetPage(afterId: number | null, size: number): Row[] {
const start = afterId === null ? 0 : this.rows.findIndex((r) => r.id < afterId);
return start < 0 ? [] : this.rows.slice(start, start + size);
}
}
Rather run it on your own computer?
Save the code as index.ts and the check as check.ts in one folder. The check exits with an error if anything is wrong and prints keyset-pagination ok when everything passes. You need Node.js installed.
mkdir keyset-pagination && cd keyset-pagination
# save index.ts and check.ts in this folder
npx tsx check.tsHow real systems do it
This lab is the core idea in a small form. Here is what production versions add or do differently.
- Real APIs hand out an opaque cursor token that wraps the last row's sort values. Clients treat it as a bookmark, never as a page number.
- The sort needs a tie-breaker, usually (created_at, id), and a database index on exactly those columns so each page is an index seek.
- The trade-off: you cannot jump to page 50. Feeds and infinite scroll do not need to.
Ways to page through results
| How it works | Can you jump to page N? | Behaviour on live data | Pick it when | |
|---|---|---|---|---|
| Keyset (cursor) | Continue after the last key you saw | No, only next and previous | Stable and fast with an index | Feeds, infinite scroll and APIs |
| OFFSET and LIMIT | Skip a number of rows | Yes | Can repeat or skip rows; slower the deeper you go | Small, stable lists and admin tables |
| Snapshot or scroll | Freeze the results at the start | Depends | Consistent, but costs memory and expires | Exports and reports |
| Load all, page in the app | Fetch everything once | Yes | Consistent until you refresh | Very small data sets |
What it cannot do
You cannot jump to page 50
Keyset only knows 'after this key'. That suits feeds, not a numbered page list.
It needs a matching index
The sort columns, with the tie-breaker, should be indexed or each page is slow.
The sort needs a unique key
If two rows share a sort value, some may be skipped or repeated unless an id breaks the tie.
Changing the sort invalidates a cursor
A cursor only means something for the ordering it came from.
4Now extend it
Each one makes your version closer to what real systems do. Pick one and make the check cover it.
- Support descending order and tie-breaking on (created_at, id)
- Return an opaque cursor token instead of a raw id
- Write the equivalent SQL for both approaches and compare the plans