Getting Pagination Wrong (2016)
blog.jooq.org
blog.jooq.org
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.
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 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.
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.
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.
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
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'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.
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.
Your use case is precisely a use case for keyset pagination. What you're referencing here from Facebook or Twitter is just poor UX
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.
I'm kind of jealous, mine brings me back to what seems to be a completely random position.
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.
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.
What's the solution?
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).
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.
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.
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.
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.
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.
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?
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.
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.
Meanwhile, I'll wait for that convincing example of where "some people" want to find 3170-3179 useful.
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.
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.
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.
(Sadly) rarely used sql features explained with code example. Lovely write-up
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?
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.
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.
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
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.)
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.
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.
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] Persuasion technique which tries to get the subject to want the approval of the persuader, by criticizing them, directly or by implication.
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.
No one wants that
It’s costly to predict, even for Google"
The man is right on the money.