Pagination with LIMIT

Before (Inefficient Query)

-- Slow OFFSET pagination
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 10 OFFSET 10000;

After (Optimized Query)

-- Efficient keyset pagination
SELECT * FROM orders 
WHERE order_date < '2024-01-15'
  AND order_id < 10005
ORDER BY order_date DESC, order_id DESC 
LIMIT 10;

Why This Optimization Works

1. OFFSET Problemi: With OFFSET 10000, the database still scans and discards the first 10000 rows. As OFFSET increases, performance degrades linearly.

2. Keyset Paginationi: By filtering on the last seen values (order_date, order_id), the database can start directly at the desired position using an index.

3. Index Utilizationi: Keyset pagination works perfectly with indexes, making it O(1) vs O(n) for OFFSET.

Pagination Patterns

Traditional OFFSET

-- Page 1
SELECT * FROM orders ORDER BY id LIMIT 10;

-- Page 1000 (SLOW!)
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 9990;

Keyset Pagination (Cursor-based)

-- First page
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 10;

-- Next page: remember last order's values
SELECT * FROM orders 
WHERE (created_at, id) < ('2024-01-15', 12345)
ORDER BY created_at DESC, id DESC 
LIMIT 10;

When to Use Each

Scenario	Recommended Method
Small tables (<10K rows)	OFFSET is fine
Random access to pages	OFFSET with caching
Sequential browsing	Keyset pagination
Infinite scroll	Keyset pagination
Large tables	Always keyset