Extremely FAST Paging With Cursor Pagination And Database Index Seek

You can still order by different columns while doing keyset (cursor) pagination. Even there are syntactic sugars for that in some databases, it is written like (a, b) > (?, ?). For example in PostgreSQL:

select * from posts where (name, id) > (“john”, 10) order by name limit 25

This syntax expands to name > “john” or (name = “john” and id > 10)

If your db hasn’t this syntax, you can always use second syntax

Also in EFCore Npgsql has translation for this syntax to postgresql, it is EF.Functions.GreaterThan(ValueTuple.Create(a, b), ValueTuple.Create(x, y))

The time it took still around 160ms?

I like the part where you copy it into a list twice 😂

This is super awesome! Thanks for sharing Milan!

Very well explained. Thanks.

What elastic search topics are they under the hood ?

You can just change Cursor >= request.Cursor to Cursor > request.Cursor rather than returning take + 1. This is fine for databases that use some form of auto increment as the first value is 1, so you don’t have an issue with off by 1 errors

Awesome video. There is a problem with cursor pagination when you need to apply a filter, so keep that in mind.

Well done for mentioning a sensible use case for cursor pagination. Video could have been improved by discussing the implications of dynamic sort order for cursor pagination viability!

Great video thanks.
Does the processing of large data by batch works better with a cursor pagination than with a skip take ?

Great

At the end with the complete cursor implementation, the performance is a BIT better than the approach with Take and Skip.

Maybe first .ToListAsync(...) is redundant?

Can we use cursor pagination in SQL Server Databse ? Because "SELECR TOP 20" is costly 💥

Hi, thanks for example. Do you know some efficient way for offset (not cursor) pagination with filtering, that could not be transmitted into where statement (for example, some sort of auth rule that could be applied only for materialized result after query execution). Would be great to watch your new video with such implementation

However this only works if you are ordering by ids, if you are ordering by something like datetime you would need to have a different implementation. And may even be hustle

Why didn't you just changed the >= to > to solve the duplicate problem? It results in much less code, you can use the ID of the last element in the returned page.

Where is the DB index see, can I see the query plan?

that sounds great to me, but what about when my primary key is of type guid?

Please make a video use case of cancelation token ,how ,why use this cancelation token

Looks like performance went back to Skip/Take performance after all that work. Might not be as beneficial since Skip/Take is fairly common and understood.

How about sorting?
Used something similar to delete old data from a database with hundreds of millions of rows, reduced batch jobs time by 60%.
Previous solution was hibernate though 🤭😉

Excellent 👌😊👌

Good video.

I've always known this as keyset pagination.

BTW: There is a keyset pagination library for EF core. It does a lot of work for you.

I suggest don't just use this for infinite scrolling, use it even for paged grids. It does mean you lose the ability to navigate to a specific page, but with search/filter features and or an index that's not really that important. Not only is perf better for a single query, but overall scalability is better because your database server is doing less work.

I often use this pattern except that I do not include the cursor in the result. The next cursor is simply the id of the last item, you don't have to query one more item this way.

Update by John - Milan is NOT deleting comments. You tube does not like links and will interpret `Product . Id` as a link.
-------------------------------------------------------------
Are you deleting comment Milan? My comment about how this wont work if the Id values are not contiguous (through a delete) or if an additional filter is used has been removed. Hmmm....

If you build some feed like Twitter/Instagram/TikTok have then consider using cursor pagination, but if you need ability to navigate to specific page (for example you're developing a porn site) then offset pagination may be more suitable

It is perfect, Unfortunately It can not be used for all situations such as for GUID and PageNumber clickable.

Awesome video, not very long and quite educating. I subbed and liked so please make more

What if the user is in first page and clicks on 10th page instead of second page? How would that work in Cursor approach?

Thanks for sharing the concept Milan.

I think the approach is very limited since most of the search will require parameters like patter to search based on example tag, product name and not rely on Id? How would cursor solve this?

Very nice video. Could you also share with us how do you add to this different kind of filtering data?

Real world example would be where you have a grid with 50 columns where 30 of them can be filtered and all need to be sortable... then the SQL is the King. But nice example, I apreciate your work, keep going.

I don't know if I can agree with that sample. While it's a good showcase for the cursor it's way too simplified. In almost all cases you don't do a paging by ascending order. It's more by descending order.

Like you've mentioned in your last example. You want to show the latest post in your feed and add older posts while scrolling down.

But it's more difficult than shown in your example. If you order descending, you also have to save your start position for the skip/take approach. Cursor is fine though since you return the next starting cursor position.

Maybe an additional (huge) advantage to the cursor approach.

If you don't save your state (last position or start point) for the descending order, you can mess up your paging if new items get added.

Seen that error a lot. Especially for junior devs and wanted to mention that.

This is also called keyset pagination

Is the example code shared on github?

Awesome video
Thanks

Great video but 2 important concerns:
1. If you can't order by the given cursor column, for example if your Id column is Guid this might not work as intended
2. The OrderBy call should come before the Where call (in theory you first need to sort by that cursor, then filter)

Nice video bro! Please, raise a little bit the gain of your mic, the volume of your voice is low....

You could use Tuple as a return value or? Not necessary to create response class object.

Hi, thanks for video.
It seems 'cursor' is a little bit confusing term in this video.
I mean the 'Cursor' in SQL DB (forward-only pointer to row in query result) is not that is in this video - just Id.
In this video the main point is using advantages of clustering index for fetching range of rows ordered by such index, for example pages.
This approach has best performance because, you know, rows are physically ordered by clustering index within db table.
EF is using clustering index for Id field by default.

Hi Milan. What about if I have my primary key is GUID. What should I do with millions of data to be fast. I face this issue with skip input of millions query is time out

Hi, I have question, it's a bit not about the topic in this video. I'm using cqrs, when I handle my command with creating Observation, I need to send notification to fron-end that is based on some logic through SignalR. I rise domain event with MediatR Notification and Handling all logic there. But now I also need to update my Patient in the same transaction(when i Create observation), as I know CQRS can update only one aggregate per command? So what should I do in this case.