Keyset (cursor) pagination instead of OFFSET
Page through large tables quickly and stably by filtering on the last seen sort key.
- SQL
- GreenSnippets
- MIT
- Executed with PostgreSQL 17
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;How it works
OFFSET 100000 makes the database read and discard 100,000 rows, and results shift if rows are inserted while paging. Keyset pagination remembers the last row's sort key and asks for rows after it, which uses an index and stays fast on deep pages.
The sort must be unique and deterministic: add id as a tie-breaker. Back it with an index on (created_at DESC, id DESC). Parameters $1 and $2 are the previous page's last created_at and id (omit the WHERE for the first page).
Related snippets
- Python Tested
Time a block of code with a context manager
Measure elapsed wall-clock time of a with-block using time.perf_counter.
- PHP Tested
Read a large CSV file with a generator
Stream CSV rows as associative arrays using fgetcsv, without loading the whole file into memory.
- JavaScript Tested
Run async tasks with a concurrency limit
Process a large list with at most N promises in flight at once, preserving result order.
- Bash
Run commands in parallel with xargs
Process many files concurrently with xargs -P, safely handling spaces and newlines in names.