Database Optimization

Pagination: Cursor vs Offset

The Offset Problem

-- Offset pagination (common but broken at scale)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- Why it's slow:
-- 1. DB must scan and discard 10,000 rows to return 20
-- 2. Gets worse as offset increases: O(offset + limit)
-- 3. Inconsistent results if data changes between pages