7 ms·
Getting Pagination Wrong (2016)
- y4mi 7y agoI wasn't expecting much with this title and was pleasantly surprised. (Sadly) rarely used sql features explained with code example. Lovely write-up
- thimkerbell 7y agoYou had a much better experience reading it than I did.
- uranusjr 7y ago> 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.
- jackewiehose 7y agoYes, 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.
- Izkata 7y agoPagination 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.
- colejohnson66 7y agoI 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 7y ago[deleted]
- kgwxd 7y agoIf 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
- steinuil 7y agoI 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.
- misnome 7y agoIt’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.
- johnchristopher 7y ago> 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.
- jillesvangurp 7y agoWell, 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.
- XCabbage 7y agoIf 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.
- lukaseder 7y agoIn 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
- heavenlyblue 7y agoNo, that's why you should reference pages from a version of search ID=234 and not search ID=356.
- altfredd 7y agoFacebook 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.
- boring_twenties 7y agoYour 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.
- yowlingcat 7y agoThis 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.
- leeoniya 7y agofor 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.
- yowlingcat 7y agoCertainly 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.
- pmontra 7y agoDoing 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.
- flowerlad 7y agoA 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?
- yetihehe 7y agoBecause 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.
- wrs 7y agoThe 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.)
- altfredd 7y ago> 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.
- mlthoughts2018 7y ago> “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.
- wrs 7y agoDoesn’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.
- mlthoughts2018 7y agoThis 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.
- lukaseder 7y ago> 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?
- Shank 7y ago> 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.
- deleted 7y ago[deleted]
- ajb 7y agoWhen 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.
- Ixio 7y ago> 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 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.
- learnstats2 7y agoI've got plenty of use-cases of this to know that GMail's number of pages is not accurate.
- buboard 7y agoI think it isn’t meant to be accurate. Not even sure if it is stable on page refresh
- elliekelly 7y agoThen why do they include it at all?
- buboard 7y agowhat matters is that there might be more search results, not their precise number
- buboard 7y agoWhich 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
- 7y ago
- skybrian 7y agoAs a side point, Civilization players do talk about game strategy in terms of turn number. "By turn 70 you should..."
- TeMPOraL 7y agoI 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.
- ptx 7y agoRegarding 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.
- phendrenad2 7y agoThis 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.
- jlokier 7y agoThis 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: - 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. The process is restarted with each new search query, so it takes quite a long time to hunt for things worth buying if I haven't decided exactly what I want, and that's why I don't buy much from Ebay any more. 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.
- buboard 7y ago“SELECT * from ... limit $start, $limit+1” is enough for prev/next navigation
- Supermancho 7y agoYou just summarized the article!
- ken 7y ago> 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?
- TeMPOraL 7y ago> 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.
- boring_twenties 7y ago> 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.
- meerita 7y agoI 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-the-listings-on-e-commerce-websites/ http://minid.net/2019/09/16/a-very-common-ux-dilemma-with-th...
- stazz1 7y ago"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.
- torstenvl 7y agoSomeone 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.
- Aperocky 7y agoAm 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.
- lukaseder 7y agoThis 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.
- adventured 7y agoThe 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.
- lukaseder 7y ago> 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.
- greatgib 7y agoThis article is so so stupid. It takes at reference the places where pagination is the worst: Facebook, twitter, google...
- jonathanhefner 7y agoI 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 https://github.com/jonathanhefner/moar