Keyset Pagination

Definition

Keyset pagination (also called the "seek method") is an alternative to LIMIT ... OFFSET that avoids scanning and discarding skipped rows. Instead of saying "skip N rows," you remember the sort-key value of the last row seen on the previous page, and ask the database to seek directly to rows after that value:

sql
SELECT column1, ...
FROM table_name
WHERE sort_col > last_seen_value
ORDER BY sort_col
LIMIT page_size;

Because sort_col is (ideally) indexed, WHERE sort_col > last_seen_value can be answered directly via an index seek — the database jumps straight to the right spot in the index rather than counting through every earlier row.

Worked Example

Using products(id, name, price), paginate by id, 2 rows per page:

sql
-- Page 1: no "last seen" yet, so no WHERE filter
SELECT id, name FROM products ORDER BY id LIMIT 2;

 id | name
----+-------------------
 1  | Wireless Mouse
 2  | Bluetooth Speaker

The client remembers last_seen_id = 2. For page 2:

sql
SELECT id, name FROM products
WHERE id > 2
ORDER BY id
LIMIT 2;

 id | name
----+-------------
 3  | Desk Lamp
 4  | Yoga Mat

Client remembers last_seen_id = 4. For page 3:

sql
SELECT id, name FROM products
WHERE id > 4
ORDER BY id
LIMIT 2;

 id | name
----+---------------
 5  | Notebook Set
 6  | Phone Case

Notice: no OFFSET was used at all — each query jumps straight past the previous page's last row using a WHERE condition.

Multi-Column Keysets

If the sort key is not unique alone (e.g. sorting by price), the keyset condition needs a compound comparison to also break ties, typically using the primary key as a tiebreaker:

sql
SELECT id, name, price FROM products
WHERE (price, id) > (699.00, 7)   -- "row-value" comparison (supported in PostgreSQL)
ORDER BY price, id
LIMIT 2;

Trade-offs

Advantages: query cost stays roughly constant no matter how deep the page is — page 2 and page 50,000 cost about the same, because the engine always seeks directly via the index rather than scanning-and-discarding.

Limitations:

• You cannot jump directly to an arbitrary page number (e.g. "show me page 500") — you can only move forward/backward from a known row, because there is no "last seen value" for a page you've never visited.
• Requires an index on the sort key(s) to get the seek benefit; without one, it degrades to a scan anyway.
• Slightly more application logic: the client must track and pass along the last-seen key.

Key Takeaways

• Q: What does keyset pagination replace OFFSET with?

A: A WHERE sort_col > last_seen_value condition that seeks directly to the right spot.

• Q: What is the main limitation compared to OFFSET pagination?

A: It can't jump to an arbitrary page number directly — only to the "next"/"previous" page relative to a known position.

• Q: Why is it faster at scale?

A: It uses an index seek instead of scanning and discarding all preceding rows.