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

61–70 of 118 posts

Re: We switched to cursor-based pagination

#61

Post by yrgulation »

jvolkman wrote: post #60
yrgulation wrote: post #55

Earlier quoted context omitted.

Yeah with all due respect but hacks like these are a bit amateurish. I heard of a dude i think at intuit building their queues in a relational db because they work “just fine”. Prompted a giggle or two. Use the right tool for the task at hand, dont do clever hacks as they bite back later on.

"works just fine" might be a perfectly reasonable tradeoff if it avoids adding additional architectural complexity.

Depends what you define as complexity. What i described is trivial. Unless the userbase and or data are small.

Re: We switched to cursor-based pagination

#62

Post by bjourne »

Ime, concurrent updates to the data set isn't a problem in practice and nobody cares if they occasionally get duplicate entries. The cluttered and sometimes useless urls cursors cause are, again ime, a much bigger usability problem. Sure, naively implemented queries suffer if the user types ?page=123456 in the url but such problems are quite easy to fix.

Re: We switched to cursor-based pagination

#63

Post by eurasiantiger »

waspight wrote: post #37
dinkledunk wrote: post #21

Earlier quoted context omitted.

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.

Some people thoroughly enjoy a linear saccade search! See for example any social media app.

It definitely isn’t in the users’ best interest to have any method of scrolling through a lot of records.

Re: We switched to cursor-based pagination

#64

Post by nickkell »

bjourne wrote: post #62

Ime, concurrent updates to the data set isn't a problem in practice and nobody cares if they occasionally get duplicate entries. The cluttered and sometimes useless urls cursors cause are, again ime, a much bigger usability problem. Sure, naively implemented queries suffer if the user types ?page=123456 in the url but such problems are quite easy to fix.

How do you fix those problems then? Let’s say you have a “last page” button in the UI for example

Re: We switched to cursor-based pagination

#65

Post by nerdponx »

yrgulation wrote: post #51
jitl wrote: post #41

Earlier quoted context omitted.

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

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 leave in separate documents/collections.

And even if you do decide that it makes sense to split your analytics data into a separate database system entirely, document-oriented databases "ain't it".

I have very little experience with Elasticsearch, although I'm surprised to hear it being recommended for anything other than full text search. GP was talking about spreadsheet-style filtering, not full-text search.

But I do have a good amount of hands-on experience in production with Mongo, and I can say for sure that it is absolutely not a good option for an analytics database, and that I would much rather deal with traditional joins and filters. Even using it as a primary "application database" for a web app was a constant headache from beginning to end. I never thought I would miss MySQL so much, and I would never consider using Mongo again unless I had the very specific use case of storing a high volume of semi-structured data with no consistent schema, and even then I would convert the data to a more traditional tabular/relational format for use in a data warehouse if the business analysts or data scientists needed to work with it.

Re: We switched to cursor-based pagination

#66

Post by dmacedo »

nickkell wrote: post #64
bjourne wrote: post #62

Ime, concurrent updates to the data set isn't a problem in practice and nobody cares if they occasionally get duplicate entries. The cluttered and sometimes useless urls cursors cause are, again ime, a much bigger usability problem. Sure, naively implemented queries suffer if the user types ?page=123456 in the url but such problems are quite easy to fix.

How do you fix those problems then? Let’s say you have a “last page” button in the UI for example

Have used the conditional that if current page is greater than last page, just return the last. And same with negative just returning the first. If records are updated / deleted and the last page changed, then you'll just get the results of what the "new last page" are.

At scale you might care about the duplicate or up-to-date records. But cursor-based doesn't solve the problem if a results page is left open for "too long" and stuff was added behind your cursor (or after it, if navigating backwards).

It's as if making things less intuitive (to articles' reference to book pages), makes it any easier as long as you don't think about any pitfalls.

My suggestion is to just use pages, and optimise for the right order (I.e.: sequential IDs, or creation date, alphabetical, etc) that make sense for your data.

If you REALLY must care if results have changed, some delta being stored would be best (like some timestamp that allows the server side to indicate "hey, your results are out of date, from 7 days ago, maybe you left that page/API response unused for too long")

Re: We switched to cursor-based pagination

#67

Post by vbezhenar »

mikedouglas wrote: post #57
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…

This theoretically should be possible with MVCC, right? It's not an area I've explored and I could immediately see some issues with resource clean-up, but I could imagine it being possible with most modern DBs.

Yep, keep transaction open with necessary isolation. But it requires very thorough design of queries, as you'll run into locks pretty quickly. MVCC is not magic.

Re: We switched to cursor-based pagination

#68

Post by squeaky-clean »

dinkledunk wrote: post #12
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...

how to jump to an arbitrary page?

Jumping to a specific page is a bit of an ambiguous / undefined term in this case. Like asking for a specific page in a book that's still being written. Maybe today the plot twist occurred on page 100, but then the author decides chapter 1 needs more backstory, and now the plot twist happens on page 115.

Unless you can guarantee your data is static, or that the sorting order cannot be mutated and only append later values, the concept of what data belongs in which page could be changing every millisecond.

Re: We switched to cursor-based pagination

#69

Post by bjourne »

nickkell wrote: post #64
bjourne wrote: post #62

Ime, concurrent updates to the data set isn't a problem in practice and nobody cares if they occasionally get duplicate entries. The cluttered and sometimes useless urls cursors cause are, again ime, a much bigger usability problem. Sure, naively implemented queries suffer if the user types ?page=123456 in the url but such problems are quite easy to fix.

How do you fix those problems then? Let’s say you have a “last page” button in the UI for example

Don't have a last page button. :) Or limit the number of results to, say, 1000, which is trivial for an rdbms to handle. Or precompute the result set's rankings and transform the offset-limit query into a "where rank >= 991 and rank <= 1000" query.

Re: We switched to cursor-based pagination

#70

Post by nextaccountic »

jlg23 wrote: post #54
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…

This post is about "database cursors" and "keyset pagination". In practice, these terms refer to the same thing, one seen bottom-up the other seen top-down. Implementation-wise, one saves the state of the cursor in the pagination [parameters] and resumes reading from the DB with an equivalent cursor.

> 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 pagination (you get the first page, then the second page, etc) but without doing a new query for each page discarding the first n-1 pages per query

The downside is that it makes the server stateful, and doesn't scale (you would need to keep hundreds of cursors open if you had hundreds of simultaneous users)

Post reply on HN