How do you implement pagination efficiently?

Offset pagination is convenient for page numbers but becomes slower as the offset grows, because the database still processes the skipped rows. Keyset pagination remembers where the previous page ended and seeks directly to the next position.

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 4000;

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ('2026-09-01', 98123)
ORDER BY created_at DESC, id DESC
LIMIT 20;

• Always order by a unique combination, usually the sort column plus a tie-breaker such as id
• An index on (created_at DESC, id DESC) supports the keyset query
• Keyset pagination cannot jump directly to page 200, so it fits infinite scroll better than page numbers
• Offset pagination can show duplicate or missing rows if data changes between requests
• For small tables or admin tools, OFFSET is often acceptable

If the UI needs page numbers and deep offsets, consider restricting how deep users can go or maintaining a separate ordered index or snapshot. Do not assume LIMIT makes an OFFSET query cheap.