CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 3 of 5 · 1 min read

Keyset pagination

Remember the last row's key instead of a row count, and filter for rows after it directly.

Think of a bookmark placed at the exact page where reading last stopped. The next reading session begins directly from that page, with no need to flip past every earlier page again.

In plain words: keyset pagination, also called cursor-based pagination, remembers the sort key's value from the last row of the previous page, rather than counting how many rows to skip. The next page is fetched using a sargable condition, covered on the previous page, filtering for rows after that remembered value, which an index on the sort column can satisfy directly, without generating or discarding any earlier rows at all. This keeps every page's cost roughly constant, regardless of how far into the result set it is.

A worked example. After returning a page ending at placed_on = '2026-03-15', the next page is fetched with WHERE placed_on > '2026-03-15' ORDER BY placed_on LIMIT 20, using the index directly with no rows discarded.

The key lines.

-- First page
SELECT * FROM orders ORDER BY placed_on LIMIT 20;
-- Next page, remembering the last row's placed_on value from the previous page
SELECT * FROM orders WHERE placed_on > '2026-03-15' ORDER BY placed_on LIMIT 20;

Keyset pagination

Keyset pagination remembers the previous page's last sort-key value and fetches the next page with a sargable condition filtering for rows after it, keeping every page's cost roughly constant.

Example: a bookmark letting reading resume directly from where it left off, with no need to flip past earlier pages again, mirrors keyset pagination.