Keyset Pagination in Postgres: The Pagination That Stays Fast

The first time you write SELECT ... LIMIT 25 OFFSET 1000000, the query takes ten seconds and you wonder if Postgres is broken. Postgres is fine. OFFSET is doing exactly what it has to: scanning past the first million rows to discard them, then returning the next 25.

Keyset pagination removes the discarding. It costs the same on page 1 as on page 1000. Here is how to switch over.

The problem with OFFSET

-- Page 1: fast
SELECT * FROM orders ORDER BY created_at DESC LIMIT 25 OFFSET 0;

-- Page 1000: slow
SELECT * FROM orders ORDER BY created_at DESC LIMIT 25 OFFSET 24975;

The second query has to read and discard 24,975 rows before it can return 25. On a large table, that work is linear in the offset. Page 100 is 100x slower than page 1.

OFFSET is the right tool for shallow pagination — first few pages of search results. It is the wrong tool for anything that might paginate deep.

Keyset: paginate by the last seen row

Instead of "skip 24,975 rows," keyset says "start after the last row I showed you."

-- First page (no cursor)
SELECT * FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 25;

-- Next page (using last row's created_at + id from previous page)
SELECT * FROM orders
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 25;

The (created_at, id) < (...) comparison uses tuple ordering: lexicographic over the tuple. The index (created_at DESC, id DESC) answers this in constant time regardless of how deep the pagination goes.

Page 1: same speed. Page 1000: same speed. This is the property OFFSET cannot have.

Why include id in the ordering

If you only ORDER BY created_at, two rows can have the same timestamp. The pagination loses or duplicates rows when the cursor is on a tie. Adding id (the primary key) breaks ties deterministically.

The rule: the columns you order by must, taken together, uniquely identify a row. Otherwise pagination is unstable.

For most tables, (created_at, id) works. For sorted-by-status tables, (status, id). The trailing id is the tiebreaker.

The matching index

The index column order must match the ORDER BY column order, including direction:

CREATE INDEX orders_keyset
  ON orders (created_at DESC, id DESC);

With this, the keyset query is an index range scan starting at the cursor, taking 25 rows. The cost is independent of which "page" we are on.

API design

The keyset cursor is the last row's sort columns, encoded as an opaque string (base64 of JSON, typically). The client sends the cursor with each page request:

GET /api/orders?after=eyJjcmVhdGVkX2F0Ijoi...

The server decodes the cursor, applies it as the WHERE (created_at, id) < (...) clause, returns the next page plus a new cursor pointing to the last row.

No total page count. No "page 5" jump. Forward-only or with a parallel "before" cursor for backward pagination.

When you cannot use keyset

Keyset works when:

• The order is well-defined and includes a unique tiebreaker.
• You only need next-page navigation (or next + previous, with two cursors).
• The total count is not needed, or is approximated.

Keyset does not work for:

• "Jump to page 47." Without scanning, the server cannot know what cursor corresponds to page 47. If you genuinely need page-of-N navigation, OFFSET (with caching of the count) is unavoidable.
• Random-order pagination. "Sort randomly" is incompatible with keyset by definition. For random sampling, use TABLESAMPLE.
• Filter-on-anything pagination. The cursor is tied to a specific ORDER BY. If users filter dynamically and the filter changes the order, the cursor is invalid. The fix is to encode the filter context in the cursor, which is more work.

For most product pagination — feeds, lists, search results — keyset is the right tool.

Compound keyset

For more complex orderings, the tuple grows:

-- ORDER BY status, priority DESC, created_at DESC, id
SELECT * FROM tasks
WHERE (status, priority, created_at, id) < ($s, $p, $c, $i)
ORDER BY status, priority DESC, created_at DESC, id
LIMIT 25;

The matching index includes all the sort columns. The tuple comparison handles the lexicographic order across mixed directions.

The one annoying detail: Postgres does not handle mixed directions in a single tuple comparison cleanly. If your ORDER BY is status ASC, created_at DESC, you cannot write WHERE (status, created_at) < ($s, $c) because the directions disagree. You have to expand the comparison:

WHERE status > $s
  OR (status = $s AND created_at < $c)
  OR (status = $s AND created_at = $c AND id < $i);

Ugly but correct. Most ORM-style libraries that support keyset pagination generate this for you.

A migration story

A team I worked with had a job-listing page using OFFSET. By page 200, the query took 8 seconds. The product team had assumed pagination was free; the page kept getting slower as the dataset grew.

The fix:

1. Add the keyset index: CREATE INDEX CONCURRENTLY jobs_keyset ON jobs (created_at DESC, id DESC);
2. Change the API to accept after cursor instead of page number.
3. Drop the page count from the UI; replace with "Next →" button.

Query time on every page: 12 milliseconds. Database load dropped meaningfully. The product team stopped being able to make jokes about how slow page 200 was.

The one cost: users could no longer jump to a specific page number. After two weeks of telemetry, exactly zero users had used that feature in the previous quarter. The design constraint was theoretical.