Idea

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.

Motivation

"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.

Approach

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:

http://localhost:9130/users?limit=100&offset=100&query=(active=="false") sortby personal.lastName personal.firstName

If we move from offset pagination  to seek method  then query for first page will be the same.

But for further pages we need:

1. Front-end side:
   1. remove offset from query
   2. take last result from previous page and pass parameters(parameters that is used for sort) from it to query for the next page
2. Back-end side:
   1. remove offseting
   2. add filtering for parameters that is used for ordering

SQL example(fetch second page):

Conclusion

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.

Links

https://use-the-index-luke.com/no-offset