don’t use “offset” in your SQL

Video Description

In this video I’ll explain why you should avoid using SQL 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 is inspired by Use the index luke, i’ll have a link to the blog and slides to learn more. Let say you have a web application with an API that supports paging, you user want to request 10 news articles in page 10, this is performed via a simple GET request as shown here The API server receives the GET request and builds the SQL in order to send it to the database hopefully a pool of connections exist here. Page 10 translates to offset 100 assuming each page has 10 records and now the database is ready to execute the query against the table. Offset by design means fetch and drop the first x number of rows, so in this case the database will fetch the first 110 rows and physically drop the first 100 leaving the limit of 10 which the user will get. As the offset increase, the database is doing more work which makes this operation extremely expensive. Furthermore, the problem with offset is you might accidentally read duplicate records. consider the user now want to read page 11 and meanwhile someone inserted a new row in the table, row 111 will be read twice Let us jump and test this against postgres 0:00 Why Offset is Bad 2:30 Offset Example 5:45 Offset alternative Use the Index Luke Blog https://use-the-index-luke.com/no-offset Slides in this video https://payhip.com/b/B6o1 (Members get all slides for free. Become a Member to support the channel) https://www.youtube.com/channel/UC_ML5xP23TOWKUcc-oAE_Eg/join 🎙️Listen to the Backend Engineering Podcast https://husseinnasser.com/podcast 🏭 Backend Engineering Videos https://backend.husseinnasser.com 💾 Database Engineering Videos https://www.youtube.com/playlist?list=PLQnljOFTspQXjD0HOzN7P2tgzu7scWpl2 🏰 Load Balancing and Proxies Videos https://www.youtube.com/playlist?list=PLQnljOFTspQVMeBmWI2AhxULWEeo7AaMC 🏛️ Software Archtiecture Videos https://www.youtube.com/playlist?list=PLQnljOFTspQXNP6mQchJVP3S-3oKGEuw9 📩 Messaging Systems https://www.youtube.com/playlist?list=PLQnljOFTspQVcumYRWE2w9kVxxIXy_AMo Become a Member https://www.youtube.com/channel/UC_ML5xP23TOWKUcc-oAE_Eg/join Support me on PayPal https://bit.ly/33ENps4 Join our Thriving Backend Community on Discord https://discord.com/invite/CsFbFce Stay Awesome, Hussein