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

101–110 of 118 posts

Re: We switched to cursor-based pagination

#101

Post by phendrenad2 »

andy800 wrote: post #78

Why does every web site default to aggressively paginate their information? Pagination sucks, it's a waste of time and of clicks, and should be a last resort. Sure, when Google returns millions of search results, paginate. But: For instance, if you have 40 entries and specify that there should be 10 items per page 40 entries??? Just show them all to me. My browser has a scrollbar, CTRL-F is much faster than your sear…

I'd guess that most people have written some bad query that only worked for small limits, so they started superstitiously making their limits very low.

Re: We switched to cursor-based pagination

#102

Post by moduspol »

mbreese wrote: post #84
simonw wrote: post #52

Earlier quoted context omitted.

The only approach I can think of which might be able to handle mutability of the rows used for sorting would be to support paginating through a snapshot: provide a mechanism whereby a full snapshot of the query at a specific point in time is captured such that the user can then paginate through that snapshot. Expensive to implement, so this would only work for situations where you can afford to spend significant stor…

You could also store timestamps as part of the records. Then your query will always be consistent if you add an extra clause of tstamp You probably also need to switch from deleting records to adding an archive bit (or timestamp). This gets complicated fast.

This also presumes that your records always are processed in order, right?

We struggled with this at a prior job because records within the most recent ~10 minutes could be delayed for any reason. We'd be collecting data from thousands of IoT devices and any of them could have momentarily lost a network connection for a few minutes, been rebooted, etc. So it'd be totally normal for end users to see records from 30 seconds ago, but then a few minutes later, see older records appear in between those records and older records because there were minor delays in some of them getting sent and processed.

We were leaning toward just putting a bound on the range that the end user could see (for example, only show records that are at least ten minutes old) to reduce the variability, but that gave the appearance that our system took ten minutes to process all data. It was a tricky one to solve from a user expectation perspective.

Re: We switched to cursor-based pagination

#103

Post by WesleyJohnson »

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.

Thanks, I'll have to search for some case studies on the topic to help me understand the approach. We use ElasticSearch in one small area to search for products across multiple points of metadata and I've always wondered if we were underutilizing ES in general.

Re: We switched to cursor-based pagination

#104

Post by fizx »

paulmd wrote: post #98
simonw wrote: post #36

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

Sounds great on paper but my experience is that key-based indexing is broken on SOLR and likely other lucene engine DBs. If you are evaluating conditions on child objects inside a parent document, the child objects get split and stored as a separate document that is joined… and if that child document is in a different block/file on disk, then it won’t necessarily be inside the range being scanned, and so you will be…

parent-child in lucene is a hack. It only works when the children immediately follow the parent, and that never happens when it's in a different file because you updated the child. It's a minor miracle you didn't notice the error with numeric pagination.

Re: We switched to cursor-based pagination

#105

Post by donavanm »

kibwen wrote: post #77
simonw wrote: post #52

Earlier quoted context omitted.

The only approach I can think of which might be able to handle mutability of the rows used for sorting would be to support paginating through a snapshot: provide a mechanism whereby a full snapshot of the query at a specific point in time is captured such that the user can then paginate through that snapshot. Expensive to implement, so this would only work for situations where you can afford to spend significant stor…

That might be even worse (or just as bad) for users, as now they won't see any updates to the underlying data set even if they want to, and will presumbaly need to perform some explicit action to get a new snapshot. Personally, if you care about users not missing any item in a query, you just can't use pagination at all, and you have to give them every item in the query in a single huge dump (running the query again…

Not quite. The API action and the previous responses pagination token is what denotes the consistent list “snapshot.” They get a current view of the datastore every time they start a new paginated API operation, eg without passing an existing token.

Edit: you might think of the list results like a materialized view. It works really well with natural ordering on something like item create time and you can pass that in as a query param.

Re: We switched to cursor-based pagination

#106

Post by Beltalowda »

notriddle wrote: post #85
andy800 wrote: post #78

Why does every web site default to aggressively paginate their information? Pagination sucks, it's a waste of time and of clicks, and should be a last resort. Sure, when Google returns millions of search results, paginate. But: For instance, if you have 40 entries and specify that there should be 10 items per page 40 entries??? Just show them all to me. My browser has a scrollbar, CTRL-F is much faster than your sear…

How many people actually read past entry five?

Your comment is half-way down the page. I read it.

It really depends on what kind of site we're talking about; there's loads of times I want to see loads of results. Sometimes I don't, but there's no harm is showing me more than I want, either.

Re: We switched to cursor-based pagination

#107

Post by Beltalowda »

Aeolun wrote: post #94
andy800 wrote: post #78

Why does every web site default to aggressively paginate their information? Pagination sucks, it's a waste of time and of clicks, and should be a last resort. Sure, when Google returns millions of search results, paginate. But: For instance, if you have 40 entries and specify that there should be 10 items per page 40 entries??? Just show them all to me. My browser has a scrollbar, CTRL-F is much faster than your sear…

And this kind of thinking is how you end up with 2mb per request. It’ll be like Jira, pulling in literal megabytes of data and still slow.

I don't think the problem there is the pagination (or lack thereof). This page has 103 comments right now and is 204K (31K compressed).

HN is pretty minimalistic, but the point is that text data isn't really what makes requests very large. For reference, 2MB is enough to include an entire book and then some. All of Dune is about 1.7M uncompressed in epub format (and ~1.1M compressed as an epub file). HN threads, Jira discussions, etc. can get pretty large, but I have not seen any yet that have started to approach the size of Dune.

Images might add more data, but those can be loaded on-demand when scrolled into view pretty easily.

Re: We switched to cursor-based pagination

#108

Post by clementmas »

simonw wrote: post #36

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

Most users don't care but I very much prefer to have URLs with `/?page=123` instead of `?_next=xxx`. I should get over it but meanwhile I'll stick to offset pagination with deferred join (see aarondf's comment).

Re: We switched to cursor-based pagination

#109

Post by hamandcheese »

andy800 wrote: post #78

Why does every web site default to aggressively paginate their information? Pagination sucks, it's a waste of time and of clicks, and should be a last resort. Sure, when Google returns millions of search results, paginate. But: For instance, if you have 40 entries and specify that there should be 10 items per page 40 entries??? Just show them all to me. My browser has a scrollbar, CTRL-F is much faster than your sear…

I’ve seen instances where the issue is not database performance, but web server performance. Serializing a graphql response in graphql-ruby, for example, can easily take 100+ms and grows linearly with number of records in a request.

In the general case this isn’t a good excuse, there’s no reason it should take that long. But you don’t always get to pick your technology.

Re: We switched to cursor-based pagination

#110

Post by wruza »

Aeolun wrote: post #95
wruza wrote: post #92

Why is offset-based pagination a thing at all? It sucks when a feed is very active, it sucks when you link to a page or visit search results, where it may turn out to be hundreds of pages away in a week. I always wondered why nobody does some obvious (order_col > k, order by order_col, limit n). It’s not a two years old mistake, it’s two decades and sites still do that by default.

Because normal people enjoy referring to ‘item 4 on page 3’ instead of ‘search for item 57533898-2’. At least I do. Passing numbers under 10 is easy. Passing identifiers is hell.

This makes sense for content that grows at the end.
Post reply on HN