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

91–100 of 118 posts

Re: We switched to cursor-based pagination

#91

Post by aarondf »

barrkel wrote: post #90
aarondf wrote: post #82

There are ways to mitigate the (although not eliminate) the slowing down of offset/limit pagination in later pages. The technique is called a "deferred join" and it is most effective in MySQL. The basic idea is to paginate as little data as necessary, and then do a self-join to get the rest of the data for a single page. You can read more about it here: https://aaronfrancis.com/2022/efficient-pagination-using-def...…

To be clear, this technique (which it seems I independently discovered in 2015) mostly only works in MySQL because other databases usually have planners which are smart enough to not pull everything in eagerly. MySQL is fairly predictable, though, so when you understand that it wants to nested-loop join all your rows before evaluating predicates on the parent table, it's a predictable win to stop it doing that. The t…

Hey this is a great explanation. Most people just say "this makes no sense, can never work"

Re: We switched to cursor-based pagination

#92

Post by wruza »

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.

Re: We switched to cursor-based pagination

#93

Post by quietbritishjim »

orf wrote: post #73
nextaccountic wrote: post #70

Earlier quoted context omitted.

> these terms refer to the same thing No, cursor is this https://en.wikipedia.org/wiki/Cursor_(databases) https://www.postgresql.org/docs/current/plpgsql-cursors.html I once did pagination using database cursors, which is something different than keyset pagination: The server would keep a cursor open and keep fetching more data from the same query. This enabled the system to have an interface similar to offset pagina…

Words can have two meanings. Cursor pagination and key set pagination do indeed refer to the same thing. database cursors are a different thing.

You are agreeing with the parent comment with a tone of disagreement.

They never said what "cursor pagination" is or used the term at all. In this chain of comments, yours is the first one to use the term.

The comment you're replying to, like the original comment in this chain, merely said that this post is not about using "database cursors" for pagination. Which is correct, and you agreed that this is a different thing.

Re: We switched to cursor-based pagination

#94

Post by Aeolun »

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.

Re: We switched to cursor-based pagination

#95

Post by Aeolun »

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.

Re: We switched to cursor-based pagination

#96

Post by Aeolun »

indecisive_user wrote: post #88
debrice wrote: post #79

The pagination con given in the article is wrong, switching to stream isn’t fixing the dropping issue, removing sorting is likely why no records are being dropped (you wouldn’t drop records using a numerical sorted ID or creation date) Pagination is incredibly useful to human. If I tell you I found something on page 15 you can relate to it, something I cannot do with infinite scroll.

If a user has to go to page 15 to find something useful to them then I would argue that's a bigger failure of the UX/filtering than it is a success of pagination.

Books work this way. It’s a very relatable to very many people. Conversely, few people understand why they cannot just jump to page 15.

Re: We switched to cursor-based pagination

#97

Post by VWWHFSfQ »

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…

> Why does every web site default to aggressively paginate their information?

Because data retrieval costs money and resources. Unbounded API responses are a susceptible to denial-of-service. Well-architected APIs will always cap the number of results they return per-call.

Re: We switched to cursor-based pagination

#98

Post by paulmd »

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 missing some results that meet your logical criteria.

Possibly just an implementation error in the BlockJoinParser but it did not occur with numeric pagination.

In order to work with that, you need to “flatten” your json, like with the @JsonUnwrapped annotation, and some structures (like arrays) may become problematic and/or require significant lexical mapping of queries to the dataset.

Re: We switched to cursor-based pagination

#99

Post by andy800 »

VWWHFSfQ wrote: post #97
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…

> Why does every web site default to aggressively paginate their information? Because data retrieval costs money and resources. Unbounded API responses are a susceptible to denial-of-service. Well-architected APIs will always cap the number of results they return per-call.

Nothing needs to be "unbounded." Set the cap at 100 or 200 records instead of 10-20. Better yet, plenty of results can be cached. A custom search might require a database query, but Newegg's initial listing of MicroSD cards, graphics cards, hard drives, etc, -- queries that are fulfilled identically hundreds of times per day -- can be cached.

Re: We switched to cursor-based pagination

#100

Post by andy800 »

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.

No, relying on mega-frameworks and non-optimized graphic images are the express train to large request sizes. Don't forget video.

Indeed.com's search results are almost all text. Maybe a company avatar. No reason it can't provide 100+ job listings per page rather than 10-20.

Post reply on HN