Offset vs Cursor Pagination: The Mistake Most Backend Engineers Make

Your API works perfectly today. Then six months later, your transaction table grows from 10,000 records to 10million records. Suddenly, requests that once took 80ms now takes 4seconds. The question is: Did you choose the right pagination strategy? Most backend developers are familiar with pagination, but many don't realize that there are different pagination strategies, and choosing the wrong one can seriously affects your application's performance as data grows. In this articles, we'll compare the two most common approaches: Offset Pagination Cursor Pagination We'll look at how they work, their advantages and disadvantages, when to use each one, and how companies handling millions of records approach this problem. What is Pagination? Pagination is simply the process of breaking a large dataset into smaller chunks. Imagine your database contains 5 million transactions. Returning all 5 million records in a single API response would: Consume excessive memory Slow down the database Increase API response time Create a poor user experience Instead, we return only a small subset. For example: 10 records per request The client then requests additional records when needed. The question is... How should the next set of records be fetched? There are two common answers. 1. Offset Pagination Offset pagination is probably the first pagination technique every backend developer learns. A request usually looks like this: GET /transactions?page=3&limit=10 or GET /transactions?offset=20&limit=10 The database query looks something like: SELECT * FROM transactions ORDER BY created_at DESC LIMIT 10 OFFSET 20 ; Here, the database skips the first 20 rows before returning the next 10. Visual Representation Suppose we have these transaction IDs: 100 99 98 97 96 95 94 93 92 91 90 89 88 87 ... with Page = 1 Limit = 5 You get: 100 99 98 97 96 Page 2 skips those records and returns: 95 94 93 92 91 Simple, Easy and Readable. Advantage of Offset Pagination → Very easy to implement Almost every fr