Consider changing pagination to just "Previous" and "Next" buttons
This is a somewhat controversial proposal: to stop using the regular pagination that renders a page number for every page in favour of just "Previous" and "Next" buttons.
The reasoning behind this is two-fold:
- Usability / Usefulness
To render the various page buttons (1, 2, 3, 200, last, etc) we need to know the total number of rows that we may end up displaying. This is done by running a SQL query that counts the number of rows, taking into account whatever filters (e.g. labels) may be set.
Unfortunately counting rows can be pretty slow as a table gets bigger. While one can cache the output this gets increasingly more complex as more filters are added over time. Even with the right indexes a
COUNT(*) can still take up a decent amount of the total time spent in SQL queries per page.
Usability / Usefulness
Currently out pagination looks more or less like this:
Prev 1 2 3 4 5 … Next Last »
This brings up the question: do users actually care about all the individual page numbers? How would somebody know what page number to navigate to? In particular, how is this better than just having a "Next page" button. This is something that could probably be best answered by those actually doing usability/UX research. Personally I can't really think of a case where one would e.g. navigate to an issues list and say to themselves "Aha! I know, that one issue is probably on page 14".
Having a simple "Previous" and "Next" button of sorts would at least solve the performance problem. Displaying these buttons can be done conditionally quite easily:
- We determine how many rows we want to display per page
- When the OFFSET is 0 we don't show "Previous"
- When the OFFSET is greater than 0 we show "Next", except when no rows were returned
For this setup no additional
COUNT(*) queries are needed. Further, we can change the pagination system this way to use keyset pagination (https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/), a setup where we paginate using ID ranges. In other words, instead of this:
SELECT * FROM issues WHERE project_id = X ORDER BY created_at DESC LIMIT 20 OFFSET 0
We'd use something like this:
SELECT * FROM issues WHERE project_id = X AND id > Y ORDER BY created_at DESC LIMIT 20
This particular approach to pagination is much faster, especially once you navigate away from the first few pages.