Pagination with LIMIT / OFFSET

-- Page 3 of 25 results (offset = (page - 1) * page_size)
SELECT id, title, created_at
FROM   posts
ORDER  BY created_at DESC, id DESC
LIMIT  25 OFFSET 50;

-- Pagination metadata in one query (PostgreSQL window function)
SELECT id, title, COUNT(*) OVER() AS total_rows
FROM   posts
ORDER  BY created_at DESC
LIMIT  25 OFFSET 0;

-- Keyset (cursor) pagination — faster, immune to inserts shifting pages
-- The caller passes the last-seen (created_at, id) tuple from the prior page.
SELECT id, title, created_at
FROM   posts
WHERE  (created_at, id) < ('2025-03-12 14:00:00', 12345)
ORDER  BY created_at DESC, id DESC
LIMIT  25;