# Keyset pagination

> Page through large tables with a cursor on an indexed key instead of OFFSET.

Concept · Networking & APIs · https://levelcode.org/concepts/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:

```sql
-- 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](https://levelcode.org/concepts/unique-ids)).

## 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.