How do Postgres Pagination, Sorting and Count Work Together?

App Building
1

After quite a bit of trial and error, I have managed to get cursor-based pagination working with my retool table connected to my postgresql resource.

This has led to some new problems:

  1. How do I set-up sorting with server-side cursor pagination, given that cursor pagination requires sorting by id? I can see the sortArray property provides the means to do it, but given that I want users to be able to sort by any column, it seems a compound sort would get far too complicated (and slow, for sorting all the unindexed combinations of multiple columns). Is there another way? (I'm somewhat new to SQL)

  2. Why does cursor pagination not list the total number of pages/results? Offset pagination requests a total row count field; so why not with cursor pagination as well? Currently it just shows pageSize (e.g. 'Showing 100 results')

2

Hi @Sam_Q

If you're using cursor based pagination, you'll have to build any potential sorting into your SQL query (it can definitely get quite complex!). Where are you seeing that the pagination requires sorting by id? About how many records are you working with? Curious if you could get away with not even using pagination in order to leverage the frontend sorting options

Adding total row count to cursor based pagination is a feature request that we're tracking! I'll post back here when it ships