An API returns 1million records - how do you design pagination?

Description

If you’re not using pagination, your database is silently suffering. Comment "Pdf" for clean easy notes. ➡️ Offset-based pagination (works, but slow at scale) Uses LIMIT + OFFSET. Example: Page 10 → LIMIT 20 OFFSET 180 Database skips first 180 rows and returns rows 181–200 → slow for big tables. ⸻ ➡️ Cursor-based pagination (used by big apps) Fetch records after the last seen item, not by page number. Example: Twitter loads tweets after tweet_id = 98765, not “page 10”. Fast even with millions of rows. ⸻ ➡️ Always Use Stable Sorting Pagination only works if the order never changes. Example: ORDER BY created_at DESC ensures posts don’t jump or repeat. ⸻ ➡️ Stable Even When Data Changes New records don’t shift pages or cause duplicates. Example: New tweets arrive → your feed position stays correct. ⸻ ➡️ Return Only What the List Needs Avoid loading heavy data during pagination. Example: Product list → id, name, price (not reviews, images, descriptions). ⸻ ➡️ Cache the First Page Most users never scroll far. Example: Amazon homepage → page 1 served from cache instantly. ⸻ Interview takeaway: OFFSET works for small data. Cursor-based pagination + stable sorting is how real systems scale. #api #backenddeveloper #developer #coding #techreels (pagination design, database performance, cursor pagination, offset pagination, system design interview, backend engineering, scalable APIs, SQL optimization, large datasets, API performance) To join the community 👇 https://discord.gg/YtVEvJXfct Connect with me on social media 👇 LinkedIn : https://www.linkedin.com/in/raj-padval-10869125b/ Instagram : https://www.instagram.com/iamrajpadval/ Twitter / X : https://x.com/coder29yt