LIMIT & OFFSET: Real-World

Every web application uses pagination - product listings show 20 items per page, search results display 10 per page, infinite scroll loads 50 items at a time. LIMIT and OFFSET enable efficient data loading, preventing slow queries that return millions of rows. Critical for scalable UIs and APIs.

Stable order-history pagination

A customer with years of orders needs fast next-page navigation without duplicates when new orders arrive.

Keyset pagination keeps response time bounded and preserves page continuity.

Use the last visible ordering key

SELECT order_id, created_at, total FROM orders WHERE customer_id = 42 AND (created_at, order_id) < (:last_created_at, :last_order_id) ORDER BY created_at DESC, order_id DESC LIMIT 25;

All