The Hidden Dangers of SQL OFFSET: Pagination Woes Explained

Рет қаралды 3,831

11 ай бұрын

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/matteobertozzi/database-pagination-sql-offset-vs-the-seek-method
Chapters:
0:00 Offset & Limit problems
1:05 KeySet Pagination
2:07 SQL-92 Tuple Comparison
2:19 KeySet Pagination Limitations
2:36 Rethink the UI and endpoints
4:04 Pagination from the storage point of view
#database #backend #codingtips #webdevelopment #coding #backenddeveloper #webdeveloper #coding #programming #sql

Пікірлер: 12

@arcstur 7 ай бұрын

Incredibly high quality. Thanks for your work!

@th30z-code 6 ай бұрын

Thank you! i'm glad you enjoyed it

@farhadeviltrg6116 Ай бұрын

you have explained it very well thank u

@surajmandal_567 11 ай бұрын

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 11 ай бұрын

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.

@juhairahamed5342 2 ай бұрын

Good Explanation

@lattelover7186 11 ай бұрын

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 11 ай бұрын

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 11 ай бұрын

@@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 11 ай бұрын

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 2 ай бұрын

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 2 ай бұрын

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