Is using OFFSET is good or bad in SQL query?

Hello Readers,

Hope you all are doing well!

Today I m here with another topic. As we all backend developers very frequently use the OFFSET keyword in SQL queries but is it good to use ? or it is bad? If it's bad then what will be a good approach?

(Note:- We will be using PostgreSQL Databases for query reference)

We will cover all these topics lets start the show!

Suppose we have a table named as users with two attributes id (primary key) and random_string(varchar(100)).

				users
	id(serial primary key)   |   random_string(varchar(100))					

Now by using the script we add almost 10K data

Suppose we need to write a query we need data on page number 2 and fetch 10 rows assuming we have already shown 10 rows on page number 1. We gonna write this simple and beautiful query

The above query is very simple we are just saying to the database "hey buddy I need 10 more records but the condition is here you need to skip the first 10 rows and provide me next 10 rows"

Simple and beautiful! Let's see what the hard work database did in the background to get us data.

As we discussed earlier we just gonna use EXPLAIN ANALYZE syntax at the beginning of the query by this the database gonna tells how much work he has done.

Let's discuss the above weird-looking statements

• The first statement tells us the cost and limit of rows need to fetch and loops database used to fetch 10 rows

• The second statement tells us that the database uses an index scan because as we know id attribute is our primary key and its clustered index.

• If you look closely at the highlighted part database actually fetches the 20 rows (actual time=0.049..0.064 rows=20 loops=1)

• That's weird you have requested 10 rows but it fetches the 20 rows?

• Yes mate, that's how the OFFSET works in SQL

• It always fetches rows, the sum of both OFFSET value and LIMIT value in our case i.e.; (OFFSET value=10 + LIMIT value=10=20 rows)

• And after fetching it discards the OFFSET value and returns the LIMIT value rows

• Lets increase the value of OFFSET suppose we have reached at page number 1000. We gonna write a simple and beautiful query and analyze it.

• Oops! That's hurtful for fetching 10 rows database needs to first fetch 1010 rows (sum of the offset value and limit value).

• You can imagine how much disk I/O operation database has been performing when we keep increasing the value of OFFSET. If you wanna learn about disk I/O operation of database you can refer to my previous blog (https://databasenoob.wixsite.com/databasenoobs/post/how-database-stores-tables-in-hdd-and-ssd)

• Let's increase the value from 1000 to 10000

• See the execution time difference in the case of 1000 offset value it was 0.712ms and in case of 10000 offset value it is 5.941ms

So how can we optimize this thing?

We can use the help of our clustered index i.e.; our primary key (id). In our scenario, we are fetching the next 10 rows for page number 2. When we fetched for page number 1 we must get the last id value from page number 1.

If you see the last value of id is 10. For fetching the next 10 values what we can write query like this.

The above statement is very simple we just added a condition here saying "hey database give me 10 rows but the condition is the id value must be greater than 10"

Let's see the computation part how much work it did to fetch our beautiful next 10 rows

Voila! we only fetch 10 rows and its execution time is also faster than the previous query with OFFSET. Let's increase it to 10000 value

Again the win-win here! Fewer rows with less execution time!

That's all for this blog!

Stay Safe! Keep Learning!