Offset says how many rows we want to skip but doesn’t account for row deletion or new rows being created. That means that the higher our offset is, the longer our database query will take.Īnother downside of offset pagination is that it doesn’t play well with real-time data or data that changes often. Alongside that, the offset in databases is implemented in a way that loops through rows to know how many should be skipped. Looking at sum attribute, we can already see that it can take quite some time to count all rows in a database to the exact number. Using limit, sum and a bit of math, you can calculate last page number using lastPage = ceil(sum / limit)Īs convenient as this feature is for the user, developers struggle to scale this type of pagination. How do they do it? Alongside results for each page, they also tend to return a sum attribute telling you how many rows there are in total. Some websites implementing offset pagination also show the page number of the last page. In the SQL world, such a query would be written as SELECT * FROM posts OFFSET 10 LIMIT 10. We continue this approach thereby allowing the end user to paginate through the results and see all of their content. To get the second page, we keep the limit at 10 ( this doesn’t change since we want every page to contain 10 rows ) and set offset to 10 ( return results from the 10th row onwards ). To get the first page of our data, we set the limit to 10 (because we want 10 items on the page) and offset to 0 (because we want to start counting 10 items from the 0th item). Offset is like a page number, but with a bit of math around it ( offset = (page-1) * limit) limit - Number of rows to fetch from the database.To implement offset pagination, we will usually need two pieces of information: Most websites use offset pagination because of its simplicity and how intuitive pagination is to users. Let's explore both methods to understand their advantages, limitations, and performance implications. It’s very clear that two tech giants could not agree on which solution is better! Why? Well, we’ll need to use an answer developers hate, Because it depends. As for your users, their experience will look something like this. As soon as your list grows to more than a few thousand items, your database will take too long to resolve each request and your front-end’s speed and accessibility will suffer. This data could be anything from user-generated content, content added by administrators or editors, or automatically generated audits and logs. These days, pagination has almost become a necessity since every application is very likely to deal with large amounts of data. Pagination also solves a lot of performance issues both on the client and server-side! Without pagination, you’d have to load the entire chat history only to read the latest message sent to you. Thanks to pagination, we can split our large dataset into chunks ( or pages ) that we can gradually fetch and display to the user, thus reducing the load on the database. Pagination is a strategy employed when querying any dataset that holds more than just a few hundred records. “An ounce of prevention is worth a pound of cure.” - Benjamin Franklin ? What is pagination? This article will prepare you to fight database-related performance issues using offset and cursor pagination. To prevent these horrific scenarios, we should be aware of risks from the beginning because a well-prepared developer will never have to risk.