Handling API Pagination

No, you cannot just say “ban the user,” though I would not blame you for thinking it for half a second, haha. But in a real backend interview, this question is whether you understand why deep offset pagination quietly murders databases. My first move would be to look at the query plan. Here is how I would think about it: [1] The problem is probably OFFSET Most APIs like this eventually become: LIMIT 50 OFFSET 24286400 That means the database may have to walk through 24 million rows, discard them, and then return the next 50. The user only sees 50 records. The database pays for millions. That is why page 1 is fast, page 100 is okay, and page 485728 feels like the database is being punished for existing. Btw, if you’re preparing for Senior to Principal-level system design interviews, I’ve put together 90+ fundamentals like this into a guide. You can check it out here: puneetpatwari.in [2] Indexes do not fully save bad pagination A lot of people will say: “Add an index on created_at.” That may help sorting, but it does not fix the core issue if the database still has to skip millions of entries. Even with an index, deep offset pagination can create: - high CPU usage - wasted index scans - poor cache behavior - slow disk reads - replication lag - terrible tail latency The problem is that the API allows users to ask the database to count past half the table before returning anything useful. [3] Replace page numbers with cursor pagination For transaction history, users usually do not need page 485728. They need “give me the next 50 after the last transaction I saw.” So I would move to cursor-based pagination: GET /api/v1/transactions?cursor=abc&limit=50 Internally, the cursor can encode: - last_seen_created_at - last_seen_transaction_id - sort direction - filter state Then the query becomes: “give me 50 rows older than this timestamp and id.” With a composite index like: (user_id, created_at, transaction_id) the database can jump directly to the right range instead of skipping millions of rows. [4] Make the cursor opaque and stable The client should not build the cursor. The backend should return it. That lets us safely change the internal query strategy later without breaking clients. It also prevents users from randomly jumping to impossible pages and forcing expensive scans. If someone needs historical data across millions of transactions, that should probably be an export job, not a synchronous API call. [5] Protect the system I would also add: - max page depth for legacy APIs - query timeout - rate limits for expensive access patterns - observability by page depth - slow query alerts - async export for large history - migration path from page-based to cursor-based pagination

When developers first learn EF Core, they often reach for Offset Pagination (Skip + Take). It feels natural: page 1, page 2, page 3… • Easy to code • Easy to understand • Works perfectly fine when the dataset is small That’s why it’s the go-to for junior developers or anyone building smaller apps. But as experience grows (and datasets grow with it), we start to see the cracks: • Performance slows down because the database still scans all skipped rows • Results can become inconsistent if data changes between queries • It doesn’t scale well for APIs or infinite scrolling This is where Keyset Pagination (cursor-based) comes in. Instead of skipping rows, it fetches data starting from the last-seen record. • Blazing fast, even on millions of rows • Stable with real-time updates • Ideal for feeds, APIs, and infinite scrolling That’s why senior developers tend to use Keyset Pagination more often - not because Offset is “wrong,” but because they’ve seen the tradeoffs firsthand. 💡 The truth? Neither method is always better. It depends on the scenario: • Admin dashboards, reporting, small datasets - Offset is fine • Large-scale apps, APIs, high-performance needs - Keyset is the smarter choice I put together a detailed guide with examples, pros/cons, and example. 👉 Read it here: https://lnkd.in/gNCckdxm ♻️ If you found this helpful, consider reposting to help others in the community.  👉 Follow me, Poorna Soysa, and hit the 🔔 on my profile so you don’t miss upcoming .NET tips and deep dives. Thanks for reading — let’s keep learning .NET together!

