Use Cursor Pagination / remove Count() queries from backend

[batpad]

As a result of us spending time optimizing db indexes and queries, we see that one of the biggest reasons for slow queries seems to be large Count() queries. This is because in the current pagination architecture, we fetch the total count of results for every filter / query made to the backend. We need this to show total number of results returned as well as calculate the number of pages of results, etc.

Whilst I like being able to show total number of results and having explicit pagination, it does seem like we can get significant performance gains by changing our pagination method: never fetch total counts (perhaps unless the user explicitly requests for it), and use "Cursor Pagination" in the backend to "See More" to fetch more results for the user, without showing explicit page numbers.

What this would likely involve:

• Implement Cursor Pagination in the DRF backend code: https://www.django-rest-framework.org/api-guide/pagination/#cursorpagination
• Double-check SQL queries being executed and ensure there are no more Count() queries being executed
• If needed, create a separate endpoint that only returns the Count for a filter, that can be called explicitly by a user if they want the count of their result-set
• Handle new pagination style on the frontend:
  • Remove page numbers
  • Remove showing users total count of results (maybe convert to a button where users can explicitly request the count)
  • Enable a "See More" button for users to paginate / see more results from their result-set, which would show "No More Results" or so once the result-set has been exhausted

This is likely a bit of work, especially on the frontend.

@willemarcel do you think we could maybe do a quick attempt to implement Cursor Pagination on the backend and see if we could run some tests to measure the impact this has on performance and then see if this is worth pursuing?

cc @bitnerd @geohacker

[willemarcel]

Yes, it's quite simple to make that change in the backend. The frontend is the part that requires a lot of work to switch to the cursor pagination.