Is using offset in SQL a hero or a silent evil?

Pagination is one of the common things that every backend dev has to perform on API whether it is REST or GraphQL. Pagination is the process of dividing the huge set of data into small pieces called pages. Let’s suppose you have 10,000 rows of posts in DB. You won’t be emitting everything at one go as it can easily bottleneck your server and the loading time would be also high. In this case, you should divide your data into small pieces like in sets of 10, 20, or as you wish.

Dividing a huge chunk of data into small chunks is called pagination.

An approach for pagination is by using limits and offsets that almost every DB supports.

In this approach, offset is the number from which the result should start, and limit is the number of items you want to get.

In the above image as you can see there is a total of 7 records and you have to show just 2 records at initial load, the query will become:

select * from table offset 0 limit 2

To get an offset of the next set of results just use this formula

new_offset = limit + prev_offset

Most of the products that are in production use this approach to get data paginated from the database. This approach is the most basic and easiest to implement but it has a serious downside.

The downside of using offset

Do you know how the database engine gets data after offset value?

So a db engine have to do a complete lookup that means they have to traverse all the way from 0 to offset and get the results.

This can have serious performance issues if we have a large dataset. Let’s suppose we have 1,00,000 rows in our DB and we want the last two results then our query will be something like:

select * from table offset 99998 limit 2

The DB engine will go all the way from the 0th row to the 99998th row until it finds the offset value and then gets the last two results which you might have guessed by now can have a huge impact on the performance of the query.

A better approach can be using cursors that give better performance than this offset approach. I will explain that approach in the next blog. So that’s it for this blog.

I hope you liked the blog and if you do, don’t forget to hit the clap icon and follow me for more such blogs.

Drink water. Keep smiling…