Keyset Pagination vs Offset

-- Traditional OFFSET pagination (slow for large offsets)
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 1000;

-- Keyset pagination (fast at any depth)
-- First page
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: use the last row's values as cursor
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2024-01-15 10:30:00', 42)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Create index to support keyset pagination
CREATE INDEX idx_posts_cursor ON posts (created_at DESC, id DESC);

-- Helper: get total count efficiently
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'posts';