Recently a place with pagination that is done using
offset was found. And we don't have pagination on front-end side so it looks kind of
infinity scroll but loads more content on a button click.
Since page numbers and button for previous page are not needed and this particular example below uses postgres on a back-end side i would suggest using row values(seek method / keyset pagination) instead of
offset for pagination that uses postgres on a back-end side.
I suppose we have same situation in other places so approach offered below could be applied in similar places too.
"Offset" vs "seek method" performance comparison:
This picture is taken from presentation that could be found in "Links" section in the bottom of the page.
Resource usage is also described in that presentation.
Currently if we navigate to users app and filter by "inactive"(f.e.) and click on "Load more"(navigate to second page) following request will be executed:
If we move from
offset pagination to
seek method then query for first page will be the same.
But for further pages we need:
- Front-end side:
- take last result from previous page and pass parameters(parameters that is used for sort) from it to query for the next page
- Back-end side:
- remove offseting
- add filtering for parameters that is used for ordering
SQL example(fetch second page):
This page was created to introduce seek method over regular
offset pagination in Postgres.
/users was used here as an example and further investigation is needed to define whether
seek method is applicable when CQL is used and how much effort will it take to apply this approach.