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

41–50 of 118 posts

Re: We switched to cursor-based pagination

#41

Post by jitl »

yrgulation wrote: post #40
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.

What kind of NoSQL database are you thinking about? What strategy would you take with that database to optimize this problem?

Re: We switched to cursor-based pagination

#42

Post by thaumasiotes »

hnuser847 wrote: post #14
dinkledunk wrote: post #12

Earlier quoted context omitted.

how to jump to an arbitrary page?

Spoiler: you can’t.

But the entire concept is that this is an adaptation to the fact that data may be added to or removed from the database. If that's true, there would be no benefit in jumping to a specific page - there's no guarantee that that page will display any particular data.

Re: We switched to cursor-based pagination

#43

Post by mgkimsal »

hirundo wrote: post #38

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.

I didn't see anything specific about timelines. I would expect you'd want to offer both for some length of time, with some deprecation notice and a sunset date for the older approach. But perhaps they seem use cases in their logs that the current offset is used minimally already, and it's better to switch now vs later if/when adoption is higher?

Re: We switched to cursor-based pagination

#44

Post by erikpukinskis »

imbusy111 wrote: post #22
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?

You probably assume they are talking about database cursors. The cursor is just a record ID in this article. There is no long term storage of database cursors on the server. Assuming you can sort all your data, next query just returns all records after the given record ID, plus the record with that ID. One corner case would be if the cursor record is deleted. I don't see it mentioned how they handle it.

Does that mean you have to scan the entire results set to get the right page? So if I am on page 100, I have to query pages 1-99 and discard them?

Or is there a trick here I’m missing?

Re: We switched to cursor-based pagination

#45

Post by nerdponx »

jitl wrote: post #41
yrgulation wrote: post #40

Earlier quoted context omitted.

“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.

What kind of NoSQL database are you thinking about? What strategy would you take with that database to optimize this problem?

This sounds pretty typical for "analytics" workloads, which relational databases handle just fine. Maybe by "noSQL" they just meant something with column-oriented storage? But even that seems like it might be overkill, compared to setting up a couple of denormalized "analytics tables" to avoid the cost of complicated joins.

Re: We switched to cursor-based pagination

#46

Post by nerdponx »

erikpukinskis wrote: post #44
imbusy111 wrote: post #22

Earlier quoted context omitted.

You probably assume they are talking about database cursors. The cursor is just a record ID in this article. There is no long term storage of database cursors on the server. Assuming you can sort all your data, next query just returns all records after the given record ID, plus the record with that ID. One corner case would be if the cursor record is deleted. I don't see it mentioned how they handle it.

Does that mean you have to scan the entire results set to get the right page? So if I am on page 100, I have to query pages 1-99 and discard them? Or is there a trick here I’m missing?

I think the point is that clients are not even given the option to think in terms of "page numbers".

What's the use case for needing the 100th page of a query result, that also doesn't allow you to cache the 100th page locally to retrieve it later?

Re: We switched to cursor-based pagination

#47

Post by erikpukinskis »

simonw wrote: post #32
erikpukinskis wrote: post #23

Earlier quoted context omitted.

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 S…

Interesting… what happens if new records are added with that 15 value? Do you need an implied secondary sort with the record created time?

Also what if there are more than $page_size records with that 15 value?

Re: We switched to cursor-based pagination

#48

Post by nemothekid »

fein wrote: post #18

I have a few main requirements for what I consider easy to use pagination. 1. I can set how many items per page. 2. I can get to an arbitrary page either through a path/ query param or at least close with a pagination row that contains a way to jump around. If an item gets removed, whatever I was looking for should still be in the same vicinity. 3. As a result of #1 and #2, I can go back and find items I saw previous…

>I can get to an arbitrary page either through a path/ query param or at least close with a pagination row that contains a way to jump around

I've had quite a few heated discussions on this point. The problem is, once your dataset gets large enough, this use case is incredibly difficult to scale; not impossible (Google does it with millions of search results), but prohibitively expensive compared to how often the need of being able to jump to any specific page arises.

Now I always try to stick to cursor based pagination as the default in order to prevent people from building workflows on top of offsets.

Re: We switched to cursor-based pagination

#49

Post by jvolkman »

For anyone curious about this approach, I've posted an excerpt [1] of the shared Python + SQLAlchemy + Postgres code we use to handle pagination at my company.

The name "cursor pagination" is super confusing given that "cursor" is an overloaded term in databases. I always call this "token pagination", given that the APIs I've seen usually call the value a token.

[1] https://gist.github.com/jvolkman/b8c0e3d05929a1506c99fbc9474...

Re: We switched to cursor-based pagination

#50

Post by MatmaRex »

ReactiveJelly wrote: post #9
feike wrote: post #5

Reminds me of Markus Winand who hands out stickers on database conferences banning offset. His site is a great resource for anyone wanting to take a deeper dive on SQL performance: https://use-the-index-luke.com/sql/partial-results/fetch-nex...

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.

Yeah, or Wikipedia.

https://en.wikipedia.org/w/index.php?title=Category:Living_p...

Post reply on HN