Offset pagination is easy to understand

LIMIT and OFFSET support page numbers and arbitrary jumps. As the offset grows, the database may still scan or discard many preceding rows. Inserts and deletes between requests can also shift which row appears on a page.

SELECT id, created_at, title
FROM article
WHERE status = 'Published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 2000;

Keyset pagination follows the last row

A cursor contains the last row's ordering values. For descending (created_at, id), the next page filters with a tuple comparison or equivalent predicate, then uses the same order and limit. The unique id tie-breaker makes the ordering deterministic.

SELECT id, created_at, title
FROM article
WHERE status = 'Published'
  AND (created_at, id) < (@last_created_at, @last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Match an index to the filter and order

An index beginning with the selective status column and continuing with the ordered columns can support this access pattern. Validate with EXPLAIN (ANALYZE, BUFFERS) on representative data. An index adds write and storage cost, so create it for a measured query, not because every column looks important.

Trade-offs

Keyset paging is strong for next/previous traversal but does not naturally jump to page 100. Offset is often adequate for small administrative lists. The API contract and expected navigation should decide which model is appropriate.