Paging By Where You Left Off
Keyset pagination, also called the seek method, replaces OFFSET with a WHERE condition on the ordering key. Instead of counting rows to skip, you remember the last value seen and ask for rows after it.
How It Works
- Order by a unique key, for example created at then id.
- Return a page with LIMIT and no OFFSET.
- Remember the key values of the last row on that page.
- The next request adds a condition like key greater than that last value, then LIMIT again.
Why It Stays Fast
- The condition lets an index seek directly to the start of the next page.
- No rows before the cursor are scanned, so every page costs about the same regardless of depth.
- Because it anchors on real key values, inserts elsewhere do not shift the window.
Trade Offs
- You can move forward and backward but cannot jump to an arbitrary page number directly.
- The ordering key must be unique, so ties are broken with a second column to keep the seek deterministic.
Key idea
Keyset pagination seeks past the last seen key using an indexed WHERE condition, giving constant cost pages that are stable under inserts but cannot jump to a numbered page.