Getting Pagination Wrong (2016)(blog.jooq.org)
blog.jooq.org
Getting Pagination Wrong (2016)
https://blog.jooq.org/2016/08/10/why-most-programmers-get-pagination-wrong/
74 comments
Also from the article: "when was the last time you googled for SQL and then went to page #18375 to find that particular blog post that you were looking for?"
The key difference in use cases here is that one is searching my stuff, and one is searching everywhere. Nobody wants page #18375 of everything. People do occasionally want page #3175 of their own stuff.
The key difference in use cases here is that one is searching my stuff, and one is searching everywhere. Nobody wants page #18375 of everything. People do occasionally want page #3175 of their own stuff.
Even when I'm searching other people's stuff, I don't always just want to browse through it linearly. Sometimes I want to skip around. Meaningful keys such as timestamps would be better than just page groupings, but page groupings, so long as they are stable, are still useful. Sometimes I do want to skip to page #18375 because I know that I've already browsed pages #1 through #18374 on a previous visit and I want to start where I left off. You can't typically do that with infinite scroll.
Unfortunately, the "so long as they are stable" constraint is incompatible with idea of mutable database backend. SQL pays massive performance price for OFFSET and it's results are still neither fast nor stable.
Which is why i browse twitter chronologically. I hate when sites don’t provide a way to edit the offset e.g. by editing the url
I've got plenty of use-cases of this to know that GMail's number of pages is not accurate.
I think it isn’t meant to be accurate. Not even sure if it is stable on page refresh
Then why do they include it at all?
what matters is that there might be more search results, not their precise number
I couldn't even believe I read that, the facebook timeline is easily the worst web UI out there.
The best part is when you follow a link from your timeline, then press the back button. With any normal website, you'd be back at the same position in the page where you were before. With Facebook, not only do I not get that, I get what seems to be a random position in the timeline.
The best part is when you follow a link from your timeline, then press the back button. With any normal website, you'd be back at the same position in the page where you were before. With Facebook, not only do I not get that, I get what seems to be a random position in the timeline.
[deleted]
Facebook having a timeline which is not stable/deterministic is a peculiarity of their implementation.
OP refers to a simple timeline which would not show such undeterministic behavior
> Also, as a user, in most cases, I don’t care that I’m on page 317 of my paginated screen that displays me rows 3170-3179 (assuming 10 rows per page). I really don’t. The page number is absolutely useless in terms of user experience.
But what happens when you do care? There are numerous times I clicked away from a Facebook or Twitter timeline, but then want to go back to that post I was reading and comment. And I can’t, because my back button brings me back to the top of the timeline, not where I left. Pagination would have helped.
But what happens when you do care? There are numerous times I clicked away from a Facebook or Twitter timeline, but then want to go back to that post I was reading and comment. And I can’t, because my back button brings me back to the top of the timeline, not where I left. Pagination would have helped.
Yes, this annoying endless-scrolling without pagination on reddit is the reason I still use old.reddit.com, even though I think the new one is overall prettier. But you make one wrong click and you're completely lost, back at the top.
Pagination also takes advantage of spatial memory, so it's far far easier for me to find something when pagination is available instead of just search/sort/infinite scroll.
I've actually clicked 10+ pages back in gmail many times to find an email I could only vaguely remember, knowing roughly where in the history it should be, that I was completely unable to find with search due to a misremembered detail.
I've actually clicked 10+ pages back in gmail many times to find an email I could only vaguely remember, knowing roughly where in the history it should be, that I was completely unable to find with search due to a misremembered detail.
I don’t like infinite scrolling, but it does have one advantage IMO. It’s that everything you’ve seen is on the page, so, unless the page curls older entries, you can Ctrl-F what you want to find that you just saw.
[deleted]
If you have an account on reddit you can avoid having to use old.reddit.com if you turn off "Use new Reddit as my default experience" in preferences. Not sure how long it's going to last but I'm sure the "old" subdomain is going to stop working around the same time.
Other useful options to uncheck:
allow reddit to log my outbound clicks for personalization
allow my data to be used for research purposes
allow subreddits to show me custom themes
show user flair
show link flair
Other useful options to uncheck:
allow reddit to log my outbound clicks for personalization
allow my data to be used for research purposes
allow subreddits to show me custom themes
show user flair
show link flair
It’s even worse than that - back button brings you to the top of a _new_ timeline, that often doesn’t have the thing you wanted to go see, even if you scroll to the same point.
Well, pagination would be kind of the dumb way to do that. Smarter would be allowing users to query using a timestamp and then simply jump back to 1st of July 2007 or whatever it is you are looking at. Google photos does this pretty nicely. Using timestamps is also a smarter way to implement pagination if you do actually need it: select * where timestamp < x order by timestamp desc limit 10; Pretty cheap query to run if you have an index on the timestamp and you can just grab the next timestamp from the last result.
I've been doing search stuff for a while and this kind of UX requirement always pops up when you are dealing with junior designers or POs. They're thinking "hmm search" lets have some paging and just blindly copy a broken UX.
HN solved it nicely by just having a load more. I might hit that a few times but rarely more than a couple of times. One weird thing with that is that new articles often get added in between clicks in which case articles from the previous page reappear on the next page.
I've been doing search stuff for a while and this kind of UX requirement always pops up when you are dealing with junior designers or POs. They're thinking "hmm search" lets have some paging and just blindly copy a broken UX.
HN solved it nicely by just having a load more. I might hit that a few times but rarely more than a couple of times. One weird thing with that is that new articles often get added in between clicks in which case articles from the previous page reappear on the next page.
If I am stupid or using an application that doesn't have easily-Googleable documentation on its search syntax, then using a timestamp-based search filter may well not be possible for me.
Even if it is possible, it may be more work to look up the syntax than it is to zero in on the thing I want by page number.
Even if it is possible, it may be more work to look up the syntax than it is to zero in on the thing I want by page number.
> here are numerous times I clicked away from a Facebook or Twitter timeline, but then want to go back to that post I was reading and comment. And I can’t, because my back button brings me back to the top of the timeline, not where I left. Pagination would have helped.
YMMV: Not anymore. Clicking back refreshes the facebook feed for me.
On a more serious note: pagination is really helpful when working with inventory or data sets, it's some kind of crumble piece of information to hold in short term memory if you want to get back to a particular item that you saw "somewhere around here, in the 2 000 range".
But the article is more about database performance than UI.
YMMV: Not anymore. Clicking back refreshes the facebook feed for me.
On a more serious note: pagination is really helpful when working with inventory or data sets, it's some kind of crumble piece of information to hold in short term memory if you want to get back to a particular item that you saw "somewhere around here, in the 2 000 range".
But the article is more about database performance than UI.
I think in those cases it would be better to implement pagination, but with a "from" ID, such that the first element in that page will be the one with that ID. I remember a post saying that this was faster, and more useful in cases like Facebook where the timeline is always updating. This happens to me all the time when I'm browsing news sites with pagination: I go back in the article history to catch up, then I leave it there a few hours and when I come back and go to the next page I see half of the articles on the previous page on top of the new one.
Facebook and Twitter timelines are bad examples. There is no reason to avoid indexing by date and those services do in fact index by date. Those services may even be able to use custom database engines and build excellent date-based pagination menu from their index — they just don't do it. Infinite scrolling has better user retention (read: "takes more time to navigate") and allows to easily inject advertizing. So Facebook and Twitter will continue to use it regardless of it's technical merits.
The article does not make a point to avoid feature-rich pagination. It is not about infinite scrolling somehow being a great thing either. It is about SQL "OFFSET" operator being a piece of donkey shit. SQL requires OFFSET to have precise starting position and that position can only be determined by iterating over first OFFSET items from database — which generally does not use index. Most backend programmers have no clue and try to use OFFSET as their go-to pagination tool. Their pagination sucks and makes servers hang under trivial load.
The article does not make a point to avoid feature-rich pagination. It is not about infinite scrolling somehow being a great thing either. It is about SQL "OFFSET" operator being a piece of donkey shit. SQL requires OFFSET to have precise starting position and that position can only be determined by iterating over first OFFSET items from database — which generally does not use index. Most backend programmers have no clue and try to use OFFSET as their go-to pagination tool. Their pagination sucks and makes servers hang under trivial load.
In fact, with offset pagination, there's no way you can ever get back to that item, because you didn't reference the item, you referenced page 317. By the time you go back to page 317, there are 28 new items, and the item you care about is now on page 321, or whatever.
Your use case is precisely a use case for keyset pagination. What you're referencing here from Facebook or Twitter is just poor UX
Your use case is precisely a use case for keyset pagination. What you're referencing here from Facebook or Twitter is just poor UX
No, that's why you should reference pages from a version of search ID=234 and not search ID=356.
Your back button brings you back to the top of the timeline?
I'm kind of jealous, mine brings me back to what seems to be a completely random position.
I'm kind of jealous, mine brings me back to what seems to be a completely random position.
This is cargo-cult programming based on an overreaction to OFFSET, which while inefficient, is not the only way to implement pagination. If you have data with an auto-incrementing ID or timestamp and index on it, it's very easy to page arbitrarily. It's also very useful -- take any chat or news feed application. I don't want to lose my place in the feed just because there's some chattiness that occurred later on in time than my cursor is at.
The real answer to this question is to better understand how your database indexes your data and will try to use it to go and plan your queries, which it will very helpfully tell you. You want to avoid OFFSET to avoid a full-table scan? Great! You want to completely destroy a useful feature because you don't understand the way your database stores your data's intrinsic structure so as to more efficiently use it's very fast tree lookup? Better hold your horses there and engage your curiosity a bit more, because this is a common and efficiently solved problem.
The real answer to this question is to better understand how your database indexes your data and will try to use it to go and plan your queries, which it will very helpfully tell you. You want to avoid OFFSET to avoid a full-table scan? Great! You want to completely destroy a useful feature because you don't understand the way your database stores your data's intrinsic structure so as to more efficiently use it's very fast tree lookup? Better hold your horses there and engage your curiosity a bit more, because this is a common and efficiently solved problem.
for many cases, paginating by a timestamp range is meaningful, performant and deterministic. though you lose the ability to view a fixed number of rows which can result in some awkward ui...but i guess this is when overflow-y: scroll can work around that.
Certainly true, although that pain is something that can be at least partially mitigated in the backend. You can use approaches that are similar to exponential backoff, just with LIMIT, so that you do end up filling up your page. While there's some annoying bookkeeping involved, it does work and it does scale.
Doing without OFFSET and by using an index field (an autoincrement id, a timestamp, etc) is feasible if there are no order by clauses. If you want to sort the columns of an on screen table and paginate it you need an index for each sort combination. It's quite a pain to keep it up to date.
Edit: I run into this today because of a table with UUIDs and NULL values in a sort field. SELECT OFFSET 0 LIMIT 10 and SELECT OFFSET 10 LIMIT 10 returned overlapping results. I eventually added the record creation timestamp to the ORDER BY and all went well.
Edit: I run into this today because of a table with UUIDs and NULL values in a sort field. SELECT OFFSET 0 LIMIT 10 and SELECT OFFSET 10 LIMIT 10 returned overlapping results. I eventually added the record creation timestamp to the ORDER BY and all went well.
Definitely a very real caveat, but in this case, you're breaking the monotonicity condition that makes paging this way very simple. As you describe, adding a little bit more context to restore the monotonicity condition makes all go well!
> Better hold your horses there and engage your curiosity a bit more, because this is a common and efficiently solved problem.
What's the solution?
What's the solution?
Use the index. Understand the index.
I use and understand them.
What I don't understand is what kind of solution you're talking about, how it's different from the one recommended in the article, what exactly you mean by "page arbitrarily" and does that imply more capabilities than the solution offered in the article (where you remember the auto-increment-value/timestamp/sort-field of the last record and then request additional records based on that).
What I don't understand is what kind of solution you're talking about, how it's different from the one recommended in the article, what exactly you mean by "page arbitrarily" and does that imply more capabilities than the solution offered in the article (where you remember the auto-increment-value/timestamp/sort-field of the last record and then request additional records based on that).
The author references two methods in the post: one caches page boundaries and the other references using the seek method for keyset pagination. The former is brittle, and I am advocating for the latter.
It is solved with keyset pagination to be precise. "I don't want to lose my place in the feed" - well, you just lost it because of OFFSET.
[deleted]
Sure. I'm not advocating for OFFSET. I guess to be candid, I really didn't like the examples you picked. Arbitrary pagination is useful, and I don't understand why you spent so much time trying to attack it unconvincingly.
Beyond that, in your post, you link to two approaches for keyset pagination: the seek method and the page boundary caching approach. I'd much rather use the seek method you link to and leave it at that, because it doesn't rely on the brittleness of row_number(), which is liable to change. Of course, this all depends on your use case -- as you point out, if your data is truly static, page boundary caching is unlikely to become dangerous.
Beyond that, in your post, you link to two approaches for keyset pagination: the seek method and the page boundary caching approach. I'd much rather use the seek method you link to and leave it at that, because it doesn't rely on the brittleness of row_number(), which is liable to change. Of course, this all depends on your use case -- as you point out, if your data is truly static, page boundary caching is unlikely to become dangerous.
I'm not sure I get it. A row_number() based approach is the same thing as using OFFSET...
This assumes you have the ideal database schema with the ideal data content producers with the ideal user.
One could call into question any of the assumptions here, so why not go straight for the most self-assured one:
"I never ever ever want to jump to page 317 right from the beginning. There’s absolutely no use case out there, where I search for something, and then I say, hey, I believe my search result will be item #3175 in the current sort order."
...Unless you're trying to save money by shopping for some common item on eBay or Amazon, in which case your search will yield 1000s of results with the same name and slightly different prices, and no way to tell when the next level of "quality" begins. For instance, the first 3 pages of results may be some accessory for the item you really want, priced at $1. Then thankfully on page 4, you find the cheapest version of the item you're looking for. Pages 5-55 are all that exact item, plus accessory packs for a more expensive iteration of the item. Finally on page 56, you find the next step up in quality of the item, you decide it's sufficient for your purposes, and you purchase it.
Now wouldn't jumping directly to page 50 and then page 100 help you narrow down your search?
Now tell me how filtering or infinite scroll can aid in this situation. It can't. The only thing that can fix it is better control on eBay's part to weed out duplicate listings, but that would involve some pretty hefty machine learning, and suddenly eBa would have to raise their rates massively, and I'd no longer buy from there.
One could call into question any of the assumptions here, so why not go straight for the most self-assured one:
"I never ever ever want to jump to page 317 right from the beginning. There’s absolutely no use case out there, where I search for something, and then I say, hey, I believe my search result will be item #3175 in the current sort order."
...Unless you're trying to save money by shopping for some common item on eBay or Amazon, in which case your search will yield 1000s of results with the same name and slightly different prices, and no way to tell when the next level of "quality" begins. For instance, the first 3 pages of results may be some accessory for the item you really want, priced at $1. Then thankfully on page 4, you find the cheapest version of the item you're looking for. Pages 5-55 are all that exact item, plus accessory packs for a more expensive iteration of the item. Finally on page 56, you find the next step up in quality of the item, you decide it's sufficient for your purposes, and you purchase it.
Now wouldn't jumping directly to page 50 and then page 100 help you narrow down your search?
Now tell me how filtering or infinite scroll can aid in this situation. It can't. The only thing that can fix it is better control on eBay's part to weed out duplicate listings, but that would involve some pretty hefty machine learning, and suddenly eBa would have to raise their rates massively, and I'd no longer buy from there.
This is the #1 reason I don't use Ebay much any more.
When searching on Ebay, I use a sort of binary search on page numbers. The algorithm goes like this:
But if I had to infinite-scroll to get to the first useful result at item #2800, for each search, it would take so much longer.
I'm pretty sure Ebay could improve this UX by segmenting search results.
When searching on Ebay, I use a sort of binary search on page numbers. The algorithm goes like this:
- Set number of results per page to the maximum allowed, 200.
- Scroll through results on page 1. Item I searched isn't found?
- Scroll through results on page 5. Item I searched isn't found?
- Scroll through results on page 20. Item I search is found?
- Scroll through results on page 10. Item I searched isn't found?
- Scroll through results on page 12. Item I searched isn't found?
- Scroll through results on page 15. Item I searched is found about half way down?
- Scroll through results on page 14. Item I searched is found about half way down?
- Scroll through results on page 13. Item I searched is found about half way down but it's spares & repair only?
- Ok, I can begin *really* browsing what I'm looking for from half way down page 14,
and expect the density to increase with each page.
But if I had to infinite-scroll to get to the first useful result at item #2800, for each search, it would take so much longer.
I'm pretty sure Ebay could improve this UX by segmenting search results.
> “There’s absolutely no use case out there, where I search for something, and then I say, hey, I believe my search result will be item #3175 in the current sort order.”
I worked for a large ecommerce company that sold digital assets previously (music, photos and other things).
It was absolutely a huge use case that people would submit deeply paginated queries directly, such as from saved search result links to resume later, or known good bots that we allowed to crawl and index the search results for customers that integrated our inventory to their APIs.
It was such a high priority use case that we actually built a detector system to recognize these queries and divert them to a different pool of query shards to keep the heavy deep paginating load and cache misses off of the pool of shards that served main traffic.
Very much a huge part of our pagination design.
I worked for a large ecommerce company that sold digital assets previously (music, photos and other things).
It was absolutely a huge use case that people would submit deeply paginated queries directly, such as from saved search result links to resume later, or known good bots that we allowed to crawl and index the search results for customers that integrated our inventory to their APIs.
It was such a high priority use case that we actually built a detector system to recognize these queries and divert them to a different pool of query shards to keep the heavy deep paginating load and cache misses off of the pool of shards that served main traffic.
Very much a huge part of our pagination design.
Doesn’t that have an even worse problem, in that if you use a query for an extended period, the results are going to change out from under you while you’re scanning them? Seems like you can’t build a stable crawler on that — you’d need to store the actual result list, not redo the query each time.
This system used pagination keying, not just a link to a page of the results. The link would take you back to the most recently visited item from your pagination key.
Results could have changed in that sort order, but that is a risk in all pagination, depending on the freshness of results required by the user.
Results could have changed in that sort order, but that is a risk in all pagination, depending on the freshness of results required by the user.
> It was absolutely a huge use case that people would submit deeply paginated queries directly, such as from saved search result links to resume later
How would offset pagination matter to these folks? They would have probably had an even better experience with keyset pagination, resuming later after the exact item they left with, not the page which may have random other items on it by the time the resume.
> or known good bots that we allowed to crawl and index the search results for customers that integrated our inventory to their APIs.
Why not offer an API to those bots? And how does this relate to offset pagination, in the first place?
How would offset pagination matter to these folks? They would have probably had an even better experience with keyset pagination, resuming later after the exact item they left with, not the page which may have random other items on it by the time the resume.
> or known good bots that we allowed to crawl and index the search results for customers that integrated our inventory to their APIs.
Why not offer an API to those bots? And how does this relate to offset pagination, in the first place?
> “How would offset pagination matter to these folks? They would have probably had an even better experience with keyset pagination,”
we did use pagination keying, see the sibling comment
> “Why not offer an API to those bots?”
We did offer an API, these calls were from the API. The use case of the bots was to reproduce our search result orderings inside of partner integrations. Inside the partner app, the partner’s own search index of our stuff would utilize our own sort order ranking for a variety of queries that were dynamically changing at the partner’s discretion. Everything they consumed, including paginated items, was through our developer API.
we did use pagination keying, see the sibling comment
> “Why not offer an API to those bots?”
We did offer an API, these calls were from the API. The use case of the bots was to reproduce our search result orderings inside of partner integrations. Inside the partner app, the partner’s own search index of our stuff would utilize our own sort order ranking for a variety of queries that were dynamically changing at the partner’s discretion. Everything they consumed, including paginated items, was through our developer API.
Am I the only one feeling the author is speaking completely in his personal bubble?
While he might find 3170-3179 not useful, there might be some people that does, and having it there barely affect anything else at all.
Meanwhile his sql queries are assuming that businesses that build pagination use relational db exclusively. Highly scalable nosql db is a thing and if I were to bet most popular paginated calls would be built from such.
While he might find 3170-3179 not useful, there might be some people that does, and having it there barely affect anything else at all.
Meanwhile his sql queries are assuming that businesses that build pagination use relational db exclusively. Highly scalable nosql db is a thing and if I were to bet most popular paginated calls would be built from such.
This is not at all related to RDBMS. You would still prefer keyset pagination in a "highly scalable" nosql database.
Meanwhile, I'll wait for that convincing example of where "some people" want to find 3170-3179 useful.
Meanwhile, I'll wait for that convincing example of where "some people" want to find 3170-3179 useful.
The author is broadly arguing against this style:
1 2 3 ... 315 316 317 318 319 ... 50193 50194
That style is in fact extremely useful if you want to jump multiple pages ahead faster, or work backwards faster, rather than one at a time. And I'd argue that this:
< Prev | 1 | 315 316 317 | 50194 | Next >
with 3 or 5 results in the center, is the ideal form of that (can optionally drop the prev / next with strong left / right arrowing to compact it further). The example the author gives is intentionally bloated to amplify their point.
If I'm on page 1, and want to get to page 13, having multiple numbers in the middle accelerates that process. I can skip to 13 much faster by jumping results rather than going one after another. This is a practical use case and it works well in reverse by enabling you to jump to the end of the results (page 50194) and work backwards quickly through multi-page jumping from the last result, which is great for many types of time-marked content.
1 2 3 ... 315 316 317 318 319 ... 50193 50194
That style is in fact extremely useful if you want to jump multiple pages ahead faster, or work backwards faster, rather than one at a time. And I'd argue that this:
< Prev | 1 | 315 316 317 | 50194 | Next >
with 3 or 5 results in the center, is the ideal form of that (can optionally drop the prev / next with strong left / right arrowing to compact it further). The example the author gives is intentionally bloated to amplify their point.
If I'm on page 1, and want to get to page 13, having multiple numbers in the middle accelerates that process. I can skip to 13 much faster by jumping results rather than going one after another. This is a practical use case and it works well in reverse by enabling you to jump to the end of the results (page 50194) and work backwards quickly through multi-page jumping from the last result, which is great for many types of time-marked content.
> The author
I am the author ;-)
> That style is in fact extremely useful if you want to jump multiple pages ahead faster, or work backwards faster, rather than one at a time
It is very easy to jump several pages using keyset pagination. That's just a UX thing to do.
I am the author ;-)
> That style is in fact extremely useful if you want to jump multiple pages ahead faster, or work backwards faster, rather than one at a time
It is very easy to jump several pages using keyset pagination. That's just a UX thing to do.
You’re right about the offset problem, I guess that could be optimized somewhat by caching, then again for google 90% of search likely won’t hit any db at all.
Going to the next 10 isn’t as costly as sorting the next 1000. Which is likely why pagination are limited at that. It’s easy to not do offset and tokenize every single entry for a small amount of range and present it as the same kind of pagination.
Going to the next 10 isn’t as costly as sorting the next 1000. Which is likely why pagination are limited at that. It’s easy to not do offset and tokenize every single entry for a small amount of range and present it as the same kind of pagination.
I wasn't expecting much with this title and was pleasantly surprised.
(Sadly) rarely used sql features explained with code example. Lovely write-up
(Sadly) rarely used sql features explained with code example. Lovely write-up
You had a much better experience reading it than I did.
As a side point, Civilization players do talk about game strategy in terms of turn number. "By turn 70 you should..."
I don't understand why they even brought up the Civilization example. "One more turn" isn't something positive unless you're designing a videogame or dealing internet crack.
> Who got it right? Facebook. Twitter. Reddit. And all the other websites that do timelines.
Curiously: a webpage I only use because I'm part of a group that uses it for scheduling, a webpage I only use for occasionally ranting about software (never reading) because I'm too cheap/lazy to set up a blog, and a webpage I only read through its "old" domain which does classic pagination.
Am I the 2019 version of the guy who hates PCs because there's no switches on the front panel to toggle in a new boot loader when something goes wrong? Do I just not realize how much better the new way is?
Curiously: a webpage I only use because I'm part of a group that uses it for scheduling, a webpage I only use for occasionally ranting about software (never reading) because I'm too cheap/lazy to set up a blog, and a webpage I only read through its "old" domain which does classic pagination.
Am I the 2019 version of the guy who hates PCs because there's no switches on the front panel to toggle in a new boot loader when something goes wrong? Do I just not realize how much better the new way is?
> Do I just not realize how much better the new way is?
You're fine. The "new way" is unergonomic, but perfect for generating addiction. Sites using it are digital crack dealers, and the users who like the timeline tend to be casual consumers who don't yet realize they have a problem.
You're fine. The "new way" is unergonomic, but perfect for generating addiction. Sites using it are digital crack dealers, and the users who like the timeline tend to be casual consumers who don't yet realize they have a problem.
> Am I the 2019 version of the guy who hates PCs
I've been wondering this same thing lot. Last few years it's been seeming to me that technology is only getting worse, not better. Only anti-features are being added, and what useful features already exist are being removed.
Sometimes I think it must be just me, and this is what getting old is like. Like the grotesquely oversized phones and screens these days. I can't stand not being able to use the phone with one hand, personally. And for what? So I can watch a Hollywood movie on my phone, or something? Who the fuck does that?
But maybe lots of people do watch movies on their phones, and many seem to have both hands glued to the phone 100% of the time anyway, so using it with one hand is wholly irrelevant. I can admit that, I guess, as much as I hate it. Maybe it's just me.
But then you have garbage like infinite scroll. As far as I can tell, it's just objectively stupid. Worse than the alternative in every single possible way. To go back to the phones for a second, wireless charging. Is there anyone on the planet actually excited about this, except for phone maker execs? Wow, let me not only take 5 times as long to charge the phone , and use 5 times as much power while doing it, but also not be able to use the damn thing because it has to stay on the charging pad! Like, what the actual fuck. All this so I can save myself the trouble of sticking the plug into the port every other day?
Oh yeah, I almost forgot, to even do that we'll first have to make your phone out of glass, so it'll slide out of your pocket, off your desk, and off of anything it's not Krazy Glued to. What's that? Your phone doesn't even support wireless charging? No problem, we'll make your case back out of glass anyway. Because fuck you, that's why.
Fuck, even thinking about the state of tech today is now like talking about politics: do it for 5 minutes and now I need a shower and a stiff drink.
tl;dr- it's not just you.
I've been wondering this same thing lot. Last few years it's been seeming to me that technology is only getting worse, not better. Only anti-features are being added, and what useful features already exist are being removed.
Sometimes I think it must be just me, and this is what getting old is like. Like the grotesquely oversized phones and screens these days. I can't stand not being able to use the phone with one hand, personally. And for what? So I can watch a Hollywood movie on my phone, or something? Who the fuck does that?
But maybe lots of people do watch movies on their phones, and many seem to have both hands glued to the phone 100% of the time anyway, so using it with one hand is wholly irrelevant. I can admit that, I guess, as much as I hate it. Maybe it's just me.
But then you have garbage like infinite scroll. As far as I can tell, it's just objectively stupid. Worse than the alternative in every single possible way. To go back to the phones for a second, wireless charging. Is there anyone on the planet actually excited about this, except for phone maker execs? Wow, let me not only take 5 times as long to charge the phone , and use 5 times as much power while doing it, but also not be able to use the damn thing because it has to stay on the charging pad! Like, what the actual fuck. All this so I can save myself the trouble of sticking the plug into the port every other day?
Oh yeah, I almost forgot, to even do that we'll first have to make your phone out of glass, so it'll slide out of your pocket, off your desk, and off of anything it's not Krazy Glued to. What's that? Your phone doesn't even support wireless charging? No problem, we'll make your case back out of glass anyway. Because fuck you, that's why.
Fuck, even thinking about the state of tech today is now like talking about politics: do it for 5 minutes and now I need a shower and a stiff drink.
tl;dr- it's not just you.
I agree with most of the points regarding user experience. But I loathe infinite scrolling, and so strongly disagree that Facebook "got it right." I think "geared pagination" with a sprinkle of Ajax works well though, in lieu of infinite scrolling.
Here's an example, assuming there are 200 rows in total:
- User visits page; first 10 rows are listed.
- User clicks "More" link; next 20 rows are fetched via Ajax and appended to the list (30 total).
- User clicks "More" link again; next 30 rows are fetched via Ajax and appended to the list (60 total).
- User clicks "More" link again; browser navigates to the next 60 rows (rows #61-120).
- User clicks "More" link again; browser navigates to the next 60 rows (rows #121-180).
- User clicks "More" link again; browser navigates to the final 20 rows (rows #181-200).
The basic idea is that there are two typical use cases: 1) The user expects their desired row to appear in the first few dozen rows, and will change their query if it doesn't (as mentioned in the article). 2) Or, the user really does want to sift through all the rows, in which case it's preferable to do it in large steps.
Of course, the Ajax fetch increments can be tuned to best support these cases, as well as the number of Ajax fetches before triggering a full page transition.
I actually wrote a Rails gem that encapsulates this behavior, including robust handling of back-and-forth navigation: https://github.com/jonathanhefner/moar
Here's an example, assuming there are 200 rows in total:
- User visits page; first 10 rows are listed.
- User clicks "More" link; next 20 rows are fetched via Ajax and appended to the list (30 total).
- User clicks "More" link again; next 30 rows are fetched via Ajax and appended to the list (60 total).
- User clicks "More" link again; browser navigates to the next 60 rows (rows #61-120).
- User clicks "More" link again; browser navigates to the next 60 rows (rows #121-180).
- User clicks "More" link again; browser navigates to the final 20 rows (rows #181-200).
The basic idea is that there are two typical use cases: 1) The user expects their desired row to appear in the first few dozen rows, and will change their query if it doesn't (as mentioned in the article). 2) Or, the user really does want to sift through all the rows, in which case it's preferable to do it in large steps.
Of course, the Ajax fetch increments can be tuned to best support these cases, as well as the number of Ajax fetches before triggering a full page transition.
I actually wrote a Rails gem that encapsulates this behavior, including robust handling of back-and-forth navigation: https://github.com/jonathanhefner/moar
A related question: why are relational databases not able to tell you how many rows are in a table without a full table scan, which is very inefficient?
> why are relational databases not able to tell you how many rows are in a table without a full table scan
This is unrelated to database being relational or not. Any mutable database will have hard time with that challenge.
Can you remember how long you have lived in seconds? In milliseconds? Why are you not keeping track of such obviously useful information?
Of course, it is theoretically possible to create a database, that can count rows very quickly — under very specific conditions. Are week-old results acceptable? What about year-old results? A nanosecond-old results?
Locking is hard. Your computer has multiple CPUs, which constantly execute out-of-order instructions, — such as other transactions, mutating the same table. In order to count results of read operation those CPUs will have to take a stop (no matter how insignificant) and agree on linearity of events. Some of CPUs may have to perform pending work (such as sending recent transaction contents over PCIe bus) before they declare themselves ready to sync. In the worst case you may have to wait for some preempted threads to be brought back to life by OS scheduler. And you have to do that during every read — otherwise your results will automatically be outdated!
If you can accept outdated or outright invalid results (duplicates, remains of incomplete transactions), you can always use weaker DB transaction isolation level ("READ UNCOMMITTED" etc.) or simply cache results in Redis/Memcached. But for obvious reasons that isn't a default.
This is unrelated to database being relational or not. Any mutable database will have hard time with that challenge.
Can you remember how long you have lived in seconds? In milliseconds? Why are you not keeping track of such obviously useful information?
Of course, it is theoretically possible to create a database, that can count rows very quickly — under very specific conditions. Are week-old results acceptable? What about year-old results? A nanosecond-old results?
Locking is hard. Your computer has multiple CPUs, which constantly execute out-of-order instructions, — such as other transactions, mutating the same table. In order to count results of read operation those CPUs will have to take a stop (no matter how insignificant) and agree on linearity of events. Some of CPUs may have to perform pending work (such as sending recent transaction contents over PCIe bus) before they declare themselves ready to sync. In the worst case you may have to wait for some preempted threads to be brought back to life by OS scheduler. And you have to do that during every read — otherwise your results will automatically be outdated!
If you can accept outdated or outright invalid results (duplicates, remains of incomplete transactions), you can always use weaker DB transaction isolation level ("READ UNCOMMITTED" etc.) or simply cache results in Redis/Memcached. But for obvious reasons that isn't a default.
Because for each entered/deleted row you would have to update that counter, which would need to be done synchronously and would add overhead. In typical db usage you don't really need to access that number that often.
The number of rows in a table depends on whether rows have been inserted or deleted in another transaction. Conceptually it’s the same reason the database can’t tell you the sum of all the rows without a table scan.
Even if it could do this, it wouldn’t help pagination except in the rare case you’re trying to display all the rows in the table. Normally you need to count the results of a query, not the whole table, so there’s no good reason to optimize that case.
In an MVCC database, rows are present until the global transaction ID moves far enough to make them “old”, and the only way to count them for a given transaction is to look at them all and check their max transaction IDs. I guess you’d have to keep a set of row counts for each outstanding transaction ID to be able to instantly know the row count. (This is really the implementation-dependent version of the first paragraph above.)
Even if it could do this, it wouldn’t help pagination except in the rare case you’re trying to display all the rows in the table. Normally you need to count the results of a query, not the whole table, so there’s no good reason to optimize that case.
In an MVCC database, rows are present until the global transaction ID moves far enough to make them “old”, and the only way to count them for a given transaction is to look at them all and check their max transaction IDs. I guess you’d have to keep a set of row counts for each outstanding transaction ID to be able to instantly know the row count. (This is really the implementation-dependent version of the first paragraph above.)
Someone at Lexis Nexis took this blog's advice, around the same time they went to "natural language search" that's more Google-like and has less control.
The end result is that if you want to find out the legal dictionary definition of a common term like "property," it takes something like half an hour to click through to the actual result page.
The end result is that if you want to find out the legal dictionary definition of a common term like "property," it takes something like half an hour to click through to the actual result page.
This article is so so stupid.
It takes at reference the places where pagination is the worst: Facebook, twitter, google...
Regarding the second issue brought up, that you will get duplicate or missing rows when the database changes between pages, I addressed this in one of my apps by
1) initially issuing a query that selects only record IDs for all the records and sends them to the client;
2) when displaying a particular page, issuing a query for the actual data "... WHERE id IN (..., ...)" with IDs taken from a slice of the client-side array.
It seemed to work OK, but this was on a very small scale. I guess one disadvantage is that you have run two queries for the initial page. And you have to transfer this array of IDs which might be a problem with many millions of rows.
1) initially issuing a query that selects only record IDs for all the records and sends them to the client;
2) when displaying a particular page, issuing a query for the actual data "... WHERE id IN (..., ...)" with IDs taken from a slice of the client-side array.
It seemed to work OK, but this was on a very small scale. I guess one disadvantage is that you have run two queries for the initial page. And you have to transfer this array of IDs which might be a problem with many millions of rows.
When there's a 'neg'[1] in the headline, I usually check out the comments here first. Very frequently, as in this this case, I then don't feel the need to actually read the article - the comments here are probably more informative.
[1] Persuasion technique which tries to get the subject to want the approval of the persuader, by criticizing them, directly or by implication.
[1] Persuasion technique which tries to get the subject to want the approval of the persuader, by criticizing them, directly or by implication.
> Moreover, I never ever ever want to jump to page 317 right from the beginning. There’s absolutely no use case out there, where I search for something, and then I say, hey, I believe my search result will be item #3175 in the current sort order.
There is a pretty big exception to this: when the search is so bad that it never returns proper results or filters poorly. If the search algorithm always starts in the same dumb way, pagination can let you “skip ahead” and find better results.
I know this is the XKCD workflow thing, but when a site implements search terribly but at least has some semblance of working pagination with jump tools, I can at least still get okay results.
There is a pretty big exception to this: when the search is so bad that it never returns proper results or filters poorly. If the search algorithm always starts in the same dumb way, pagination can let you “skip ahead” and find better results.
I know this is the XKCD workflow thing, but when a site implements search terribly but at least has some semblance of working pagination with jump tools, I can at least still get okay results.
I wrote about pagination and it's UX problems and this solution doesn't solve it. http://minid.net/2019/09/16/a-very-common-ux-dilemma-with-th...
"As you can see, Google estimates that there are probably at least 10 pages for your search and you can go “next”. Yes, you can skip some pages, but you cannot skip to a page number 50194, because, again:
No one wants that
It’s costly to predict, even for Google"
The man is right on the money.
No one wants that
It’s costly to predict, even for Google"
The man is right on the money.
“SELECT * from ... limit $start, $limit+1” is enough for prev/next navigation
You just summarized the article!
[deleted]
I actually have a use case. When the search filtering functionality is lacking or overly complicated to use. For example with Gmail if I can't be bothered to look up how date filtering works, since I receive emails at a fairly constant rate I can sort of guess that item #3175 might be around the date I'm looking for.
I strongly disagree with anyone that thinks Facebook "got it right" with their timeline. As far as my experience goes it's very easy to see something interesting on the Facebook timeline only for it to refresh and lose it forever. It can be very frustrating not to be able to get a consistent timeline.