Make client-side pagination a true recommendation

[awoods187]

I heard from k yesterday that they thought pagination via offset/limit was slow and were looking for recommendations on what to do for large result sets.

I spoke with @jordanlewis and he recommended:
"Doing “client-side pagination” by retrieving a set of records with a limit, and then checking the index key of the last row, using that as an index constraint, and then running the query again
this is normal - not specific to cockroach"

Our current docs: https://www.cockroachlabs.com/docs/stable/selection-queries.html#limiting-row-count-and-pagination actually suggest the wrong solution for pagination.

The reason is that "offset doesn’t know anything smart - it has to get the same data as before, just skips the first n. You have to participate as a client by remembering the index key of the last result set you saw."

We should also reference that some databases offer a feature called cursors to do this. @andy-kimball mentioned that "cursors are generally not a great architecture as well b/c they force server to keep state. client side is way to go for pagination. server pagination just doesn't scale well"

Jordan also mentioned that "you run into trouble with a scale out system like cockroach - if your load balancer moves you to a different server for example the cursor will be lost"

Andy also mentioned that "sql server has cursors, and we spent a lot of time recommending customers not use them"

This way we can make it clear that client-side is the way to go and its not just because we don't have cursors.

[awoods187]

I sent the recommendations to K and he replied:

`Hi, terrific answer Andy. Thanks for spending time on looking this up and we will add it to our general guidelines/knowledgebase for CRDB.

What devs usually do (I think) is to use what Spring Data JPA or Spring Data JDBC provides. It has a simple pagination API which does basic limit/offset traversal by default, but you can create own custom queries.`

Let's make sure to recommend the right course of action here.

[awoods187]

Another customer needing updated docs on pagination:
cockroachdb/cockroach#30352 (comment)

[knz]

Thanks Andy for collating things together. After reading this I concur with Jordan and Andy K's opinions. We could implement cursors technically but the PK values + LIMIT (and thus not OFFSET) are a better solution. This needs to be combined with AS OF SYSTEM TIME for reliable pagination.

I think the product is ready for clients to do effective pagination, however I also think it's not actually trivial for clients to achieve so we need ample guidance in docs. This will be an interesting / worthy doc project.

[wzrdtales]

With "client-side" pagination I assume you actually mean keyset pagination?
Since you also closed #1067, without catching important bits from it, it should be noted that with cockroachdb currently only quite ineffecient keyset pagination can be done (assuming utilizing UUIDs which pretty much everyone does today.) Should there be something new in cockroachdb I missed, please let me know.

However what could be a valuable feature for cockroach would be to have AFTER and BEFORE keyword which could be used instead of offset. Cockroachdb could optimize this way better, by using the index on the column and start retrieving records after that, than this very nasty subquery solution.

[jordanlewis]

@wzrdtales, I don't understand what you mean. If you have a table with UUID primary key, which is I think what you're talking about, you should be able to select all of the rows that come after a given row with a > constraint. UUIDs still have order, after all. Are you having problems with that?

[wzrdtales]

@jordanlewis Nope, they are ordered, but not, well ordered in the order they're created.

[jordanlewis]

That's true that UUIDs aren't well-ordered in their creation order, but IMO that's entirely separate from the discussion we're having here, which is about the recommendation we give to users for how to paginate a table by the order of its primary key.

[wzrdtales]

well pagination is about a predictable outcome of pagination. Which is basically the issue of LIMIT and OFFSET. they're not quite predictable. However > with UUID is less predictable than LIMIT, OFFSET, since LIMIT, OFFSET at least have an order in that case.

[wzrdtales]

Actually also adding to this, especially this is unpredictable since new records can appear after and before the record you're comparing against without any deterministic outcome. So yeah may be still a different discussion, but just adding this here since you closed the other issue.

[knz]

Thomas with CockroachDB you can use AS OF SYSTEM TIME to make the scrolling deterministic. I am still confident there is a way to obtain what you desire. -- Sent from my Android device with K-9 Mail. Please excuse my brevity.

[wzrdtales]

@knz I guess you meant me with Thomas? In that case: AS OF SYSTEM TIME was also discussed in the closed issue. It is not appropriate since this is a snapshot of reality and not the reality itself. Changes just disappear and new things do not appear. As said above the best solution would be something like AFTER and BEFORE keywords that could be easier translated by cockroachdb itself. Doing search over outdated data is almost at no time an option though.