Cursor based Paging

[marcfil]

@fernandes wanted this, perhaps better if he writes about it a bit.

[fernandes]

Hello,

Now I'm writing something here! The idea of cursor based pagination is:

1. do not depend on page/offset because of two reasons:
   1. some records maybe be added and do not paginate correctly
   2. limit/offset is not performant in many database situations
2. We need to find a stable cursor (that is the default ordering of the table)
3. Provide mechanisms to go next and previous (can't jump to "pages")

The article that discusses the unstable pagination is this one, and this is the article that explains keyset pagination, and it's performance implications.

There's an implementation (but only for AR) and is the gem order query

To test the pagination here, I created a 10MM records tables started to go through the table, after creating the correct indexes I could retrieve the records in 300ms (I haven't tuned my machine either PostgreSQL installation)

When there's an index that "pre-filter" the records (like a user_id as a foreign key for these records), it retrieves the records in less than 10ms, so this is performant

ps: I added some rules to the stable cursor, was ordering based on 3 columns, and this is what lowers the performance, if orders on by a DateTime column, the 10MM rows paginations is always below 50ms

So here's my idea of what is, why it's important and the performance of cursor based pagination.

[marcfil]

I'm out of the running for this week, I'll respond to it when I get some time!

[marcfil]

@fernandes I love the idea of it, but since you already looked into it, could you maybe try and make as a feature/adapter for AR in finder?

If you need some help with understanding where's what and what does what, you can hit me up on Thursday or after on Slack.