We switched to cursor-based pagination • Hacker News Board

Re: We switched to cursor-based pagination

#81

Post by yrgulation » Sun, Aug 21, 2022, 10:50 PM UTC

nerdponx wrote: post #65

yrgulation wrote: post #51

Earlier quoted context omitted.

This is hilarious - i am sharing my knowledge and getting downvoted for it. Anyway, the gist of it is that you store data in denormalised documents whereby searchable columns become keys of a single entry. The storage is a secondary storage not the main data source. You write data in both - sync in your relational db, async via a queue or what works best for your infrastructure. Searches are then made against it. If…

You said in a sibling comment that denormalized analytics tables are a hack, but I don't see how this is any less of a hack. It's literally the same technique, except now you're adding substantial operational complexity with a whole extra database server (versus just extra tables in the same database). And it does not at all solve the problem of needing to figure out which fields to denormalize and which ones to leav…

I hate mongodb with a passion. It may very well be that we are misunderstanding ops use case and making assumptions. My assumptions are: 1) many types of reports (as such many tables, es can create docs on the fly), 2) reports are made of many rows (otherwise why compare them with spreadsheets). My second assumption is that once you add pagination you can no longer ctrl f for content, you need full text search.

For a set of reports with consistent column names and values made of aggregate or static data what you are proposing works fine - since you can just increase counters or cache values as data comes in.

But for a use case where different types of reports have varying columns you can just dump everything into es documents and run basic aggregate queries. Or you can precompute data when making inserts.

Anyway, i am assuming too much about the use case, my bad. I’d have to hear more about it to defend my point.

Re: We switched to cursor-based pagination

#82

Post by aarondf » Sun, Aug 21, 2022, 10:51 PM UTC

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.

There are libraries for Laravel (https://github.com/hammerstonedev/fast-paginate) and Rails (https://github.com/planetscale/fast_page) as well!

Cursor based pagination is wonderful, but sometimes you're stuck with offset/limit for whatever reason. Might as well make it fast.

Re: We switched to cursor-based pagination

#83

Post by mbreese » Sun, Aug 21, 2022, 10:54 PM UTC

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.

Yes words can have two meanings, but given the similarity of context between these two meanings, calling this cursor pagination is not a great idea. It screams of someone that didn’t know about database cursors (which is only one implementation method) trying to describe a method for web site pagination. I’m not blaming the author here for this, as they likely know the difference. But for a new developer trying to Google this, it will be very confusing.

Re: We switched to cursor-based pagination

#84

Post by mbreese » Sun, Aug 21, 2022, 10:58 PM UTC

simonw wrote: post #52

hamandcheese wrote: post #31

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…

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.

Re: We switched to cursor-based pagination

#85

Post by notriddle » Sun, Aug 21, 2022, 11:06 PM UTC

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?

Re: We switched to cursor-based pagination

#86

Post by indecisive_user » Sun, Aug 21, 2022, 11:07 PM UTC

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.

I don't see how that solves the issue. A record can be updated for multiple reasons unrelated to the current database query, and any update at all to the record would hide it using the timestamp approach, regardless if the update would actually affect which page the data is on.

Re: We switched to cursor-based pagination

#87

Post by andy800 » Sun, Aug 21, 2022, 11:08 PM UTC

ilammy wrote: post #80

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? You get to see more ads while flipping through pages. Timing metrics for loading smaller pages make marketing happy. Timing metrics for time spent on the website make marketing happy.

Perhaps, for content-based sites. Not if you're a financial institution and visitors to your site are likely looking to find a specific transaction. Not if you are an insurance provider and visitors are trying to find a service provider. If you are a retailer selling stuff, you don't want browsers, you want buyers. I will concede that Amazon does pretty well and they paginate product listings, but I think they use a lot more intelligence to deliver high-value results than typical retailers, including very large ones.

Re: We switched to cursor-based pagination

#88

Post by indecisive_user » Sun, Aug 21, 2022, 11:12 PM UTC

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.

Re: We switched to cursor-based pagination

#89

Post by mbreese » Sun, Aug 21, 2022, 11:22 PM UTC

indecisive_user wrote: post #86

mbreese wrote: post #84

Earlier quoted context omitted.

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.

I don't see how that solves the issue. A record can be updated for multiple reasons unrelated to the current database query, and any update at all to the record would hide it using the timestamp approach, regardless if the update would actually affect which page the data is on.

I didn’t say it was a great approach, but if you used the timestamp as part of the PK and treated every record as immutable (any update resulted in a new row), it would work.

I’m not actually a fan of this approach as I think it causes too many scaling issues. If I were to do this, I’d either keep a database cursor in my session or store the full List of PKs in my session for the current query. This way you’d have the order of results as they existed at the time or the query. You still have to deal with the UPDATE/DELETE issue, but o guess this just depends on how much you care about consistent query pagination.

I’m not that much of a stickler and most datasets don’t change fast enough to make it an issue.

Re: We switched to cursor-based pagination

#90

Post by barrkel » Sun, Aug 21, 2022, 11:23 PM UTC

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 technique is still applicable even when you have no joins, because MySQL will materialize rows with every selected column before evaluating the unindexed portion of the predicate, and the order by.