Cursor pagination or keyset pagination

[ionutzp]

hi @Uysim by cursor pagination i guess you don't mean https://www.postgresql.org/docs/current/sql-declare.html real db cursors but something like keyset pagination, where the last seen value is used to get the next desired set of results right?

[Uysim]

Hey @ionutzp I never have experience with declare in postgres sql. I'll check on it and let you know

[sergioisidoro]

I'm very confused by the behaviour of this gem, as it does not seem to behave like a cursor.

In a cursor pagination, as I understand, you are supposed to pass the last record of the query, and it will return the following ones. Regardless of the order of the query.

It looks like this gem more like a ID based pagination, rather than a cursor, as it seems to break down as soon as the results are ordered by something other than a monotonously increasing ID.

I have the following pagination call:

 pagy_cursor(
   collection,
   order: { starts_at: :asc },
   after: params[:after]
)

However, the main key where things are sorted can be duplicate (two items with starts_at), which resulting in an interesting situation. When 2 items had the same starts_at I was expecting that the after would sort primarily through the starts_at rather than the ID. Also an item with a higher ID can have a lower starts_at.

So, given the first page:

// FIRST PAGE
// ....
{
  "id": 353682,
  "startsAt": "2024-01-23T00:00:00.000+02:00",
},
// FIRST PAGE END -- START EXPECTED SECOND PAGE
{
  "id": 352325,
  "startsAt": "2024-01-25T00:00:00.000+02:00",
},
{
  "id": 352327,
  "startsAt": "2024-01-25T00:00:00.000+02:00",
},
{
  "id": 352329,
  "startsAt": "2024-01-25T00:00:00.000+02:00",
},
{
  "id": 352629,
  "startsAt": "2024-01-25T00:00:00.000+02:00",
},

I would expect that passing the after: 353682 it would return items 352325, 352327, 352329, etc. However, as you might notice, all those IDs are lower than 353682, so these results will not show in the next page. Instead, what shows is:

// WITH AFTER = 353682
{
  "id": 353731,
  "startsAt": "2024-01-25T00:00:00.000+02:00",
},
{
  "id": 353780,
  "startsAt": "2024-01-29T00:00:00.000+02:00",
},
{
  "id": 353829,
  "startsAt": "2024-01-31T00:00:00.000+02:00",
},
{
  "id": 353927,
  "startsAt": "2024-02-02T00:00:00.000+02:00",
},

It took me a long time to realise what was happening because I was thinking this was an issue with unstable sorting of the second page. But after extensive tries I could not reproduce unstable sorting, since a call to all items yielded a stable sorting of the collection items.

This was working ok because in 80% of my case the starts_at grows in tandem with the ID, but not always. Am I getting something wrong?

[sergioisidoro]

As an update: I hacked something together for a pseudo cursor pagination a while back. I thought of making a PR, but I feel this was too much of a hack. In case someone elese finds it useful it is here: https://github.com/Uysim/pagy-cursor/compare/master...sergioisidoro:pagy-cursor:cursor-pagy

PLEASE NOTE that pagy 9.x now supports keyset pagination, which might be what you're looking for -- https://ddnexus.github.io/pagy/docs/api/keyset/