Performant Pagination Technique for SQL databases

#sql #pagination #performance #optimization

In this article, I will go over a performant technique for paginating records.

The most common way of implementing pagination is by using offset based pagination. But this is not really performant technique when dealing with huge dataset.

Let's say we need to write a query to fetch latest posts for a social media website.

SQL query would look like this:

SELECT * FROM POSTS ORDER BY ID DESC OFFSET 0 LIMIT 10

Let's say you want to select next page for page 2 you will change offset to 10 & for page 3 offset will be 20 as mentioned below:

SELECT * FROM POSTS ORDER BY ID DESC OFFSET 10 LIMIT 20

SELECT * FROM POSTS ORDER BY ID DESC OFFSET 20 LIMIT 30

This technique selects all the records then skips records. And for that reason it is not really a performant technique. Consider a scenario where you are skipping million rows in order to fetch the next 10 records.

But there is a way, we can improve on this:

SELECT * FROM WHERE POSTS ID < {MAX_POST_ID+1} ORDER BY ID DESC LIMIT 10

This query would only select all the records that need to be returned & hence this would be much faster. Also since ID is an index so search over it would be much faster.

For first page, data returned would look like this.

ID BODY

20 Post Body
19 Post Body
18 Post Body
17 Post Body
16 Post Body
15 Post Body
14 Post Body
13 Post Body
12 Post Body
11 Post Body

And to fetch next 10 records(and subsequent queries) you would pass 11 as post_id. So query would look like this:

SELECT * FROM POSTS WHERE ID < {POST_ID} ORDER BY ID DESC LIMIT 10

ID BODY

10 Post Body
09 Post Body
08 Post Body
07 Post Body
06 Post Body
05 Post Body
04 Post Body
03 Post Body
02 Post Body
01 Post Body

So basically to fetch next page all you have to do is to pass post_id of last record returned from previous query.

This kind of pagination is not length aware. Cause again if we go on to calculate length then it would slow down the API call & subsequently apps will take longer to load newsfeed.