Keyset pagination

LIMIT 20 OFFSET 100000 makes the database find and discard 100,000 rows before returning 20, so deep pages get slower and slower. Worse, if rows are inserted while a user pages, offsets shift and items are skipped or repeated.

Keyset pagination (the seek method) remembers the last row returned and asks for rows after it:

-- First page
SELECT id, created_at, title FROM posts
WHERE author_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: continue after the last row of the previous page
SELECT id, created_at, title FROM posts
WHERE author_id = $1
  AND (created_at, id) < ($2, $3)        -- last row's created_at and id
ORDER BY created_at DESC, id DESC
LIMIT 20;

With an index on (author_id, created_at DESC, id DESC), every page is a short index range scan, equally fast at page 1 and page 10,000.

#Details

• Include a unique tiebreaker (id) in the sort, or rows with equal timestamps can be skipped or repeated across a page boundary.
• Encode the cursor as an opaque token (base64 of the last values) so clients don't depend on its contents and you can change it later.
• Time-ordered IDs (UUIDv7, Snowflake) let a single column serve as both sort key and cursor (unique ID generation).

#Trade-offs

You can't jump to "page 37", and total counts require a separate (often approximate) query. For infinite scroll, feeds and APIs that's rarely needed — and the consistency under concurrent inserts is a real win. The Backend with Go roadmap's REST API design lesson shows cursor pagination in an API.