The Hidden Dangers of SQL OFFSET: Pagination Woes Explained

While SQL OFFSET might seem like a simple solution for pagination, it comes with severe performance, memory, and data integrity issues.
As your application and database grow, these problems will become more apparent and can hinder scalability.
Keyset pagination offers a more efficient and reliable alternative,
providing consistent performance, predictable memory usage, and stable data ordering. By adopting keyset pagination, we can build applications that are not only performant but also scalable and reliable in the long run.
Slides are available here: speakerdeck.com/matteobertozz...
#database #backend #codingtips #webdevelopment #coding #backenddeveloper #webdeveloper #coding #programming #sql

[@arcstur]

Incredibly high quality. Thanks for your work!

[@th30z-code]

Thank you! i'm glad you enjoyed it

[@juhairahamed5342]

Good Explanation

[@user-mp9um5qj3u]

I am new to programming and learning spring and i am falling in love with database design. I am a newbie in this field.
Your videos are very helpful❤🎉🎉🎉. Hope to see more database and backend related stuff.

[@th30z-code]

Hi! I'm glad the content was helpful.
There will surely be more videos on database and backend stuff.
If you have any specific topic that you are interested in, let me know and if I have the knowledge I'll try to cover it.

[@lattelover7186]

I have questions:
1. How does Keyset pagination help with dynamic data filtering tuple WHERE new_categories IN ("politic", "sports") or new_title LIKE "something%" or EXISTS subquery.
2. How it solve the backward direction. Let's say last seen news_id is 51 and we need to get previous data with id 41-50.

[@th30z-code]

Hi, thanks for watching!
1. You can have any data filtering in your query. The pagination query must contains the filter AND the key condition (filter-cond AND key-cond). In the example of news feed is quite common have something like: WHERE topic = 123 AND (date, id) < (prev_date, prev_id)
2. backward direction is a "limitation" of the keyset pagination. If you use infinite scrolling you don't need a query to go back, since you already have all the previous data locally. If you start from a specific position the only way to go back is to do the query but with the reverse sort order, which means that you have to keep two indexes for both directions.

[@lattelover7186]

@@th30z-code
1. When using data table/grid in frontend we usually deal with arbitrary sorting. Can the pagination still handle that kind of sorting well.
2. You are right, we already have all the prev data locally. Make sense.
Thank you so much

[@th30z-code]

Arbitrary sorting does not play well with databases. unless there is a specific index for the sort order, It's a lot of memory and cpu usage. so every time a request comes in there is a full table scan and a sort operation. It's ok for 10 apps with few users that access the table/grid sporadically, but if you have a bit of traffic it is better to fetch all the main columns you see in the table and sort them locally. If you have large fields (e.g. blobs, or something hidden with an expander) you can fetch them on demand using the key of the row. Fetching everything should cost less than asking the db to sort everytime. In terms of data should not be that much (e.g. 32bytes per column, 10 columns 10k rows are 3MB)

[@ddavid8888888]

There are disadvantages for this method too… not showing makes this video harmful for devs who can’t think about the cases this is not useful

[@th30z-code]

I tried to cover the main advantages and disadvantages from both the Database and the UI point of view.
but if you have further points that you want people to know, feel free to add your notes