I use both techniques, but most of the time I use limit/offset because the need for paginating to a specific page, I use cursor technique for paginating notifications and tasks and similar things. Thanks Aaron! that was an amazing video as usual.

Cursor pagination seems super useful in scenarios, where you have: a) infinite scroll pagination b) a table that updates in real time c) both

Your content is fenomenal. Explanation and visualization is great. One of the best DB channels out there 👏

I’m always actually learning stuff I thought I already knew with you guys. Great content!

Im about to add pagination to an old report, and at first I was going with limit offset, but after watching this video I know it makes a lot more sense to just use cursor. Thanks for such an organized and well thought out video.🎉🎉

Paginate vs Paginate is the next great battle in tech. Great deep dive on the differences between the two paginate (paginate?) methods that give a really good framework to help decide when you might reach for each one. A++++ would buy again.

You are the first person to explain this in a way I understand, while still being very technical and using SQL examples.

I've been using paginate, chunk and cursor in Laravel, but only LIMIT OFFSET in MySQL. This is a great video! Thanks!

I have been working with MySQL for last 17 years and I never use cursor but your video helped me to understand MySQL cursor. Thank you.

Ive been looking at articles for cursor based pagination, this has been the most informative in actually implementing it. Thank you

that was really informative. keep up the great content. i feel like db subjects are not really talked about. so this channel is a blessing

Great article, I've only used offset pagination, as a web developer I can't see much use cases of just prev/next navigation, unless you have 2 or 3 pages, otherwise you want the user to have the control to navigate a bit close or far or even to the end, and this includes specific pages navigation, left and right, start and end. Otherwise for cursor page navigation the infinite scrolling seems a much more real world option and for that would make sense to use it.

this is an excellent description of the differences between the two methods, thank you for putting this together!

Really clear and not sloppy, I like the way you demonstrate thing to people and it's really helpful! Thanks for such great content!

If anyone is wondering why encode the last id to base64 here is the reason. Javascript can handle number to a certain limit after that it wont work so the solution to that is just to encode your id and send it as a string and decode it to a number on client. If you have a cursor which wont grow more that than the maximum available number then you can directly use numbers instead of encoding it and adding a overhead. This was started by facebook ( now meta) for the very same reason

I learned this some years ago when I had to process all items in a table with many millions of rows. I used LIMIT 1000 and OFFSET (Laravel chunks). In the beginning everything was fine and fast, but as time goes on, the SELECT query became slower and slower, and the DB load higher and higher. So I cancelled the processing job to investigate the problem and changed the query to something like WHERE id >= i*1000 AND id < (i+1)*1000 and every query was fast again.

This video is insanely timely, great explanation, thanks!!

I am not a db guy, but was soooooooo good. So many thanks for the video.

You're raising the bar with each and every video, Aaron. This is not fair!

Bro I like you more than PlanetScale 😂

I hardly even subscribe to channels your content is sooooooo soooo well created. keep up the good work.

I find that limit/offset is only ever a requirement when the PO or PM or whoever is designing the UI blindly assumes that clicking a list of page numbers is the simplest solution rather than specifying the requirements and leaving the implementation up to the developer. If you tell them that a virtualised list/infinite scrolling is just as easy and works much better they are usually more than happy to use it, they just assumed it would be complicated or didn't even think of it. Most of the time when the list is very long what you really need is just searching and filtering, and then you can use the continuation token to page the result as the user scrolls. If there are still a lot of results then the user can tighten their search. No user ever wants to go straight to page 27.

i didn't get the part where first_name > 'Aaron', fantastic video by the way

Great video, thanks!

very cool content and friendly explain, thank you

I learnt a new thing today! Thank you for the excellent teaching!

Loved it

It's such a great show. I enjoy it more than then education content. Good job PlanetScale.

Thanks for this video. Really helpful. I was wondering: 1. At what point does offset pagination become unfeasible in terms of perf? 1m records, 2m records? Usually though you would surely first reduce the initial result set scoped to the user (but not for e.g an internal admin dashboard). 2. With cursors. This operates under the assumption that ID is an incrementing integer right? If an application uses uuid as primary keys, would that work too? Or do you need another unique auto increment column to have that precision? 3. Pagination backwards with cursors: I would assume the previous page token would need to either be kept track of or is there a way to reference only the first record in the set as the ‘end’ point of the previous cursor and limit it to ten before that?

I’m don’t usually leave comments but this is actually really good stuff. Loved your php vid and love this as well. Subbed.

Super helpful explanation and visuals! Thank you!

great presentation, well done

Awesome content better than theory i learn in college

Really great video !

that is such a good video! thanks!

This was very nice an d compact lesson, thanks mate

Yes, cursor pagination is great but it gets more complicated and controversial when we talk about UUID as primary key in the database table.

Awesome! I haven’t heard anyone else explain SQL as well as you do. Do you have a course on this stuff?

Great video! Thank you!

pagination for dummies, thanks a lot ;-)

Awesome explanation! Thanks!

Definitely not more than i wanted to know! Very helpful.

If PlanetScale ever establishes a presence in South Africa I would surely migrate over

Great content!

I am so glad I've stumbled upon this video. Let's see what you got here! Subscribed!

8:25 my question that i figured out the answer to: why does a cursor need to include the extra/useless data of the name? id is unique and is sortable, so i would think all you need to know for the cursor is that it's after that id (if id is in the ordering) since the rest of the ordering is handled by the rest of the SQL statement. my answer: it's possible that there's someone named Bobby that has the id 20000, which is lower than the Aaron's id of 25995, so you only need to care about the id when the name is the same, otherwise anything after the name "Aaron" is fair game

