🐘 PostgreSQL
Beginner
What is the LIMIT and OFFSET clause in PostgreSQL?
Answer
LIMIT restricts the number of rows returned; OFFSET skips a number of rows before starting to return rows. Used for pagination: SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40; returns rows 41-60 (page 3 if page size is 20). PostgreSQL also supports FETCH FIRST 20 ROWS ONLY (SQL standard). OFFSET pagination has a major performance problem: OFFSET 10000 still scans and discards 10,000 rows. For large datasets, use keyset/cursor pagination instead: WHERE id > last_seen_id ORDER BY id LIMIT 20 — this is O(1) using the index and does not degrade at high offsets.