Pagination is one of those things that almost everyone gets wrong for two reasons:
- User experience
- Database performance
Here’s why.
What’s wrong with pagination?
Most applications blindly produce pagination like this:
This is how GMail implements pagination. With my current settings, it displays 100 E-Mails at a time and also shows how many E-Mails there are in total, namely 1094. Those aren’t the total number of E-Mails I’ve ever had, they’re the total number of E-Mails in my “blog-idea” label (I’m using GMail as a TODO list, and yes, this blog won’t run out of articles any time soon).
What’s wrong with this practice?
Bad user experience
As a user, in most cases, I don’t really care about the total number of objects that are in my result set. At least, I don’t care about the exact number. Does it make any difference if I have 1094 or 1093 E-Mails? What about if I had 1067? Or 1000? 1000 would be precise enough for what I’m concerned.
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.
Who got it right?
And all the other websites that do timelines. Yes, I want to display only 10 rows at a time (or perhaps 100, but almost never all). So, pagination is important. But I don’t care about the fact that I’ve clicked 317 times on that “next page” button. If I ever browse that many pages (and I hardly ever do), then the only thing that matters is the next 10 rows. Just like when you play Civilization. You don’t care that you’re in turn 317. You just want to play one more turn:
![4b665f86cbb10d44e2db6ae4c96fef4050f0ce42878015ab30cf681b84537a30[1]](https://i0.wp.com/blog.jooq.org/wp-content/uploads/2016/08/4b665f86cbb10d44e2db6ae4c96fef4050f0ce42878015ab30cf681b84537a301.jpg?resize=625%2C351&ssl=1)
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. Exactly. Instead, I will do any of these:
- Refine the search
- Sort the result
In both cases, I will get a result where the record that I’m looking for is much more likely to appear on page #1 or perhaps #2. Again, 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? No. You searched for “Java 8 SQL” to find jOOQ, the best way to write SQL in Java 8. For instance.
How to implement a timeline with SQL
If your data source is a SQL database, you might have implemented pagination by using LIMIT .. OFFSET, or OFFSET .. FETCH or some ROWNUM / ROW_NUMBER() filtering (see the jOOQ manual for some syntax comparisons across RDBMS). OFFSET is the right tool to jump to page 317, but remember, no one really wants to jump to that page, and besides, OFFSET just skips a fixed number of rows. If there are new rows in the system between the time page number 316 is displayed to a user and when the user skips to page number 317, the rows will shift, because the offsets will shift. No one wants that either, when they click on “next”.
Instead, you should be using what we refer to as “keyset pagination” (as opposed to “offset pagination”). We’ve described this in past articles here:
The SQL syntax is a bit cumbersome as the pagination criteria becomes an ordinary predicate, but if you’re using jOOQ, you can use the simple synthetic SQL SEEK clause as such:
DSL.using(configuration)
.select(PLAYERS.PLAYER_ID,
PLAYERS.FIRST_NAME,
PLAYERS.LAST_NAME,
PLAYERS.SCORE)
.from(PLAYERS)
.where(PLAYERS.GAME_ID.eq(42))
.orderBy(PLAYERS.SCORE.desc(),
PLAYERS.PLAYER_ID.asc())
.seek(949, 15)
.limit(10)
.fetch();
The above will fetch the next 10 players after the player with SCORE 949 and ID 15. The pagination really depends on the ORDER BY clause, which is why you have to provide as many values in the pagination as you provided columns in the ORDER BY clause.
Now, that we’ve fixed the user experience let’s also look at …
How OFFSET pagination is bad for performance
The previously linked articles about keyset pagination also mention the poor performance of OFFSET pagination. Which is kind of obvious as OFFSET has to skip a given number of rows after applying all predicates and sorting, etc. So the database has to do all the work and then throw away 3170 records (if you’re jumping to page 317 with 10 rows per page). What a waste.
The following diagram shows very nicely how OFFSET gets slower and slower for large offsets:
That’s the obvious problem, but there’s another one. People always count the total number of rows to calculate the total number of possible pages. Why? To display nonsense like the following:
Page number:
1 2 3 ... 315 316 317 318 319 ... 50193 50194
Wow. OK so we’re on page number 317, which we don’t really care about in the first place, but we could just as well jump to page number 50194. This means that the database needed to run the query across all the rows just to be sure we get exactly 50194 pages in total.
Google something like page number pagination and observe the number of tutorials that show how you can implement the above nonsense. On Google Image search, you’ll find:
At the same time, the Google search itself reveals:
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
In fact, Google search implements keyset pagination as well, just like Twitter, Facebook, Reddit. And they don’t display the total number of pages because counting that total can be very costly, depending on your database.
In particular, databases that do not support window functions will require you to run two separate queries:
- The actual query with a
LIMITclause - An additional query replacing the
SELECTcolumn list with a simpleCOUNT(*)
Needless to say that this is not the best approach. If your database supports window functions (read about that miraculous SQL feature here on the jOOQ blog), you could produce the total row count in one go as such:
SELECT
rental_date,
inventory_id,
COUNT(*) OVER()
FROM rental
WHERE customer_id = 1
ORDER BY rental_date
LIMIT 10
That COUNT(*) OVER() window function is like an ordinary aggregate function, except that it doesn’t group your results. It just counts all the rows of your result and produces that count in each row, prior to limiting the result to 10.
When run against the Sakila database, the above produces:
rental_date inventory_id count
2005-05-25 11:30:37 3021 32
2005-05-28 10:35:23 4020 32
2005-06-15 00:54:12 2785 32
2005-06-15 18:02:53 1021 32
2005-06-15 21:08:46 1407 32
2005-06-16 15:18:57 726 32
2005-06-18 08:41:48 197 32
2005-06-18 13:33:59 3497 32
2005-06-21 06:24:45 4566 32
2005-07-08 03:17:05 1443 32
So, we’re displaying the first page with 10 rows and we need to provide navigational links for a total of 4 pages because we have a total of 32 rows.
What happens when we benchmark this query on PostgreSQL? The first run doesn’t calculate this COUNT(*) OVER() value, whereas the second one does:
DO $$
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT INT := 10000;
rec RECORD;
BEGIN
v_ts := clock_timestamp();
FOR i IN 1..v_repeat LOOP
FOR rec IN (
SELECT
rental_date,
inventory_id
FROM rental
WHERE customer_id = 1
ORDER BY rental_date
LIMIT 10
) LOOP
NULL;
END LOOP;
END LOOP;
RAISE INFO 'Statement 1: %', (clock_timestamp() - v_ts);
v_ts := clock_timestamp();
FOR i IN 1..v_repeat LOOP
FOR rec IN (
SELECT
rental_date,
inventory_id,
COUNT(*) OVER()
FROM rental
WHERE customer_id = 1
ORDER BY rental_date
LIMIT 10
) LOOP
NULL;
END LOOP;
END LOOP;
RAISE INFO 'Statement 2: %', (clock_timestamp() - v_ts);
END$$;
The result clearly indicates that in PostgreSQL, there’s a significant overhead in calculating this value:
INFO: Statement 1: 00:00:01.041823
INFO: Statement 2: 00:00:03.57145
Oracle optimises things a bit better when you’re using ROWNUM to paginate:
SET SERVEROUTPUT ON
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT NUMBER := 5000;
BEGIN
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
SELECT
rental_date,
inventory_id
FROM (
SELECT
rental.*,
ROWNUM rn
FROM rental
WHERE customer_id = 1
ORDER BY rental_date
) rental
WHERE rn < 5
ORDER BY rn
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Statement 1: ' || (SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
SELECT
rental_date,
inventory_id,
COUNT(*) OVER()
FROM (
SELECT
rental.*,
ROWNUM rn
FROM rental
WHERE customer_id = 1
ORDER BY rental_date
) rental
WHERE rn < 5
ORDER BY rn
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Statement 2: ' || (SYSTIMESTAMP - v_ts));
END;
/
Result:
Statement 1: X
Statement 2: X +/- 1%
So, the COUNT(*) seems to be calculated “for free”. Bonus question: Why is that?
Due to Oracle license restrictions, we cannot publish benchmark results here, comparing Oracle with PostgreSQL, sorry, but you can run the above code yourself against the Sakila database:
https://www.jooq.org/sakila
Conclusion
TL;DR: OFFSET pagination bad. Keyset pagination good.
If you want to paginate in your application, please make sure whether you really, really, really need:
- Exact page number
- High page numbers
- The last page number
- The total number of rows
Because if you don’t (and in 98% of all UIs, you really don’t), then you can drastically speed up your queries while providing your users a much better experience. If that’s not a win-win situation worth thinking about…?
And don’t forget, jOOQ ships with native keyset pagination support!
Nicely written advocacy for keyset pagination. But i don’t agree with the timeline is always the better kind of ux. Search and refine search is fine “if you know what you want to find”, but totally does not help you if you are browsing/exploring the search space.
And if you do that, you usually want to know how large the space is (approximately), to decide if you need to refine search first or just click through the pages. It also helps if you happen to be stuck with SEO wastelands at the top of your search, because you only have bad, low quality search terms at your disposal. Often helps to skip the first few dozend pages in such cases, instead of clicking next a gazillion times (or even worse, continuous scrolling).
Can you provide a concrete example of a situation where one wants to know how large the space is?
@lukaseder, isn’t the answer to your question is already in the @Michael’s comment?
> to decide if you need to refine search first or just click through the pages
Example: when you search “java sql” and you know the result is only 3 pages long – you will not concretize the search request, just examine the result. But when you see it’s 3000 pages long (or 3000 items found), you think twice and concretize your search query to “java sql jooq”.
Frankly, I never rely on that number (which is but an estimate). Much more interesting is the fact whether the first 2-3 results appear high quality, or if they’re rather random. It doesn’t matter if there are millions or only dozens of random results, my search was bad… Likewise, it doesn’t matter if there are millions or only dozens of high quality results. My search was good.
Perhaps, I’m more of a quality than a quantity person.
I would like to know how many articles you have written and next/prev just doesn’t cut it. Am I close to the end of the articles on your site? I dunno. Have I read half of what you have to offer? I may be able to squeeze another hour of reading your great blog posts, but not if, after an hour, I find out that I haven’t even scratched the surface. Knowing the total or even a relative position in a list is critical for things like time management. Your position is great for email because email has become this nebulous and time-based thing where nobody really cares. But if you are grading papers and you don’t have any idea how many you have to grade or how many are left, it becomes nearly impossible to determine if you have time to use the restroom or if you should just plow through so you can take your time before class.
Plus your system gives you no sense of accomplishment. It’s great to be handling your task list, knowing it is 500 items long you are on page 49 of 50…WOOHOO! Unless I don’t understand your post, your method would only let me know if there were more…what if there was one more and I just couldn’t face 10 more (the normal number on a page, for example). I’d have absolutely no will to continue.
But then there is also the indicator of whether you want to refine your search. If I see an estimated 4000 pages and I am on page 4 of the results, I’m gonna refactor. But without knowing that there were 4000 pages, why refactor? I’m sure it’s right here after I press next (said 3000 times). By definitiion, the nubmer of items returned quantifies the need to refactor. And then the pagination index lets you know that it is not in the first or second page of results. That’s when it’s time to refactor. But knowing that you have had 2 pages of results that didn’t work out of a lot more pages is the only possible way to know if you should refactor, I think.
These are all concrete examples of where a start, end, and position pointer for pagination is critical to your ability to manage your time or your expected effort.
I hate pagination as much as anyone, but I tend to think of pagination like a scrollbar. The size of the scrollbar determines the number of results on a “page” based on your visible area and your location within the entire list of results is determined by the scrollbars relative position from top to bottom. Using your method, there would a scroll thumb of just the right size to have an up and down arrow, there would be no start or end or way to know where you were in the list. It would be like Picasa–the photo management app that absolutely sucks for scrolling through thousands of pictures.
And the thing about pagination is that if a user doesn’t like it, he doesn’t have to use it. There’s always the next, prev button even if there’s not a first and last or page link. Having fewer options for navigating lists is only of significant benefit under very specific use cases like e-mail or twitter/facebook feeds.
Still, I love your blog and enjoy/appreciate what you have to say.
Absolutely agree. The timeline UX is worst UX in most cases *for me*.
Pagination is the best when you really don’t know what you seach for. Or partially know it. Or when UX don’t allow you to filter on any sofisticated criteria you can imagine (actually, all UX can’t do this).
So, you set as many criterias as you can by filter, and then… what you shoud do? Pagination, of course!
Endless scroll? No, thanks. Especially when I don’t know how more I need to scroll. Is there are 2-3 more pages of data or 2-3k and then I need to think more about search criteria or even forget about this search and go drink coffee?
About insufficient search criteria in the UX.
Example: Search browsing history in Google Chrome.
Often I remember that I saw some interesting page 2-3 month ago. It’s all I remember. No keywrods, nothing! Just fact, that I saw it 2-3 month ago. But if I’ll open the page, I’ll can say – that’s it!
If the history search UX were support pagination, I can easily go to page 10, watch dates of the history events to decide, what page number I should open next. And after 2-3 iterations I cound find the page (period) I want. Then several prev/next clicks and I found what I searched for.
But Chrome doesn’t implement pagination. It implements endless scroll. Try to scroll down up to 2-3 months ago. It will take infinity.
I agree with some arguments like performance, bad UI (and not UX). But i disagree with most of your article.
Bad UI (not UX): Yes, pagination is not pretty, but it’s not the purpose. Pagination is useful. Using a timeline with autoload or one button is prettier and easy to use. No questions, you just have one link to click. Easy. But removing it does not benefits user experience.
When you say we should refine search or sort it if we don’t find something. Sure, we could, but it’s your idea, not the way every people want to go. Plus, depending of how it’s stored, what you want to search and how many data your database has, your search, even detailed, can return a huge number of results.
Here is examples where pagination with page number help a lot against a timeline:
– Every sites with a list of things (shop, movies, books etc). If i have no idea or an imprecise idea of what i want, i just want to see a lot of them. So site display x items on the page and i go to many of the pages until i find the one i want. In case of connection issue, if i want to go back later or if i want to share the page, i want to be able to go to this page which show me the product or the products i saw sooner.
– It’s very frustrating to know there is something you see before, but you have to scroll during 5 minutes to get it back because there is no pagination and no search page.
From performance point of view, i agree with you for server side. But huge timeline is very bad for browser. More you display “pages”, more your browser has markup to deal with.
You can use timeline if you always have pages with few results, or at least if your search page and facets are exhaustive.
There is no better solution for all cases. Better solution depends of a lot of things, especially how the site is built and how to help user to find what he wants.
How does an easier UI not benefit user experience? I find Google incredibly more easy to use than, say, the ordinary ERP system. Let me illustrate this:
Your examples are very good examples for keyset pagination, not offset pagination. Do the exercise and think through them. You say you want to go back to a particular page much later. With offset pagination, you’re going back to record 3700 of a stored search. If there are new products that were added in the meantime, record 3700 is no longer the one you’ve had in your previous search. If you’re using keyset pagination, then you get the exact same result.
Also, do think about keyset pagination more thoroughly. Just because I used the timeline example doesn’t mean you can’t scroll back with keysets. You can, of course, just the same. The idea is to get the “next 10 rows” or the “previous 10 rows”, just not the 10 rows at offset 3700.
Also, if you’re keen on optimising performance in the browser, heck, then throw away the rows that you’ve displayed before. Like Google does. That’s just an implementation detail.
Cheers,
Lukas
> 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. Exactly. Instead, I will do any of these:
Please, don’t mention user-experience then follow up with noise about your personal preferences. UX would have you talking about a specific case where {behaviours} was monitored over {period}, and it’s quite likely that if there were options to do the different things they’d all get representation in results and the software would be a horrible mess to use because too many screens etc.
Just because Google doesn’t do something, or it’s not best located to a database, or ORM that doesn’t mean it’s a bad idea or a bad user-experience. Twitter has reasonable UX, Google has reasonable UX; unfortunately if you know enough both will suck to use in certain situations. Most tools are not omni-focused, and that’s a good thing! Like most things it’s good enough to keep you, but anyone that’s ever had Twitter play with the scroll-bar when you just wanted to read the last tweet, or lose your place in a list know it has some real sucky parts.
The cost of computation for n things, in n combinations over an active data-set is high, so what do we know from computer-science? we can put it into a lookup table maybe, have that help retrieve results. Maybe we can limit to the results at the time of search and lock that in until a new search is preferred? It’s not going to fit everyone’s use-case. “Live” data IMHO is a premium space that should be reserved for real edge cases. It’s not that expensive to search static data.
We know the user doesn’t know what they cannot see, so we guess they’ll never go to page 317. What we don’t know is maybe they use lookup and pagination by date and not ID, so they know exactly where they want to go (pages don’t have to be auto-incremented ID’s). Perhaps it’s a contact book and instead of refining query they just want to go to last page of A, or middle page (something order would have difficulty dealing with)?
The real problem is that the query set has to be re-built every search before refined, there are also ways around that! There are also problems with searching, do you use exact match? do you search case-insensetive, do you use LIKE “:term%”, or “LEFT(field, :termlength) = :term”; these are all case-by-case choices.
I do really like PK based offset systems, as a technical user I’m frustrated and often hampered by a non-static view of data. The human mind doesn’t deal well with ~8 million switches (1 byte of data). In my business we facilitated an option for those using business software as an alternative for people that wanted “every result scrollable on one page”, which as I suspect you know is also a really bad idea, is much easier to get a total and the first n results, then grab pages in batches of n using record offset. The thing is, 3 people have ever used it. Turns out it’s useless compared to a search, and it only works for Twitter because it’s free and people put up with free. Sometimes some people struggle to say what they are searching for, so they figure just manually looking will be faster, and it turns out in every single case those 3 people were responsible for payments to us. However abstractly right you may feel, you just do as asked and send a bill.
Use the bill to determine what is difficult, what is efficient and it will rarely ever be wrong.
I like exact total number and hate those rounded numbers. The reasoning like “a-ha, last Sunday I had 9846, now it’s 9848, so these two items appeared since then” is quite common to me and completely impossible with rounded numbers. Or “there where 9846 before I started deleting things, now it’s 9759, so I just deleted 87 items”. So please don’t speak for all users.
Twitter-like infinite scrolling is really bad when you are far from recent messages and use back-forward browser buttons. Pagination is much better in this case.
But you’re referring to some entirely different use-case. This isn’t pagination, this is just a dashboard, no? And if that number is so important, then it can be pre-aggregated at every insert / deletion. In SQL, a materialized view could be an interesting option.
Also, you’re right that there are some UX caveats when you’re far from recent messages, but that could also be solved with non “standard” pagination. You don’t care to jump back to page 17. You only want to jump back to page 1, and that is not really a problem with both pagination techniques.
As you’ve seen, you’re discussing UX use-cases, not technical implementations. I’m pretty sure that offset-based pagination can be avoided in almost all cases while improving the UX.
You’re declaring the difference as if it’s obvious to everyone which way it is. Who gets to decide if it’s a “dashboard” or just “pagination”? You sound as if the developer chooses, and always makes the right choice. Half the time, the developer chooses differently than I as the user want.
This article was written to show to developers that there are options to implementing things. The choice is not always obvious, but to know that there is a choice is a first step.
In the late 90’s I started developing in a Danish ERP system called XAL with it’s own proprietary language and proprietary database.
Given a table TAB with an index IDX on columns COL1, COL2, COL3 – the language supported syntax like:
Which would mean “find the point in the index where the tuple (VAL1,VAL2,VAL3) would be – if there is a row there then give me that row, if not then give me the following row in the index”.
Later they built a kernel allowing us to continue use the proprietary language, but have it run on an Oracle (or other) database. The kernel would translate that syntax into something like:
And then it would simply fetch the first row and close the cursor. There was a similar syntax for a form, where it would fetch the first {pagesize} rows before closing the cursor.
Quite nice user experience and generally performing quite well. The problems occurred when the users paginated the other way (used PgUp instead of PgDn) which would do something like:
Which would mean "find the point in the index where the tuple (VAL1,VAL2,VAL3) would be – if there is a row there then give me that row, if not then give me the preceding row in the index".
Translated into something like:
And again simply fetch one or a page of rows before closing the cursor.
This generally did *not* perform well for large tables where 99% of all rows had same value in COL1 (which almost all the tables had ;-), so the optimizer would usually go for full table scans.
When I asked for tuning advice back then, most would say something like "Oh, you're trying to use Oracle like a VSAM file – well SQL won't do that, sorry." :-)
Very interesting anecdote, thanks for sharing! Yes indeed, when there is skew on the first column, it’s going to be more difficult. I think that PostgreSQL is still the only database that gets it right, when you use the row expression predicate syntax:
What if we will limit pages to max 100 allowed, it will be ok to use offset pagination?
You do the math
Keyset/seek pagination and filter by search term
https://stackoverflow.com/questions/50347511/keyset-seek-pagination-and-filter-by-search-term
How keyset pagination will work in this case?
Thanks for the pointer. I’ve provided an answer here: https://stackoverflow.com/a/50353400/521799
Does JOOQ supports late row lookups? http://devoluk.com/mysql-limit-offset-performance.html
Why would you expect jOOQ to have anything to do with it? jOOQ just generates SQL, and what you’re looking for is a feature of the underlying database…
Well, it has a special method to do keyset pagination, so, I am wondering if is there a special method for this case.
Keyset pagination and offset pagination are just SQL syntax. The first one uses a synthetic SEEK clause in jOOQ, which translates to ordinary SQL predicates. Again, jOOQ really is just a SQL builder. It does not do any database optimisation, it’s not a database.
IMHO, you are wrong, in programming is better to know that something changed, to break and start again, than just to continue. Some here, for example I list products by price, so I want to know that some new products were added, if I see duplicates I know that there were added new products, so I will go back to previous page to see them.
That comment is not about programming, it was about user experience. There are much better ways to show new products having been added to the user than random shifting when applying pagination.
Can you please give some examples how to better show there are new products?
I’m not a UX expert, but
1) I really doubt this info is needed in the first place (mostly). YMMV
2) If it is needed, then you should show it directly, not a side effect caused by it. E.g. a flyout, or some menu item, just like unread emails (34)
Hi Lukas,
I’ve been reading your blog and been intrigued by the use of keyset pagination.
I have a question regarding how optimized these queries are. More specifically, when we request for the previous, or next page, does the database have to do ORDER_BY and sort from scratch? Or is there some caching involved?
To add to this, if the database does indeed store this somewhere, if a new row is added how would it know where to insert this new row?
Thanks
The point of keyset pagination is that you will jump to the right page in an index, because your order by, filter, and limit criteria will allow you to select a specific value (“key”) from an index and read the next few rows from the index as well. This means you will need to design your database indexes for this particular use-case.