There's a third way - to maintain an 'index' table that references records in the original table. Unlike original table it must have no 'holes' -- ids must be strictly sequential. This gives you the flexibility to quickly jump to the page of interest using BETWEEN index1 and index2. This solution is not perfect and acts much like the limit/offset solution, but it's much faster to access but harder and slower to update and maintain. The advantages are that it's much faster than limit and offset while letting you access random pages. The disadvantages are that it is still prone to 'drifting' and harder to implement.

Btw cursor navigation is commonly called keyset navigation, which avoids confusion with db cursors.

this is such a good video

This is f-ing gold !!

Awesome content Great

This is awesome

With cursor you can still use pages, you just have to go a bit further with finding which record will be first.

Explain how it would work cursor pagination when you have the Application ordering múltiple and different columns

Good stuff as usual 🎉😊

What are you using for the visualizations? Manim perhaps? They are stunning!

Could you do more content on DB transactions and LOCK FOR UPDATE & CO?

Awesome explanation, thanks. But in the limit-offset method, you mention that all records before the offset are discarded, hence making it less performant. I'm not sure why the database can't just skip over those records in the first place, similar to how it skips over records in cursor pagination. Could you please explain that part? Thanks

What about the case where the id are not serial type, what if it is uuid, how would fhe cursor where clause work?

One more sub. Have a nice evening :)

Great video!!! I recently found a way to greatly simplify the cursor pagination query for sorting by two or more columns. The trick is to use tuples for comparison. I'm curious if there are any trade-offs that I should be aware of

I really love the animations you had in those videos. Do you mind sharing what software you use for making these animations. I really appreciate it. 👍

Can you use offset / cursor pagination when using indexes? A good example would be when using geospatial queries?

Okay, I will say it, order by and limit are a solution for noobs, the cleanest solution has been window functions for now 4 years if using MySQL, 6 years using MariaDB, and for more than a decade with PostgreSQL, Oracle, and MS SQL Server.

When using cursor and multiple columns, I dont understand why you'd need to query both the first_name and the id. Since the id guarantees deterministic ordering, looking for id > 25995 is enough to get the same order you had before (assuming same order by, of course), first_name = Aaron or first_name > Aaron seems redundant

i wish we had materialize cte inside a function that has an expiry time. so we can have more complex query.. instead we pass in a refresh key to the function to request between new record or retrieve from cache sort of like session tokens

If you want to use cursors but also support jumping to pages, couldn't you conditionally use cursors or offsets? You could use a cursor by default, but if a user wants to jump to page x, then you can drop down to offsets. Then use an offset if the user navigates to the next page (or previous page if you provide both a next and previous cursor).

Always implemented cursor pagination manually. Going backwards is a pain! But doable, and you can always "fake" the page number on the client side. It's purely cosmetic Edit: skipping pages too, as mentioned later in the video. You can totally do it, I would just rather not haha. Always a relative jump with respect to the current cursor. And often times you still need to fallback on offset/limit if the user provides no cursor, or only a page index

You will never get me to stop saying "page"-inate.

Dude, what about when you have UUID as primary key, since it's not numeric I think it's not applicable?

/api/user?sort=age In these scenario tabel get unordered then how cursorbase pagination work?

Interesting explanation, thanks for this! But, what if there are no incremental ids in the db? How would cursor based pagination work if the primary key is an uuid and there's no serial number available?

What if an entry has been added before your page?

But the cursor only works, if the ID is an integer. How about UUID as primary key? This way even offset and cursors are unusable, if you delete data. What’s the best practice for this usecase?

How about using UUID as the row's identifier?

I use both but don't what they called. But don't know performance difference. Still thinking best way to get total count() should i run a another query or something else.

How would u implement cursor pagination if your id‘s are uuid? My first thought is using an createdAt Column instead of the id. Is that a good practice or is there a better solution?

I have 1 question, what if my where clause is super complex and I need to reduce my data universe a lot , cursor will need to reduce the universe and then apply the where for the cursor like offset , this is not more complex or equal ?

How does cursor pagination handle insertion of records before and after the cursor?

I am always having to lookup both pronunciation and spelling.

I use limit and offset because I'm not deleting records in the database. never did lol

How would you go about reversing order when using a cursor-based approach? I.e. you've got to page 3 and then want to retreat back to page 2?

As a user, not being able to jump around on the pages feels very annoying, imo...

Well I think by the time a table gets so big that the performance of offset will be a major problem, it will also be a major pain to not be able to jump to page one gazillion in an instant?

Hi Aaron, What kind of mysql user interface do you use? im trying to install that

although cursor sounds great after watching this video, I can't find a suitable UI/UX to use it

I thought limit/offset is better, now im sure it is

what about using cursor and uuids as a id?

ladies and gents, Arnulfo Reilly...

TL;DR offset is more suitable for pagination and cursor for infinite scrolling

Does anyone know what software or technique he uses to produce animation like this?

There is a security catch here (in the base64 encoding value method)! Implementing it without enough validation of the base64 encoded value leaves a way to SQL Injection.

What about uuid? Can't do greater_than

wait. so I've pronounced paginate wrong the whole time?

I can't believe I was calling it page-ginate all these while 😅😅

Keyset pagination

I Googled to check if Ron Livingston had died. He hasn't.

with limit offset its better to run again the query to fetch the current page updated data after any modification or update?

wait, 0:35 .... are you actually using youtube in light mode?? like seriously!!, do you like it?? ooh i see that's why you have glasses! 😂😂, that was a terrible one

7:47 SQL injection ahoy