What is the best practice for offset pagination using FaunaDB?

Help
,
1

Currently, I am developing a data Grid using angular which serves the data from faunaDB Via API call… Any tutorial for pagination to get the total count along with paginated data? I am exploring with angular MatPaginator component … Related tut links would be more helpful

2

Hey, welcome to the forum and thanks for placing the question here.
I don’t think there is a tutorial that already handles counts, hence why I asked you to place this question here. If there are any, our community will surely respond over time!

In the meantime, I’ll write down some extra info and recommendations. Pagination in FaunaDB indeed does not provide any count information by default but it’s fairly easy to get that count yourself. It would be quite hard for FaunaDB to get you that count since that means it has to anticipate what kind of query you are going to write… In essence, the only way it could do that is to run through all the pages and count them. That’s in some cases with huge collections not feasible and the decision to enable that for pagination should not be taken lightly :slight_smile:.

Counting dynamically

Imagine you paginate over a collection

Paginate(
  Documents(Collection('users')), 
  { size: 3, after: ..., before: ...} 
  // after or before won't be added on your first call of course
)

You could fairly easily transform this to:

Let(
  {
    query:  Documents(Collection('users')),
    page: Paginate(
      Var('query') , 
      { size: 3, after: ..., before: ...} 
      // after or before won't be added on your first call of course
    ),
    count: Count(Var('query'))
  }, 
  {
    page: Var('page'),
    count: Var('count')
  }
)

Which is something you will only have to do for your first query since from the moment you start paginating (using a cursor), you of course do not want the count to change anymore.
Nevertheless, Count() is an O(n) operation which means it will have to go over each user in order to count them. Work is happening that might provide a built-in collection count in the future to give you a more efficient conflict-free count but it will still take some time.

This will of course not help a lot with custom queries but you could argue that it makes sense that you have to count all these values in order to know the total. You can apply the manual dynamic counting approach as shown above on each query, no matter whether there are joins/filters/etc.

Counting via storage or via a cache.

You could of course, store the counted value as well which makes it faster for retrieval. E.g. each time you create a new document you could increase the count. There are downsides to this approach though:

  • for a more complex query that filters this is less feasible
  • depending on the amount of writes vs reads, the dynamic approach might be faster.
  • writing to one document for counts will create conflicts and will slow down your writing. There are workarounds (e.g. writing to multiple document randomly (e.g. 10) and on read take the count of these 10 documents).

+ caching

You could combine the above approach with caching and only provide the UI with an estimate of the count instead of the actual count (e.g. when fetching the stored count you could check the ts and determine whether you need to update the count because it’s too old). Each time you paginate forward, you know exactly how much the previous page contained which makes it possible to keep that count in the UI and give the user the exact count if he did paginate through all the pages.

3

See also this previous Topic. Particularly since you include “offset” in the title.

I was helpful for me to be mindful of what Ben here identifies as keyset pagination, as opposed to offset pagination.

The linked article concludes that you should be really, REALLY sure that you need

  • Exact page number
  • High page numbers
  • The last page number
  • The total number of rows

Because most applications don’t, and by avoiding those things you can improve both performance (cost!) and user experience at the same time.

1 Like
4

Any guidance on what that implementation actually looks like?

Does it just mean to that you get the data sorted how you want, so you can then target the specific keys sequentially? Which Range will then help you pull out a section.

thanks

5

Welcome @dwbelliston!

If you are using the Paginate function (docs) then you are already implementing keyset pagination.

If you are just really used to SQL, I guess the trick is to get your brain to think in the different style. There is no OFFSET, so in order to move to the next page, you provide the after option.

Paginate works on a “Set” of data, which can be simple or complex depending on the use Set functions.

image
image435×954 35 KB

Use these, along with sorting and filtering via indexes to narrow the data you desire, and then page through by providing the last item in the current page.

Example: If by chance there are unique fields in your data, then an app could provide a search field to retrieve that one document, then separately run the query for all of the data using that Ref as the cursor.

Example: An app could let the user enter a range of values, maybe before and/or after dates. The query could be modified using Range.

Example: An app could filter for a given tag, or anything and everything that apps ever want to filter on. Same thing.

These things are not new to applications. Keyset pagination just provides some perks like allowing new data to be added to the DB without bonking up your UX. You still have to page through the data, because there’s (presumably) too much to get at one time. But you don’t have to limit yourself to providing page number and forward/back buttons to the user. Or the end result may look just like any other site with paginated data. That’s up to you.