📄 Enhancing API Performance with Pagination 📄 Pagination is a crucial technique in REST APIs for efficiently managing large datasets and improving performance. By implementing pagination, you can enhance user experience by delivering data in manageable chunks. Here’s how pagination works and best practices for integrating it into your REST APIs: 1. What is Pagination? - Pagination divides large datasets into smaller pages or segments, allowing clients to retrieve data incrementally. - It typically involves using query parameters like `page` and `limit` to control the number of results per page. 2. Benefits of Pagination: - Improved Performance: Reduces server load and response times by fetching only a subset of data per request. - Enhanced User Experience: Provides a smoother browsing experience for users navigating through large datasets. - Scalability: Allows APIs to handle large volumes of data without overwhelming clients or servers. 3. Implementing Pagination: - Use query parameters to specify pagination details: - `page`: Indicates the current page number (e.g., `page=1`, `page=2`). - `limit`: Specifies the maximum number of results per page (e.g., `limit=10`, `limit=20`). - Calculate offsets and limits in your API backend to fetch the appropriate data subset for each page. 4. Handling Pagination Links: - Include pagination links in API responses to facilitate navigation between pages. - Use URL links with `next`, `prev`, `first`, and `last` to indicate navigation options (e.g., `next_page`, `prev_page`). 5. Consistent Pagination Strategy: - Maintain consistency in pagination across all endpoints and API versions to avoid confusion for developers and clients. - Document pagination parameters and behaviors in your API documentation for clarity. 6. Testing Pagination: - Test pagination scenarios during API development to ensure accurate data retrieval and seamless navigation. - Verify edge cases, such as the first and last pages, to validate pagination logic. Implementing pagination effectively not only optimizes API performance but also enhances usability for clients consuming your API. By following these best practices, you can ensure a smooth and efficient data browsing experience through your REST APIs. How do you handle pagination in your REST API projects? What challenges have you encountered, and how did you address them? Share your insights and experiences in the comments below! Let's discuss and learn from each other's approaches to implementing pagination in APIs. For more tips on REST APIs and software development practices, follow my LinkedIn profile: [https://lnkd.in/gVUn5_tx) #API #RESTAPI #Pagination #SoftwareDevelopment #BestPractices #TechCommunity

𝗦𝘁𝗼𝗽 𝗨𝘀𝗶𝗻𝗴 𝗦𝗸𝗶𝗽–𝗧𝗮𝗸𝗲! 𝗔 𝗙𝗮𝘀𝘁𝗲𝗿 𝗪𝗮𝘆 𝘁𝗼 𝗣𝗮𝗴𝗶𝗻𝗮𝘁𝗲 𝗟𝗮𝗿𝗴𝗲 𝗧𝗮𝗯𝗹𝗲𝘀 𝗶𝗻 .𝗡𝗘𝗧 Most developers rely on the classic Skip–Take approach (or SQL’s OFFSET–FETCH) for pagination. It’s simple and works well until your table grows beyond a few thousand records. 𝗛𝗲𝗿𝗲’𝘀 𝘁𝗵𝗲 𝗰𝗮𝘁𝗰𝗵 Every time you use OFFSET, your database still needs to count and skip over all the preceding rows before it starts returning results. That means the more you paginate, the slower your query becomes. For large datasets, this can easily turn into seconds of delay per query. 𝗧𝗵𝗲 𝘀𝗺𝗮𝗿𝘁𝗲𝗿 𝘀𝗼𝗹𝘂𝘁𝗶𝗼𝗻? 𝗞𝗲𝘆𝘀𝗲𝘁 𝗣𝗮𝗴𝗶𝗻𝗮𝘁𝗶𝗼𝗻. Instead of telling the database “skip 10,000 rows,” you say “start after the last record I already have.” This tiny mindset shift changes everything: - No wasted scanning or skipping - Predictable, linear performance - Works perfectly with sequential IDs (INT, BIGINT, or UUIDv7) - Ideal for real-time feeds, dashboards, and infinite scrolls So if your app uses Skip–Take or OFFSET–FETCH, it’s time to upgrade your pagination strategy and make your queries leaner, faster, and production-ready. 𝗣𝗿𝗼 𝘁𝗶𝗽: If your IDs aren’t sequential, migrate to UUIDv7 it provides sortable identifiers optimized for modern databases. What’s your preferred pagination strategy for high-traffic APIs or dashboards? Would you pick keyset pagination or stick with offset-based paging? Let’s discuss Follow me Kanaiya Katarmal, and hit the 🔔 on my profile so you don’t miss upcoming .NET tips and deep dives. 👉 𝗦𝘂𝗯𝘀𝗰𝗿𝗶𝗯𝗲 𝘁𝗼 𝗺𝘆 𝗻𝗲𝘄𝘀𝗹𝗲𝘁𝘁𝗲𝗿: https://lnkd.in/dp7tz_3V #dotnet #efcore #databaseperformance #csharp #softwareengineering #backenddevelopment #sqloptimization #programmingtips

Pagination Strategies for Large Systems Pagination looks simple when your table has a few thousand rows. It becomes a real problem once your data grows. Here is how the main approaches compare when the dataset is large. Most pagination implementations start with offset and page numbers. You ask for “?page=3&size=10” and the database skips 20 rows, then returns 10. Simple to build and easy to explain. The issue is that the database still reads every row it skips. Page 5 is fast. Page 5000 is slow. Keyset pagination handles deep pages much better. Instead of counting rows, you tell the server to return items after ID 101. The query goes straight to that point using the index, so page 1 and page 10,000 take the same time. Continuation tokens are common in large APIs like S3 and YouTube. The server returns a token, you send it back on the next request, and it resumes from where the last response ended. It hides the cursor logic from the client. The token is tied to the filters and sort order from the original request, so the client must keep using the same parameters until the pagination ends. Time-based pagination works well for feeds and logs. You ask for items before a timestamp and read backwards. Add a secondary field, like an ID, for rows that share the same timestamp. Without it, you can lose records. Over to you: Which one have you used? -- Subscribe to our weekly newsletter to get a Free System Design PDF (368 pages): https://lnkd.in/gauQcE45 #systemdesign #coding #interviewtips  .