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

1 Like
3

Hi Tess,

By sorting by id, I mean sorting by some sort of incrementing number. In my case, that's just my id column. Is there another way that cursor-style pagination can work without relying on a sorted column?

4

Hi @Sam_Q Are you using the Postgres integration for this resource? If so, are you able to use limit offset pagination instead?

Any chance you can share a screenshot of your query? Retool doesn't require sorting by the id to implement pagination

5

Yes, the limit-offset pagination absolutely works. It was just, per question 1, I was hoping to use cursor pagination, as I'm querying an rather large table and the offset starts to slow things down when many pages in.

6

Any chance you can share screenshots of your set up? About how many records are you working with?

7

About how many records are you working with?

As few as 500,000 in some apps, as much as 80 million in others.

Any chance you can share screenshots of your set up?

I currently have it set to the offset pagination model, which works alright, but can be slow many pages in, as mentioned. So nothing to show you re cursor pagination, which I can get to work and performs much better, except that I still don't understand how to implement complex sorting at the same time.