Why should you care?

Flipkart mobile lists 20 products per scroll; OFFSET grows with page number so users do not download the entire catalog at once.

See it live — copy this example

Run in pgAdmin or psql.

SELECT order_id, created_at, total_amount
FROM orders
ORDER BY created_at DESC
LIMIT 15 OFFSET 30;

What happened?

• Orders sort newest first.
• OFFSET 30 skips the first 30 rows (pages 1–2 if page size is 15).
• LIMIT 15 returns page 3.
• Large OFFSET on huge tables gets slow — keyset pagination is the production upgrade.

Practice next

1. Insert at least 50 orders with varied created_at.
2. Run LIMIT 15 OFFSET 0 for page 1.
3. Run OFFSET 30 for page 3 and compare order_ids.
4. Fetch page 2 with LIMIT 10 OFFSET 10 on products sorted by price.
5. Replace OFFSET with WHERE order_id < $cursor ORDER BY order_id DESC LIMIT 15.

Remember

LIMIT = page size; OFFSET = (page-1)*size. Always pair pagination with stable ORDER BY. Keyset pagination beats large OFFSET at scale.

PostgresVerse order history

Customer app loads order history three pages deep without scanning entire ledger.

Outcome: API returns 15 rows per request; bandwidth stays low on 4G.