Very Important interview question

🚨 Famous Interview Question:

Your API is returning 1 MILLION records

How do you design pagination?

This is not just adding LIMIT.
This is production-grade pagination design.

Let’s go step by step.

⸻

1️⃣ Why This is a Problem
• 1M records → huge memory usage
• Network becomes slow
• User waits forever

Production lesson: Never return all records at once. Ever.

⸻

2️⃣ Offset-Based Pagination
• Simple approach using LIMIT + OFFSET

SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 0

• Page 2 → OFFSET 20

Production lesson: Easy to implement, but slow at large scale (full scan).

⸻

3️⃣ Cursor-Based Pagination
• Use last record as reference (cursor)

SELECT * FROM orders
WHERE id grter last_seen_id
ORDER BY id ASC
LIMIT 20

Production lesson: No scanning → consistent fast performance.

⸻

4️⃣ Keyset Pagination (Best at Scale)
• Based on indexed column (timestamp / id)

SELECT * FROM orders
WHERE created_at last_seen_timestamp
ORDER BY created_at DESC
LIMIT 20

Production lesson: Fastest and most stable pagination in production.

⸻

5️⃣ API Response Design
Always return:

{
“data”: […],
“nextCursor”: “xyz123”,
“hasMore”: true
}

Production lesson: Client should know what to fetch next.

⸻

6️⃣ When to Use What
• Offset → small data / admin panels
• Cursor → infinite scroll (feeds)
• Keyset → high-scale production systems

Production lesson: Choose based on use case, not habit.

⸻

🔥 Interview Ready One-Liner:
Use offset for simple cases, cursor for scalable pagination, and keyset for high-performance systems—never return all records in production.

⸻

Follow @CodeEra for more production-ready backend patterns 💪
HAPPY CODING 🚀

#backend #systemdesign #java #microservices #scalability production database sql pagination performance developers techindia interviewquestions

Видео Very Important interview question канала CodeEra by Amit