Recipe

Keyset Pagination Design

Offset pagination collapses under load: every page re-scans rows the database already skipped. Keyset pagination uses an indexed cursor to jump straight to the next slice, giving stable O(log n) latency even at the millionth page. This recipe walks through the design, the SQL, and the API contract Meridian recommends for high-throughput list endpoints.

1. Pick a stable, indexed sort key

The cursor must be monotonic and unique. A composite key of(created_at, id)is the standard choice: created_atgives natural ordering, and idbreaks ties when two rows share a millisecond. Build a single composite index that covers both columns in the same direction you query them.

2. Query with a row-value comparison

Instead of OFFSET 10000, pass the last-seen cursor as a tuple comparison. Postgres can use the composite index to seek directly to the next page without scanning the prior ones. The query plan stays flat regardless of depth.

-- Page 1 (no cursor)
SELECT id, created_at, title
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Page N (cursor = last row of page N-1)
SELECT id, created_at, title
FROM events
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;

3. Encode the cursor opaquely

Never expose raw primary keys in API responses. Base64-encode the(created_at, id)tuple and return it as a next_cursorstring. Clients pass it back verbatim; you decode and feed it into the query. This keeps the schema swappable and prevents callers from hand-crafting cursors that bypass tenant filters.