‫In this video, I'll explain why you should avoid using sexual offset when implementing any kind of
‫paging.
‫I'll explain what offset does why is it slow and what is the alternative for better performance?
‫This video inspired by use the index look and I'll have a link to his blog and slides to learn more.
‫And if you're not subscribe to do use the index.
‫Look, log what are doing.
‫This is an extremely mandatory log to read for all database engineers.
‫So check it out.
‫So let's say you have a Web application with an API that supports paging your user.
‫Want to request 10 news article in page ten.
‫This is performed via a simple request as shown here.
‫The API server receives the guest request and builds a sequel in order to send it to the database,
‫hopefully there should be a pool of is already established connection to your databases so you don't
‫have to hit that cold start problem here.
‫Page 10 translates to offset hundred, assuming each page has ten records and now the database is ready
‫to execute the query against this beautiful table.
‫Offset by design means fetch and drop the first X number of rows.
‫So in this case, the database will fetch the first hundred and ten rows and physically drop the first
‫hundred, because guess what?
‫You said it's offset, right?
‫And then leaving the limit of 10 rows, which the user will be actually seeing as the offset increase.
‫The database is doing more work, which makes the operation extremely, extremely expensive.
‫Another problem with the offset is you might accidentally read duplicate records.
‫Consider this the user has now requested page 11, which is now offset one hundred and ten.
‫And meanwhile, someone inserted a new record in your table.
‫So now offsetting one hundred and ten rows will accidentally push your existing rule that you have read
‫before to your ResultSet accidentally.
‫Now, all of a sudden, you just read a row that you have read before, which is row 11 in this case,
‫does jump to the practical side and test this thing on Postgres guys.
‫All right, guys, I have a postscripts database and I have a table called News Desk and there is an
‫index on the ID and I'm going to use the ID here as sense of it as an auto incrementing ID for my news
‫articles here.
‫And don't pay attention to these three fields.
‫This is the title of the article.
‫Essentially what I want to do here is I'm going to use Offset to start paging this and I'm going I want
‫to see how the database perform as a result.
‫All right.
‫So let's start with explain, analyze, select title from news ordered by ID descending because I want
‫the fresh news first and then I'm going to start with offset zero and then I'm going to limit the result
‫was ten.
‫And then what I do that instantly I get the results.
‫Obviously we have two results.
‫Explain what happens here.
‫We are using index scan backwards because I have to order by ID descending and index is already ordered
‫by default.
‫So the index was scanned back or from bottom up I guess.
‫And then there is this much in the index.
‫But this is not really what we were.
‫We actually pulled is ten rows.
‫Why.
‫Because of the zero plus ten rows.
‫That is exactly ten and applying a limit on top of that give you ten.
‫So that's not so bad to point to millisecond.
‫But let's just buy things a little bit.
‫Let's start with offset a thousand and all of a sudden now look at the rows here guys we have pulled.
‫From the index, thousand and ten thousand plus then and that on top of that, we apply the limit and
‫then we only pull ten.
‫You're seeing the pattern so called millisecond, right?
‫That's not so bad.
‫Let's make it.
‫A hundred thousand who you started to feel like seventy nine millisecond and the guys I run this query
‫so many times, so there is a lot of page caches.
‫So this might be slower for first time, but you get the idea.
‫So what did happen?
‫What happened here?
‫One hundred thousand and ten rows.
‫And then only from those stinking hundred thousand rows, we pulled ten.
‫Can you imagine the work that that service is doing?
‫Guys, think about this this much.
‫Rose, if if this was SQL Server, SQL Server will immediately escalate the lot, because that's all
‫we're touching this montreaux but was going to die.
‫But Postgres, thank God it doesn't lock escalation.
‫So, look, it's going to go and we're working with ten to one hundred thousand ten rows.
‫We only actually need ten.
‫And that the the unfortunate thing is the database doesn't know doesn't know to do this because it's
‫too late by then.
‫So you get the idea, do a million and you can start feeling it, it's going to be slower and slower.
‫Right.
‫And if I thought that this is going to go so much slower.
‫Six hundred twenty.
‫And the first time I took like six seconds.
‫But you get the idea right now.
‫What can I do to fix this, guys?
‫So what I'm going to do, I was going to do a little bit.
‫Nice trick to actually accomplish that.
‫I'm going to remove that, explain, analyze and I'm going to remove the offset.
‫And I'm going to simulate an offset by using the ID itself, and then since the I.T. field has a beautiful
‫index, I'm going to use that as a page.
‫Look at this.
‫So it's not really rocket science, what I'm doing here.
‫I'm going to select the title the and let's say I'm going to pull the first in road and you return this
‫to the user.
‫Right.
‫And the user is responsible.
‫Now to send you one was the last ID.
‫It's so it's overseas or it.
‫So it's best.
‫Yes.
‫So now what do you do is basically execute the same query but says hey.
‫Don't bother returning anything.
‫After this idea, this is the last idea I saw, so I want older pages because you're going back, right?
‫And it depends like how how you go forward or back.
‫You can play with this in your algorithm.
‫But if I do this now, I got only these roles.
‫And this is much, much faster.
‫Let's take a look and explain what is what are we doing?
‫Explain, analyze.
‫And if I do that, look at the number of rows that the actual database works with only 10.
‫Why?
‫Because if you do just limit 10, that's the best thing you can do.
‫This is good.
‫You don't you don't use offset with it using limit with two parameter like at 10 comma 10 or offset.
‫That kills the performance because now you have to jump and then read limit 10 tells the database,
‫hey, I'm looking only on the first ten rows that you can pull.
‫There's another component called fetch the first two rows only whatever the first 10 rows.
‫That's another thing you can use, which is exactly the same as limit.
‫All right.
‫So if you do that, then you get back 10 rows, the index will be scanned backward.
‫But whereas again, we only 10 plus we have used an index to actually filter that even down even better.
‫Right.
‫We just essentially filter down the results.
‫Right.
‫We're working with a little bit of a more nice result, which is awesome.
‫And now and the limit applied after the limits only took ten.
‫Now, if I do this, if I if I imagine go wins back all the way to page, I don't know, thousand.
‫Right.
‫Page thousand, which is very, very old.
‫That's what an instant.
‫Right.
‫We have worked now the database worked with 89 rolls and instead of whatever.
‫Right.
‫But still the actual results said from the end from the index came out as ten.
‫Now we jump back to the heap, we pull the title field and then we limit and it becomes a 10.
‫So then the amount of IO is much, much less.
‫The amount of work that databases do is way less.
‫And you saved a lot of CPU cycle and the database will.
‫Thank you.
‫I hope you guys enjoy this with your guys.
‫What are the other approaches that you personally do when paging?
‫I'd love to see them in the comments section below so we all can learn and advance and become better
‫software engineers.
‫I'm going to see you on the next one.
‫You guys stay out of the.