Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We switched to cursor-based pagination

moderntreasury.com

Post reply on HN
Print view View original

31–40 of 118 posts

Re: We switched to cursor-based pagination

#31

Post by hamandcheese »

Cursor based pagination doesn’t actually solve the described pitfalls if your results are ordered by anything mutable, though.

A result you haven’t yet seen can be mutated to sort before your current cursor, likewise a result that you’ve already seen can be mutated to sort after the current cursor, causing you to see it twice.

Cursor based pagination does minimize the issue somewhat, because only the mutated rows are possibly affected, not unrelated records that lie on page boundaries. But depending on the use case I’m not sure that is worth that added complexity of cursor based pagination (it does get a bit tricky once you have any non-trivial sort clause).

Re: We switched to cursor-based pagination

#32

Post by simonw »

erikpukinskis wrote: post #23
drdec wrote: post #20

I was hoping to read about how they handled cleaning up cursors, what the effect on the server was when having a bunch of open, long-running cursors, etc. Unfortunately the article only treated the subject at a superficial level. So, anyone here implement pagination via cursors? What do you find to be the drawbacks and how do you mitigate them?

Yah, another downside of cursor-based pagination is: what happens when the record the cursor refers to is deleted? Do you just crash and ask the user to start over? Do you have to nudge open cursors on every delete?

My implementation of cursors works by encoding the primary ID of the last row on the page, along with additional information corresponding to the sort order if that's needed.

That way it doesn't matter if the record is deleted - I can still return the next page by showing records that come after that provided cursor value.

There's an example on this page: https://latest.datasette.io/fixtures/sortable?_sort=sortable

Since the table is sorted by the "sortable" column, the next page link includes this:

    ?_next=15%2Cg%2Cz
15 is the last value for "sortable" on the page, then g,z are the compound primary key for that last row.

Re: We switched to cursor-based pagination

#33

Post by saghm »

djbusby wrote: post #13
ReactiveJelly wrote: post #9

Earlier quoted context omitted.

So basically do it like Reddit? https://old.reddit.com/?count=25&after=t3_wtpvdp I noticed Reddit's pagination has that "after" parameter, which points to the last post on the current page. It glitches out if the last item is deleted by moderators, but otherwise it works smoothly.

On Reddit I frequently see the "next" page having the same posts as the previous page. Not all the same but many of the same. Like, maybe after is being respected but the sorting is different or something.

I see that on Hacker News a decent amount as well when going through the top stories across multiple pages. My assumption has always been that the order changes between when I load the page and when I move to the next one (which sometimes is not for another several minutes).

Re: We switched to cursor-based pagination

#34

Post by radiojasper »

djbusby wrote: post #13
ReactiveJelly wrote: post #9

Earlier quoted context omitted.

So basically do it like Reddit? https://old.reddit.com/?count=25&after=t3_wtpvdp I noticed Reddit's pagination has that "after" parameter, which points to the last post on the current page. It glitches out if the last item is deleted by moderators, but otherwise it works smoothly.

On Reddit I frequently see the "next" page having the same posts as the previous page. Not all the same but many of the same. Like, maybe after is being respected but the sorting is different or something.

You took some time to read the page and while reading it, the homepage changed and some new posts got added to the top. Therefore some posts get moved to the 2nd page.

Re: We switched to cursor-based pagination

#35

Post by G3rn0ti »

theogravity wrote: post #7
G3rn0ti wrote: post #3

Earlier quoted context omitted.

Well, except that TFA explains what DB cursors are and why they are faster than page offsets (because they skip DB entries).

It needs to talk about how to actually implement it. Most articles like this one mention its existence and why it's good, but generally stop there.

Postgres supports cursors and documents them very well:

https://www.postgresql.org/docs/current/plpgsql-cursors.html

Basically, you declare a cursor like that:

DECLARE cname CURSOR FOR ;

You can pass the cursor’s name „cname“ (and even store it on the client side, although, best encrypted) and obtain the „next“ slice of your data corresponding to the query on demand like that:

FETCH next cname;

Not sure you really gain that much performance on everyday queries but with a large number of rows it might.

Re: We switched to cursor-based pagination

#36

Post by simonw »

This post is not about database cursors. It's about the style of pagination where you have a ?_next=xxx link to get to the next page, where the xxx bit encodes details about the last item on the current page such that the next page can show everything that comes after that record.

This is also sometimes known as keyset pagination. My favourite technical explanation of that is here: https://use-the-index-luke.com/no-offset

Re: We switched to cursor-based pagination

#37

Post by waspight »

dinkledunk wrote: post #21
jsmith99 wrote: post #19

Earlier quoted context omitted.

Do you really need to jump to an arbitrary page and land on the exact item? For many applications an approximate jump is fine. If your column is fairly uniformly distributed you can guess the index for any arbitrary page.

Yes, my business users will feel like they don't have sufficient access to their data if they can't. > If your column is fairly uniformly distributed you can guess the index for any arbitrary page. I don't think that'll work in a multi-tenancy situation with complex filters.

I bet your users does not always know what is best for them.

Re: We switched to cursor-based pagination

#38

Post by hirundo »

My sympathies to the coders downstream of this large change for the amount of work required. I would like to add cursor based pagination to the APIs I manage. It would not be an option to go to our clients and explain that offset-pagination will be removed. There seems to be very little cost in supporting both.

Re: We switched to cursor-based pagination

#39

Post by WesleyJohnson »

At my current job, our intranet site has lackluster performance due, in part, to limit/offset pagination. Unfortunately, the business treats the "reports" we author like glorified spreadsheets and want the ability to filter on any column and order by any column. It makes it near impossible to tune/optimize.

The legacy system we're replacing used cursor pagination in a lot of areas and was perfectly acceptable to them, but now that we're going web based - it's not. Unfortunately, really - it seems vastly superior.

Re: We switched to cursor-based pagination

#40

Post by yrgulation »

WesleyJohnson wrote: post #39

At my current job, our intranet site has lackluster performance due, in part, to limit/offset pagination. Unfortunately, the business treats the "reports" we author like glorified spreadsheets and want the ability to filter on any column and order by any column. It makes it near impossible to tune/optimize. The legacy system we're replacing used cursor pagination in a lot of areas and was perfectly acceptable to them…

“It makes it near impossible to tune/optimize.”

I recommend using elastic search or a nosql database to optimise performance. Relational databases can be slow for this use case.

Post reply on HN