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.

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

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

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

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

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

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.